Solved: Trouble saving as cell value

[Croeg]

Hello,

I am STUMMPED ! I've been working on this problem all day with no luck. With code form this forum, I am able to merge info from my spreadsheet over to bookmarks on a Word doc. What I am attempting to do is save the Word doc in a folder and name it dependant upon a cell value (in my spreadsheet).

I've attached a test spreadsheet with 2 modules. Code in Module 1 works great. Code in Module 2 produces an error: "Error no:438; There is a problem" The only difference between code in both modules is below:

I will also attach a Word.dot file (in a second message) that code refers to in the test spreadsheet attached. Any guidance would be immensely appreciated!!!

[VBA] ChDir "C:\Automated Metro\Metro PCI Cover Page\" & wb.Range("C5")
If Err.Number = 76 Then MkDir "C:\Automated Metro\Metro PCI Cover Page\" & wb.Range("C5")
pappWord.SaveAs Filename:="C:\Automated Metro\Metro PCI Cover Page\" & wb.Range("C5") & "\" & _
wb.Range("C5") & "_" & wb.Range("D7") & ".doc"[/VBA]

Croeg

[Croeg]

Here is the .dot file (had to .zip it) to attach to this post.

Croeg

[Croeg]

Hello,

I managed to make some progress....

I changed wb.Range("C5") to Sheets("METRO").Range("C5").Value

Here's the chage I made:

[VBA] Dim MyPath As String
MyPath = "C:\Automated Metro\Metro PCI Cover Page\" & Sheets("METRO").Range("C5").Value

On Error Resume Next
ChDir MyPath

If Err.Number = 76 Then MkDir "C:\Automated Metro\Metro PCI Cover Page\" & Sheets("METRO").Range("C5").Value[/VBA]

Now the folder will be created if it doesn't exist, but I still can't save the word document into the created folder (if it didn't already exist). I keep getting the Error number 438...

It seems my trouble is with this portion of the code:

[VBA] pappWord.SaveAs Filename:="C:\Automated Metro\Metro PCI Cover Page\" & Sheets("METRO").Range("C5").Value & "\" & _
Sheets("METRO").Range("C5").Value & "_" & Sheets("METRO").Range("D7").Value & ".doc" _[/VBA]


If I delete the code above, everything works but the word doc appears as document 1 and is not saved.


Croeg

[Croeg]

Hello,

I finally figured out the problem. Below is code that pushes named ranges form an excel worksheet over to bookmarks on a Word doc, then saves the word doc as Sheets(Metro).Range("C5") .xls in a folder specified by a cell value on the spreadsheet (creating the folder if it doesn't already exist).


Croeg

[VBA] Option Explicit
Sub BCMerge()
Dim pappWord As Object
Dim docWord As Object
Dim wb As Excel.Workbook
Dim xlName As Excel.Name
Dim ActiveDocument As Object
Dim Path As String
Dim MyPath As String

Set wb = ActiveWorkbook
MyPath = "C:\Automated Metro\Metro PCI Cover Page\" & Sheets("METRO").Range("C5").Value

Application.Visible = True

On Error Resume Next
ChDir MyPath
If Err.Number = 76 Then MkDir "C:\Automated Metro\Metro PCI Cover Page\" & Sheets("METRO").Range("C5").Value

Path = wb.Path & "\MetroTemplate.dot"

On Error GoTo ErrorHandler
'Create a new Word Session
Set pappWord = CreateObject("Word.Application")

On Error GoTo ErrorHandler
'Open document in word
Set docWord = pappWord.Documents.Add(Path)
'Loop through names in the activeworkbook
For Each xlName In wb.Names
'if xlName's name is existing in document then put the value in place of the bookmark
If docWord.Bookmarks.Exists(xlName.Name) Then
docWord.Bookmarks(xlName.Name).Range.Text = Range(xlName.Value)
End If

Next xlName

With pappWord

.Visible = True
.ActiveWindow.WindowState = 1
.Activate

End With

With docWord

.Activate
.SaveAs Filename:="C:\Automated Metro\Metro PCI Cover Page\" & Sheets("METRO").Range("C5").Value & "\" & _
Sheets("METRO").Range("C5").Value & "_" & Sheets("METRO").Range("D7").Value & ".doc"


End With

'Next
Application.DisplayAlerts = True

'Release the Word object to save memory and exit macro
ErrorExit:
Set pappWord = Nothing

Exit Sub

'Error Handling routine
ErrorHandler:
If Err Then
MsgBox "Error No: " & Err.Number & "; There is a problem"
If Not pappWord Is Nothing Then
pappWord.Quit False
End If
Resume ErrorExit

End If

With wb
wb.Close True
End With

End Sub[/VBA]

[Ken Puls]

Hi Croeg,

Looks like you found the Push Excel Named Range Values to Bookmarks in Word entry in the KB.

FYI, I've edited your posts to use our VBA tags to make your code more readable. When you paste your code, you can highlight it then just click the VBA button above the reply window to get the same effect.

Also, thanks for posting back with your solution. We always appreciate that so that it can help other users who may come across your solution.

Cheers!

[Croeg]

Hi Ken,

The KB section is fantastic! There are so many useful entries found there that it is where I start my search for help! I'm still working my way around the site as I am sort of new. Thanks for passing on the VBA feature. I will be sure to use it when posting code next time.

Croeg