Late Binding Help

[kopy]

I have an Object Reference issue. My PC has Microsoft Excel 12.0 Object Library and the PCs I'm going to install on have Microsoft Excel 11.0 Object Library. I've read about late binding, but I'm not sure how to implement the code. Here's the start of my current code:

Dim oApp As Object

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True

'Open the Workbook: Local
oApp.Workbooks.Open "c:\AnnualREport.xls"

DoCmd.SetWarnings False

'Sheet Settings
With oApp.ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
oApp.ActiveSheet.PageSetup.PrintArea = "$A$1:$O$8"

 

[RoyVidar]

I think it looks like a good start.

Personally, I'm a bit more explicit. See, when working with an automated application, like here, Excel, I want to be 100% sure which object I'm referring to. So I will avoid any Active<Thingies>.

This means I'd have a declaration for each level of objects I'll be working wiht, say

[tt]Dim oApp As Object
dim wr as object ' Excel.Workbook
dim sh as Object ' Excel.Worksheet

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True

'Open the Workbook: Local
' with this I'm 100% sure the workbook is "anchored" to
' the parent Excel object, and cannnot/shouldn't create
' any problems
set wr= oApp.Workbooks.Open("c:\AnnualREport.xls")

' DoCmd.SetWarnings False

'Sheet Settings
' same here, not whicever sheet that might be active,
' but selecting one specific sheet, and assign to an
' object variable - ensure it's "anchored" to the
' correct parent object variable - the workbook
set sh = wr.sheets(1)
With sh.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
.PrintArea = "$A$1:$O$8"
End With[/tt]

Another tip, is to develop using early binding (keep the reference), then switch to late binding when deploying. Note that any Excel constants will have to be replaced with their literal values, or you'll have to declare your own

[tt]with sh.range("a1").currentregion
.HorizontalAlignment = -4108 ' xlCenter
.VerticalAlignment = -4107 ' xlBottom
end with[/tt]

And, perhaps more important when automating than in other kind of programming, release the objects, in the correct order.

[tt]set sh = nothing
wr.close true ' close and save if necessary
set wr = nothing
oApp.Quit ' quit Excel if necessary
set oApp = nothing[/tt]

Roy-Vidar

 

[genomon]

I've read about late binding...

Then you will probably not want to use it.

Here is early binding:

Dim wr as Excel.Workbook

The idea is that the app runs faster if it doesn't have to take the time to figure out what datatype a variable is during compile or execution.

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[PHV]

genomon, how do you deploy your app in a multi office's version environment ?
The idea is that the app runs (in fact compiles and runs), even if slower ...

 

[genomon]

We have several local Access Frontends linked to Excel workbooks on a LAN and also to SQL Server tables.
Upgrades are simple in that each user deletes the old mdb & downloads a copy of the new FE from the network.
We don't use Access Data Projects yet, but I'm pushing in that direction. As far as your code goes,I'd say if it ain't broke, don't fix it.

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[genomon]

Oops - forgot about this:

http://msdn.microsoft.com/en-us/library/0tcf61s1(VS.80).aspx

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[PHV]

genomon, seems you didn't read carefully the OP ...