Help with late binding for excel
I'm trying run the following code to open excel from word and copy the data into it. The code works fine in Office 2003 but when run on 2000 returns the error "could not open macro storage" followed by "Can't find project or library".
[VBA]Sub UpdateExcelWB()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim i As Integer
Dim LastRow As Object
Dim ws As Worksheet
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
' Set xlWB = xlApp.Workbooks.Add ' create a new workbook
' or
Set xlWB = xlApp.Workbooks.Open("W:\INTERGRP\PNDFiles\pndbook.xls")
' open an existing workbook
' example excel operations
With xlWB.activesheet
Set ws = ActiveWorkbook.Worksheets("Sheet1")
Set LastRow = ws.Range("a65536").End(xlUp)
LastRow.Offset(1, 0).Value = UserForm1.officerbox.Text
LastRow.Offset(1, 1).Value = UserForm1.offencelocationCombo_Box.Text
LastRow.Offset(1, 2).Value = UserForm1.PNDbox.Text
LastRow.Offset(1, 3).Value = UserForm1.issuedatebox.Text
LastRow.Offset(1, 4).Value = UserForm1.offenceCombo_Box.Text
LastRow.Offset(1, 5).Value = genericcheckform.violencecombo_box.Text
End With
xlWB.Close True 'Closes the workbook
xlApp.Quit ' close the Excel application
Set xlWB = Nothing
Set xlApp = Nothing
End Sub
[/VBA]
I'm pretty sure this is due to early binding being used which of course then uses different references in the different versions.
I've had some help before re changing code to use late binding but it didn't properly sink in. I know I should change the referencing DIMs to Objects. And I think I then use the object library to replace the references but that's where I get stuck. If someone could help me with it I'd appreciate it.
Thanks
David