Run-Time Error 1004 'Worksheet' of Object _Global

Question

I have been using a Macro within a word document to input data from an Excel workbook into that MS Word document.

The routine prompts you to find the excel workbook where the data is stored, then copies fields from the workbook, finds an item in the word document text then replaces it in the word document text.

The following code was working, but would occasionally get a run-time error. Now I am getting the following error with every execution.

Run-time error '1004' Method 'Worksheets' of object'_Global' failed

It is trigger by this line:
.Replacement.Text = Worksheets("1- Organization Service Area").Range("B3").Value 'Insert Organization Name

The code is much longer, but here is the idea. The error is triggered on the first sub that is shown below.

Sub InputContractData()
'
' You must pick Microsoft Excel Object Library from Tools>References
' in the Visual Basic editor to execute Excel commands.

' InputContractData Macro
'
'

'Define Excel and Workbook Information
Dim objExcelApp As Excel.Application
Dim objCDCDataWorkbook As Workbook
Dim CDCDataFile
Dim CDCDataFilePath
Dim CDCDataFileName

'Define Word and Document Information
Dim objWordApp As Word.Application
Dim objWordDoc As Word.Document

'Open Excel Program
Set objExcelApp = New Excel.Application

Set objWordApp = Word.Application
Set objWordDoc = objWordApp.ActiveDocument
objExcelApp.Visible = True
objWordApp.Visible = True
CDCDataFile = objExcelApp.GetOpenFilename("Excel Files (*.xlsx), *xlsx")
Set objCDCDataWorkbook = objExcelApp.Workbooks.Open(CDCDataFile)
CDCDataFilePath = Left(CDCDataFile, InStrRev(CDCDataFile, "\"))

CDCDataFileName = Dir(CDCDataFile)

Call Sheet001



'Save Document in same folder as CDC Workbook
   objWordDoc.SaveAs CDCDataFilePath & "\DraftContract.docx"

' Close the new Word document.
    objWordApp.ActiveDocument.Close
' Close the Word application.
    objWordApp.Quit

End Sub




Sub Sheet001()
'Sheet 1- Organization Service Area -----------------------------
 With Selection.Find
    .ClearFormatting
    .Text = "[[ORGANIZATION]]"
    .Replacement.ClearFormatting
    .Replacement.Text = Worksheets("1- Organization Service Area").Range("B3").Value 'Insert Organization Name
    .Execute Replace:=wdReplaceAll, Forward:=True, _  
     Wrap:=wdFindContinue
 End With
End Sub

Which line(s) does the debugger highlight upon throwing this error? — underscore_d
.Replacement.Text = Worksheets("1- Organization Service Area").Range("B3").Value 'Insert Organization Name — Michiel Wackers
You should include essential info like that in your question; comments should be considered liable to disappear at any moment. — underscore_d
You create a new instance of the excel application object and open up a workbook within that new instance yet you rely on Selection (which relies on the activeworkbook within the same application instance) to perform your actions within Sheet001. — user4039065
I run the macro through MS Word from the document template, I have the excel application opened so I can access the data. No following on the new instance. Did I open excel twice? — Michiel Wackers

Scott Holtzman Scott Holtzman · Accepted Answer · 2017-10-30T16:02:36

The problem in the line:

.Replacement.Text = Worksheets("1- Organization Service Area").Range("B3").Value 'Insert Organization Name

is that MS Word has no idea what a Worksheet is since it's not a native object in the Word Object Model.

If you explicitly assign the object to it's proper parent it will work all the time. Since a Worksheet is an object in Excel, fully qualify it to the Excel object. I have shown this below and have chose to pass the Excel Workbook into the sub since you declar the variables locally in the InputContractData procedure.

Sub Sheet001(ws as Excel.Worksheet)
'Sheet 1- Organization Service Area -----------------------------
 With Selection.Find
    .ClearFormatting
    .Text = "[[ORGANIZATION]]"
    .Replacement.ClearFormatting
    .Replacement.Text = ws.Range("B3").Value 'Insert Organization Name
    .Execute Replace:=wdReplaceAll, Forward:=True, _  
     Wrap:=wdFindContinue
 End With

End Sub

Then call like this:

Sheet001 objExcelApp.Workbooks(CDCDataFileName).Worksheets("1- Organization Service Area")

Run-Time Error 1004 'Worksheet' of Object _Global

1 Answers