I have a macro in MS Word which copies Table data from the document to specified columns in an Excel spreadsheet, which is stored on my local drive.
The Word document contains a Submit button, which adds a new row of data to the spreadsheet. All submissions are copied to the master spreadsheet.
When the macro is run, it opens the Excel spreadsheet, then I receive:
Run-time error '1004': Method 'Worksheets' of object '_Global failed.
I'm not sure if it is attempting to create multiple copies of the spreadsheet or an issue with the objects.
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
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 ..."