Help with late binding for excel

[davidboutche]

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

[Oorang]

Hi David,
It sounds like you might still need to remove the reference to Excel from the project references. (In the VBE: Tools>References) After you do this, you will also want to change the declarations of Excel.Application and Excel.Workbook to the generic "Object" type:
[VBA] Dim xlApp As Object
Dim xlWB As Object
[/VBA]

[davidboutche]

Thanks Aaron

I had had a go at doing that in my attempts to correct it but it then moves the error on to the DIM WS as WorksSheet "error user defined" type not defined.....

[Oorang]

Hi David,
Did you change it to: "Dim WS as Object"?

[davidboutche]

I'm afraid I tried that too.

It now returns the message "Run-time error '424', Object required.

If I click debug it highlights the line

[VBA]Set ws = ActiveWorkbook.Worksheets("Sheet1")[/VBA]

The rest of the code looks like this now:

[VBA]Sub UpdateExcelWB()
Dim xlApp As Object
Dim xlWB As Object
Dim i As Integer
Dim LastRow As Object
Dim ws As Object
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]

Any Ideas please?

[Oorang]

Yep,
Activeworkbook is actually a property of the Application Class. If you use "Activeworkbook" within excel, VBA will automatically assume you mean the local instance of the application object. However when you are not within Excel, you need to specify which Excel application's active workbook (it is possible you might be running more than one instance of Excel). To fix this, just prefix ActiveWorkbook with the Application object in question:
[VBA]Set ws = xlApp.ActiveWorkbook.Worksheets("Sheet1") [/VBA]

[davidboutche]

Getting there but not quite yet...

Now I get the error message:

Run-time error '1004'
Application - Defined or object - defined error.


Half of me wants to understand why this is happening as a learning curve but I'm tempted just to use early binding on and older version of office and as my template will be read only, let office upgrade the library each time if needed.

[Oorang]

The easiest way to do early binding, is to set the reference so you can enjoy the intellisense (the auto-prompting). Then declare your objects using the Excel.Worksheet syntax. Do all you development, making sure you start everything off of the Application object, look up the values of any constants you used, then unset your reference and change your Dim to "Object".

The main trick is to fully qualify all of your objects. So if you are referring to a Range. Don't just say "Range("A1").Value". Make sure that everything is either attached to the application object, or attached to another object that has already been attached to the application object.

I tested this out successfully:
[vba]Option Explicit

Sub UpdateExcelWB()
'When you have the reference set, use the object explorer (F2) to find the
'values of the constants:
Const xlUp As Long = -4162&
Dim xlApp As Object
Dim xlWB As Object
Dim i As Integer
Dim LastRow As Object
Dim ws As Object
Set xlApp = CreateObject("Excel.Application")
'xlApp.Visible = False <- Applications are invisible by default.

' Set xlWB = xlApp.Workbooks.Add ' create a new workbook
' or
Set xlWB = xlApp.Workbooks.Open("C:\test\test.xls")
' open an existing workbook
' example excel operations

With xlWB.activesheet
Set ws = xlApp.ActiveWorkbook.Worksheets("Sheet1") '<Activeworkbook needs to be prefixed.
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]

[davidboutche]

That's fantastic.

It was looking up the intrinsic value of xlup that had caught me. I didn't know how to look up the values and hadn't spotted that it was used.

I think if I've got this right - and feel free to correct or edit;

Early binding -

You set the reference to the application you want to use in Tools-References

That then exposes the model of the application for you to use as you will in the code.

When the project is compiled, it binds together allowing full usage.

To use late binding -

Prepare the project using early binding including setting the references

Press F2 to get the object browser

Search for all the values such as 'xlup' and make a note of their value.

Declare that value eg
[VBA]Const xlUp As Long = -4162&[/VBA]

Change the other Declarations from their reference to objects eg
[VBA]Dim xlApp As Excel.Application [/VBA]
becomes
[VBA]Dim xlApp As Object [/VBA]

Use fully qualified qualified objects.

And finally REMOVE the references from Tools-References

Hey presto - it runs on multiple versions of office without the need to upgrade or downgrade the reference library.

Thanks for the help.

[Oorang]

No problem