Solved Get the Excel current last row no at Word VBA

October 19, 2016 at 00:07:23
Specs: Windows 7, i5 2520M / 8 GB
I have a Word document and VBA code in this Word document. I want to exec Word VBA to get a Excel last row no. Bellow is the Word VBA code, but word tell me the systex error.

Sub WordGetExcelLastRowNo()
Dim ObjExcel As Object, ObjWorkBook As Object, ObjWorksheet As Object
Set ObjExcel = CreateObject("EXCEL.APPLICATION")
Set ObjWorkBook = ObjExcel.workbooks.Open("D:\test\TestTableList.xlsx")
Set ObjWorksheet = ObjWorkBook.worksheets("TableList")

LastRowNo = ObjWorksheet.Cells(Rows.Count, "A").End(xlUp).Row ' Word show this line error

MsgBox (LastRowNo)
ObjWorkBook.Save
ObjWorkBook.Close
Set ObjWorksheet = Nothing
Set ObjWorkBook = Nothing
ObjExcel.Quit
Set ObjExcel = Nothing
End Sub

Please assist me to resolve this problem. Thanks!!!


See More: Get the Excel current last row no at Word VBA

Report •

#1
October 20, 2016 at 01:51:20
✔ Best Answer
Try this code, make sure you set a reference to 'Microsoft Excel xx.x Object Library'

where xx.x is the version installed, I have office 2010 on my machine so it is shown as

'Microsoft Excel 14.0 Object Library'

The issue you have is that the syntax used is for Excel and therefore Word does not understand what Cell is, you can either define cell as Excel.Cells or use my code below which does the same thing but in a better way.

Sub GetLastRow()
    
    Dim iExcel As Object
    Dim lastrow As Long
    Dim Range As Excel.Range
    
    
    Set iExcel = CreateObject("Excel.Application")
    Let iExcel.Visible = True
    
    iExcel.workbooks.Open ("D:\test\TestTableList.xlsx")
    
    lastrow = iExcel.Worksheets("TableList").Range("A" & iExcel.Worksheets("TableList").Rows.Count).End(xlUp).Row
    
    Debug.Print lastrow
    
End Sub

message edited by AlwaysWillingToLearn


Report •

#2
October 20, 2016 at 23:47:30
Hi
Thank you for your assistant.

I checked my Excel VBA config(VBE > Tools > References > Microsoft Word 14.0 Object Library is enable) . And I also get the TableList row properly.

I test your code and get the compiler error at Dim Range As Excel.Range.

Because my Windows environment is Traditional Chinese. So I translate the error message as below:
" User defined type net configed" I hope you may understand.

Could you tell me how to correct it.

Thank you very much!!!


Report •

#3
October 21, 2016 at 00:23:54
Which version of MS Office do you have installed on your machine?

Report •

Related Solutions

#4
October 21, 2016 at 00:32:02
Hi
My MS Office version is 2010

Report •

#5
October 21, 2016 at 04:16:02
The problem is you need the EXCEL object library as well as WORD

Keep

'Microsoft WORD14.0 Object Library'

but also add

'Microsoft EXCEL 14.0 Object Library'

message edited by AlwaysWillingToLearn


Report •

#6
October 23, 2016 at 19:03:58
Hi
I add the 'Microsoft EXCEL 14.0 Object Library' at the WORD VBA Library, it worked.

that is we need to add 'Microsoft EXCEL 14.0 Object Library' and 'Microsoft WORD14.0 Object Library' togather.

Thank you very much!!!


Report •

Ask Question