VBA code works from macro in Word but not in Excel??

[haw]

Sorry for the three posts today ... but I'm above my head here and need one last bit of help ... I have the following code in a Word VBA macro and it works fine: inserts a LINK FIELD in a specific bookmark, then adds the bookmark range (which now includes the LINK FIELD) back to the document. I say "it works fine" ... this is true when I invoke the macro from Word. But now I'm trying to run the same code from Excel, and it does not work. I cannot figure out why. And I'm a VBA novice so I feel like I'm just running around in circles here. Can someone please take a look at the code below and see if you can spot the problem?

Here's the snippet from the Word macro that works:
Set BMRange = ActiveDocument.Bookmarks("AcceptedByPerson").Range
Selection.GoTo What:=wdGoToBookmark, Name:="AcceptedByPerson"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _
"LINK Excel.Sheet.8 " & docname & " ""EstimatingInput!TLFEstimator""" & formatting, _
PreserveFormatting:=False
ActiveDocument.Bookmarks.Add Range:=BMRange, Name:="AcceptedByPerson"


And here's the full SUB from Excel. In the middle there are comments from me saying "this one works" and "this one doesn't work". The "this one works' lines do work, but they only do a straight text paste. I need to paste in a LINK FIELD, which is a little different. I've included the entire SUB because there may be something in there that looks innocent to me that's causing the problem. I basically cobbled together the "Word Automation" stuff from other posts on this forum. If you see something in this code that's opening/closing Word, which may cause other problems for me I'd appreciate a "heads up" on that, too.

Sub ExportToWord()

Dim wdApp As Word.Application
Dim wdDoc As Word.Document

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

Set wdDoc = wdApp.Documents.Open _
("C:\Data\Contracts\TestInsert.doc")
wdApp.Visible = True

Dim SourceRange As Range
Dim BMRange As Word.Range
Dim formatting As String
Dim docname As String

formatting = " \a \r \* MERGEFORMAT"
docname = "C:\\Data\\Contracts\\Test13.xls"

Application.ScreenUpdating = False
Set SourceRange = Range("CustomerName")
SourceRange.Copy

Set BMRange = ActiveDocument.Bookmarks("BM1").Range
' This works ...
'With BMRange
' .Select
' .PasteSpecial Link:=False, DataType:=wdPasteText, _
' Placement:=wdInLine, DisplayAsIcon:=False
' End With

' This does not work ...
BMRange.Select
wdApp.Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _
"LINK Excel.Sheet.8 " & docname & " ""EstimatingInput!TLFEstimator""" & formatting, _
PreserveFormatting:=False
wdApp.ActiveDocument.Bookmarks.Add Range:=BMRange, Name:="BM1"



MsgBox (" Text inserted")

With wdApp.ActiveDocument
.Save
.Close
End With


wdApp.Quit
Set wdDoc = Nothing
Set wdApp = Nothing

With Application
.CutCopyMode = False
.ScreenUpdating = True
End With

MsgBox "The report succesfully transferred to the document", vbInformation
End Sub 'ExportToWord

 

[Corni]

I suggest to check if you selected MS Word reference while in VBE. This is necessary in order to allow VBA to recognize Word objects, in order not to generate an error at compiling.

 

[NdNoviceHlp]

I think this piece of code is what Corni is refering to...

On Error Resume Next 
Set wdApp = GetObject(, "Word.Application") 
If Err.Number <> 0 Then 'Word isn't already running 
Set wdApp = CreateObject("Word.Application") 
End If 
On Error GoTo 0

I think there's a problem...even if it did compile. Your "If" test for an error if the word application is already running trys to create another application while your still on error. Maybe just move your error statement? Dave

On Error Resume Next 
Set wdApp = GetObject(, "Word.Application") 
If Err.Number <> 0 Then 'Word isn't already running 
On Error GoTo 0 
Set wdApp = CreateObject("Word.Application") 
End If

 

[haw]

YEs, I have the Word object library selected in VBE.

Yes, the code compiles -- but when it runs the "insert LINK" portion produces Run Time Error messages instead of the results I expect (i.e., instead of working the way it did in Word). The code referenced by Dave may indeed not be the best to check whether or not Word is already running ... as I said I'm not really sure how to do this and am relying on code from other people. Eventually, I'll get around to trying to "firm up" that portion of this routine. However, right now my primary concern is getting the LINK pasted into the Word document. If I cannot get that to work then there's no point in me worrying about the finer points of how many Word instances are running. I originally wrote the "insert link" macro in a Word document, and it works fine from there. The key portion of the WORD macro (that works) is:

Set BMRange = ActiveDocument.Bookmarks("AcceptedByPerson").Range
Selection.GoTo What:=wdGoToBookmark, Name:="AcceptedByPerson"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty,
Text:= "LINK Excel.Sheet.8 " & docname & _
" ""EstimatingInput!TLFEstimator""" & formatting, _
PreserveFormatting:=False
ActiveDocument.Bookmarks.Add Range:=BMRange, _
Name:="AcceptedByPerson"

This is the code I currently have in the Excel macro. It does NOT work. I'm trying to do the same thing as above, just do it from Excel (after opening the Word document earlier in the SUB).

BMRange.Select 'defined as type Word.Range
wdApp.Selection.Fields.Add Range:=Selection.Range, _
Type:=wdFieldEmpty, Text:= _
"LINK Excel.Sheet.8 " & docname & " ""EstimatingInput!TLFEstimator""" _
& formatting, PreserveFormatting:=False
wdApp.ActiveDocument.Bookmarks.Add Range:=BMRange, Name:="BM1"


In both cases the bookmarks exist in the word document before I run the macro. I have to add the bookmark back at the end of the code because word deletes the bookmark when I paste the link. This part works fine (when run from Word) ... but when I try running it from Excel I get a Run Time Error #405: wrong number of arguments or invalid property assignment, pointing to the wdApp.selection.fields.add line. I've been making slight modifications to this code block, trying to get it to work -- for example, adding the wdApp in front of the "selection.fields.add" ... but nothing I've tried works. Sometimes I get different error messages, but I cannot get it working ...

I hope I didn't confuse the issue by posting the entire SUB ... I was just trying to provide all the code in the event something that I didn't connect with the problem was actually causing it.

 

[NdNoviceHlp]

The finer point is that if you have more than 1 instance of word running your code will not work and you will get a whole bunch of varied error messages. Check the task manager processes to ensure only 1 instance of "winword" is running. Actually you really shouldn't have any running before you run your code and create another instance which is what the code in my previous post was addressing. If you fix up this first then you can move on with the rest of your code. See what your task manager says. You should really include some error coding to provide you a message and set wdapp = nothing and most importantly wdapp.quit or each time you unsuccessfully trial the code a new instance of word will be created. Hope this helps. Dave