Exporting data to Word

[BIRD_FAT]

I have the following code, with everything carefully checked and in place, but it errors out at the line:Set myDoc = wdApp.Documents.Add(Template:="H:\Storage\My Documents\7 - Training\Forums\ExWd.doc") with the message "Object Variable or With block variable not set"


Sub ExportFinalColumnToWord()
On Error GoTo errorHandler
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim mywdRange As Word.Range
Dim MyColumnA As Excel.Range
Dim MyColumnB As Excel.Range
Dim MyColumnC As Excel.Range
Dim MyColumnD As Excel.Range
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
Set myDoc = wdApp.Documents.Add(Template:="H:\Storage\My Documents\7 - Training\Forums\ExWd.doc")
Set MyColumnA = Sheets("MySheet").Range("A1").End(xlDown).Select
Set MyColumnB = Sheets("MySheet").Range("B1").End(xlDown).Select
Set MyColumnC = Sheets("MySheet").Range("C1").End(xlDown).Select
Set MyColumnD = Sheets("MySheet").Range("D1").End(xlDown).Select
With myDoc.Bookmarks
.Item("bmMyColumnA").Range.InsertAfter MyColumnA
.Item("bmMyColumnB").Range.InsertAfter MyColumnB
.Item("bmMyColumnC").Range.InsertAfter MyColumnC
.Item("bmMyColumnD").Range.InsertAfter MyColumnD
End With
errorHandler:
Set wdApp = Nothing
Set myDoc = Nothing
Set mywdRange = Nothing
End Sub
To explain what the code is (SUPPOSED) to do:

Find the data at the end of columns A,B,C & D and place it into a word document created from a template "ExWd.dot" where I have placed my bookmarks.

Can Anyone tell me why it is erroring here? I have declared the variable at the start of the code!!??!

[mdmackillop]

No time to test this, but should template not be a DOT file?

[BIRD_FAT]

The template itself is - hence calling (Template:="lalala.doc")

If I call a *.dot file, it will open the template itself, rather than a file based on the template.

I've just tried it though:

Set myDoc = wdApp.Documents.Open "H:\Storage\My Documents\7 - Training\Forums\ExWd.doc"
and

Set myDoc = wdApp.Documents.Open "H:\Storage\My Documents\7 - Training\Forums\ExWd.dot", just in case! Still, no go!

[mdmackillop]

This works for me (Office 2000)


Option Explicit
Sub ExportFinalColumnToWord()
On Error GoTo errorHandler
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim mywdRange As Word.Range
Dim MyColumnA As Excel.Range
Dim MyColumnB As Excel.Range
Dim MyColumnC As Excel.Range
Dim MyColumnD As Excel.Range
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
Set myDoc = wdApp.Documents.Add(Template:="M:\0000 General\Test.doc")
Set MyColumnA = Sheets("MySheet").Range("A1").End(xlDown)
Set MyColumnB = Sheets("MySheet").Range("B1").End(xlDown)
Set MyColumnC = Sheets("MySheet").Range("C1").End(xlDown)
Set MyColumnD = Sheets("MySheet").Range("D1").End(xlDown)
With myDoc.Bookmarks
.Item("bmMyColumnA").Range.InsertAfter MyColumnA
.Item("bmMyColumnB").Range.InsertAfter MyColumnB
.Item("bmMyColumnC").Range.InsertAfter MyColumnC
.Item("bmMyColumnD").Range.InsertAfter MyColumnD
End With
wdApp.Visible = True
Exit Sub
errorHandler:
wdApp.Quit
Set wdApp = Nothing
Set myDoc = Nothing
Set mywdRange = Nothing
End Sub

[Kenneth Hobs]

Use DIR() to determine if your file actually exists before using .Add or .Open.

[BIRD_FAT]

This works for me (Office 2000)
??

Strange?? Still bugging out at the same place for me in MSO 2003 - even moved files to C:\Tempo to shorten the path!

copied our added code, (removed '.Select, too - thanks for spotting that), still not having any of it!

I'm sure it should work! :mkay

[BIRD_FAT]

Use DIR() to determine if your file actually exists before using .Add or .Open.

Can you explain that in a bit more detail please Kenneth!

[Kenneth Hobs]

I could not get your code to show an error on that line.

Here is how you can test for the file.

Sub ExportFinalColumnToWord()
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim mywdRange As Word.Range
Dim MyColumnA As Excel.Range
Dim MyColumnB As Excel.Range
Dim MyColumnC As Excel.Range
Dim MyColumnD As Excel.Range
Dim doc As String
doc = "x:\msword\MyFile.doc"
If Dir(doc) = "" Then
MsgBox "Error, file does not exist." & vbLf & doc, vbCritical, "File is Missing"
Exit Sub
End If
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo errorHandler
Set myDoc = wdApp.Documents.Add(Template:=doc)
wdApp.Visible = True
GoTo errorExit
Set MyColumnA = Sheets("MySheet").Range("A1").End(xlDown).Select
Set MyColumnB = Sheets("MySheet").Range("B1").End(xlDown).Select
Set MyColumnC = Sheets("MySheet").Range("C1").End(xlDown).Select
Set MyColumnD = Sheets("MySheet").Range("D1").End(xlDown).Select
With myDoc.Bookmarks
.Item("bmMyColumnA").Range.InsertAfter MyColumnA
.Item("bmMyColumnB").Range.InsertAfter MyColumnB
.Item("bmMyColumnC").Range.InsertAfter MyColumnC
.Item("bmMyColumnD").Range.InsertAfter MyColumnD
End With
Set wdApp = Nothing
Set myDoc = Nothing
Set mywdRange = Nothing
Exit Sub
errorExit:
On Error Resume Next
Set wdApp = Nothing
Set myDoc = Nothing
Set mywdRange = Nothing
Exit Sub
errorHandler:
MsgBox "Unexpected error: " & Err.Number & vbLf & Err.Description
Resume errorExit
End Sub

[macropod]

Hi Bird Fat,

Interested in a non-vba solution? Without relying on Word's fragile bookmarks? If so, read on.

1. open both the Word doc and the Excel wb.
2. name each of the cells containing the data to be exported to Word
3. copy one of the cells containing the data to be exported to Word
4. in Word, use Edit|Paste Special, check 'paste link' and choose an appropriate paste format.
5. in Word, select the pasted value and press Shift F9 to expose the field code. It should look something like
{ LINK Excel.Sheet.8 "C:\\Users\\System\\Documents\\Technical\\Data.XLS" "Sheet1!R1C1" \a \r }
6. Change 'Sheet1!R1C1' to your Excel name for this address, then press F9 to update the field.
7. copy & paste the modified field to each of the other locations where you want data from the Excel wb to appear.
8. repeat steps 6 & 7, updating the range name to match the relevant locations in the Word doc with the Excel data.

Having done the above, adding/deleting rows/columns other than the named ones will cause the Word document to point to the new addresses in Excel. Having the links update automatically in Word is as simple as checking the 'update automatic links at open' option under tools|Options|General in Word.

If, perchance, you have an issue in that the named cells are liable to be overwritten or deleted (ie they don't get relocated), you could use a secondary worksheet in the Excel wb to retrieve the data from the primary worksheet (eg via a formula that retrieves the data from the last row), and use the secondary worksheet as the data source for the Word document.

[BIRD_FAT]

I could not get your code to show an error on that line.

And I can't get it to go PAST that line

I'm thinking it maybe that I need to reinstall Office - with a proper clean out first!

I'll do that now and get back to you all!

And thanks to macropod - didn't think of using field codes - even though I have just recently solved someone elses question by using them, and placing an update on open into the code - DOH!

[mdmackillop]

I'm not suggesting Word is the problem, but if you are reinstalling Office, have a read of this.
http://www.theofficeexperts.com/word.htm#TroubleshootingWord

[BIRD_FAT]

I'm not suggesting Word is the problem, but if you are reinstalling Office, have a read of this.
http://www.theofficeexperts.com/word.htm#TroubleshootingWord

Thanks, read that one before!

It WAS the installation - had some stuff leftover in the installation menu AND the registry - cleaned it all up and reinstalled (I do this quite often as I'm a fiddler with reg settings, so I have it as an unattended set up on my HD, so no real hassles!) ...


... Guess what?


... That's right - all working fine!


Thanks for all the help guys - see you again soon, I have no doubt ;-)

[Kenneth Hobs]

I could only get that error when it tried to locate a bookmark that did not exist. Have you tried stepping through the code with F8 to be sure that the Set line is the line that errors?

[BIRD_FAT]

I could only get that error when it tried to locate a bookmark that did not exist. Have you tried stepping through the code with F8 to be sure that the Set line is the line that errors?


Turns out it was something to do with the install at the end of the day Kenneth, thanks anyway.