Search code examples
excelvbams-word

How can I resolve error: object '_global' failed in MS Word Macro?


Overview: I have a Macro in MS Word, which copies Table data from the document and saves the results in specified columns to an Excel spreadsheet, which is stored on my local drive. The Word document contains a Submit button, which initiates a new row of data to be added to the spreadsheet. All submissions of data are copied to the master spreadsheet.

Current issue: When the macro is run, it opens the Excel spreadsheet, then I'm receiving the following error message: Run-time error '1004': Method 'Worksheets' of object '_Global failed. I'm new to Word Macros and unable to determine what is causing this error, as I'm not sure if it is attempting to create multiple copies of the spreadsheet or an issue with the objects. Further guidance is appreciated it.

Sub CommandButton1_Click()


'// Declare Excel Objects
Dim xlApp As Excel.Application
Dim xlwb As Excel.Workbook
Dim sh As Worksheet
Dim lr As Long

'// Declare Word Objects
Dim doc As Document
Dim tbl As Table
Dim tbl2 As Table
Dim tbl8 As Table

Dim LastRow As Long, LastColumn As Integer
Dim tblRange1 As Variant 'Range
Dim tblRange2 As Variant 'Range
Dim tblRange3 As Variant 'Range
Dim tblRange4 As Variant 'Range
Dim tblRange5 As Variant 'Range
Dim tblRange6 As Variant 'Range
Dim tblRange7 As Variant 'Range
Dim tblRange8 As Variant 'Range
Dim tblRange9 As Variant 'Range
Dim tblRange10 As Variant 'Range

 Dim AnswerYes As String
 Dim AnswerNo As String

Set doc = ThisDocument
  
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True


'Open Workbook
Set xlwb = xlApp.Workbooks.Open("C:\Users\Test Document.xlsm")
Set sh = Worksheets("CHECKLIST")

 AnswerYes = MsgBox("Do you want to Submit Application?", vbQuestion + vbYesNo, "User Repsonse")

 If AnswerYes = vbYes Then

Set tbl = doc.Tables(1)
Set tbl2 = doc.Tables(2)
Set tbl8 = doc.Tables(8)

lr = sh.Cells(Rows.Count, 3).End(xlUp).Row + 1


'MsgBox tbl.Cell(1, 3).Range

With tbl

Set tblRange1 = .Cell(1, 3).Range ' Value from Word Document (Last Name)
tblRange1.Copy
sh.Cells(lr, "D").PasteSpecial xlPasteValues


Set tblRange2 = .Cell(1, 4).Range ' Value from Word Document (First Name)
tblRange2.Copy
sh.Cells(lr, "C").PasteSpecial xlPasteValues


Set tblRange3 = .Cell(3, 7).Range ' Value from Word Document (Phone)
tblRange3.Copy
sh.Cells(lr, "E").PasteSpecial xlPasteValues


Set tblRange4 = .Cell(5, 9).Range ' Value from Word Document (Email)
tblRange4.Copy
sh.Cells(lr, "F").PasteSpecial xlPasteValues


Set tblRange6 = .Cell(1, 9).Range ' Value from Word Document (Date of Application)
tblRange6.Copy
sh.Cells(lr, "T").PasteSpecial xlPasteValues


Set tblRange7 = .Cell(3, 3).Range ' Value from Word Document (Address)
tblRange7.Copy
sh.Cells(lr, "I").PasteSpecial xlPasteValues


Set tblRange8 = .Cell(5, 3).Range ' Value from Word Document (City)
tblRange8.Copy
sh.Cells(lr, "J").PasteSpecial xlPasteValues


Set tblRange9 = .Cell(5, 4).Range ' Value from Word Document (State)
tblRange9.Copy
sh.Cells(lr, "K").PasteSpecial xlPasteValues


Set tblRange10 = .Cell(5, 5).Range ' Value from Word Document (Zip)
tblRange10.Copy
sh.Cells(lr, "L").PasteSpecial xlPasteValues


End With


With tbl2

Set tblRange10 = .Cell(1, 4).Range ' Value from Word Document (SSN)
tblRange10.Copy
sh.Cells(lr, "H").PasteSpecial xlPasteValues

End With


With tbl8

Set tblRange10 = .Cell(1, 3).Range ' Value from Word Document (DOB)
tblRange10.Copy
sh.Cells(lr, "G").PasteSpecial xlPasteValues

End With

'
'With doc
'date1 = Format(Date, "mmddyy") & ".docx"
'LName = tbl.Cell(1, 3)
'FName = tbl.Cell(1, 4)

'.SaveAs FileName:=ActiveWorkbook.Path & "\Test Document - " & LName & ", " & FName & ", " & date1
'End With


MsgBox (" Your Application has been Submitted!  ")

'//Close obj Instance
Set xlwb = Nothing
Set xlApp = Nothing

Set tbl = Nothing
Set doc = Nothing

 Else
   'Range("A1:A2").Copy Range("E1")
 End If

doc.Close
xlApp.Quit
Set sh = Nothing

End Sub

Solution

  • As you are working in Word, VBA will look to the word object model every time you don't explicitly specify otherwise.

    If you write Set sh = Worksheets("CHECKLIST") in Excel, VBA will try to figure out what Worksheets mean. For that, it will look into a kind of global object called _Global. This object will then return the Worksheets-object of the Active Workbook.

    However, as we are in Word, VBA will look to a similar _Global object, but with all the Word stuff in it, not Excel. In Word, there is no thing as Worksheets, and this causes the error "Method 'Worksheets' of object '_Global failed".

    So what you have to do is to look carefully to your code and specify explicitly when you refer to something from the Excel world. For that statement it's easy: You want to access to worksheets from the workbook you just opened, so write

    Set sh = xlWb.Worksheets("CHECKLIST")
    

    You have some more places that needs to be fixed:

    lr = sh.Cells(sh.Rows.Count, 3).End(xlUp).Row + 1 
    

    Rows is unknown in Word and you need to tell VBA what Rows is. What you mean is the number of rows of the worksheet sh, so you need to specify that.

    (In fact, the statement sh.Cells(Rows.Count) is already dirty in Excel because Rows.Count will return the number of Rows of the Active sheet, not of the sheet sh. This is not a problem most cases as this number is identically, but if the Activesheet is a sheet from an old xls-file, the number might be different.)

    I saw one more place that needs to be fixed (line is currently commented), that is when you store the Doc and access =ActiveWorkbook.Path - again, there is no Activeworkbook in Word. Fix is easy:

    .SaveAs FileName:=xlWb.Path & "\Test Document ..."