Excel, binding, Multiple versions on one PC

[mdullni1 via AccessMonster.com]

I've read the posts and MS links on early and late binding. It seems the
scenario the articles refer to are multiple users (PCs) with different, but
only one, version of Excel.

In my situation, Office 2007 has been installed on my PC and our users' PCs
for the future. Office 2003 is still on the PCs and is the version they
currently use. My programs use early binding of the Excel object 11 library,
but because my PC and my users' PCs have both 2003 and 2007 on them, the
program creates the Excel spreadsheet in 2007.

How do I use late binding to specify to create the spreadsheet as a 97-2003
worksheet?

 

[Klatuu]

Rather than Dimming the xl object as Excel.Application, just dim it as Object.
When you specify Excel.Applicaton, that is early binding and it will use the
version installed on your machine. If you Dim it as Object, then when you
create the object in code as Excel.Application, that is late binding and will
use the version on the machine it is running on.

For example, this is Late Binding:

Dim xlApp As Object 'Application Object
Dim xlBook As Object 'Workbook Object
Dim xlSheet As Object 'Worksheet Object

Then in the code:

On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.

This is so that if the user has Excel open, it will use that instance, but
if not, it will create an instance.

 

[hobson01]

I've been struggling with the exact opposite side of this issue. I have
user's with both Office 2003 and 2007 on their machines. I'm working with MS
Access 2007. When I programatically launch MS Excel via the CreateObject
function their machines default to 2003 which causes problem because the
Excel template I'm attempting to run is based in 2007. So, I cobbled
together a centralized function which makes an explicite call through a shell
statement (that is, if Excel is not already running).... If I understand your
issue, it should work for you as well, just point the shell statement to the
office11 folder. Let me know how it goes.

Private Declare Sub Sleep Lib "kernel32" _
(ByVal dwMilliseconds As Long)

Public Function CreateExcel() As Excel.Application
Dim frm As Form
Dim iSection As Integer
Dim iTries As Integer
Dim bRunning As String

FinalAttempt:
'determine if Excel is running on the machine
On Error Resume Next
Set CreateExcel = GetObject(, "Excel.Application")

If Err.Number <> 0 Then
bRunning = False
Else
bRunning = True
End If

Err.Clear

'if it's running and we've successfully set the reference, exit the
function
'otherwise, plan B (initiate through shell)
If bRunning = True Then _
Exit Function

On Error GoTo ErrorHandler

iTries = 0

'making the call using a shell statement to accommodate potential for
mutliple versions of Excel
Shell "C:\Program Files\Microsoft Office\Office12\Excel.exe",
vbMinimizedFocus

'set focus to the active form so Excel can be logged to Running Object
Table (ROT)
'we'll always have action status form open when launching Excel
Forms!frmActionStatus.SetFocus

'earmark where we are for ErrorHandler if we need it
iSection = 1

Set CreateExcel = GetObject(, "Excel.application")

'resume normal error handling
iSection = 0

Exit Function

ErrorHandler:

If iSection = 1 Then
iTries = iTries + 1
If iTries < 20 Then
Sleep 500 ' wait 1/2 seconds
Resume 'resume code at the GetObject line
ElseIf iTries = 20 Then
'I've had difficulty in registering the ROT
'opening 2 Excel sessions seems to be a failsafe way of making it
happen
Resume FinalAttempt
Else
'it's getting ugly, wrap it up
MsgBox "GetObject still failing. Process ended.", _
vbMsgBoxSetForeground
End If
Else 'iSection = 0 so use normal error handling:
MsgBox Error$
End If
End Function

 

[hobson01]

Of course now that I post this, I notice an issue with placement of the
FinalAttempt: statement, it should be relocated just below iTries=0, prior to
the shell statement. Otherwise, you run the risk of an infinite loop

I've been struggling with the exact opposite side of this issue. I have
user's with both Office 2003 and 2007 on their machines. I'm working with MS
Access 2007. When I programatically launch MS Excel via the CreateObject
function their machines default to 2003 which causes problem because the
Excel template I'm attempting to run is based in 2007. So, I cobbled
together a centralized function which makes an explicite call through a shell
statement (that is, if Excel is not already running).... If I understand your
issue, it should work for you as well, just point the shell statement to the
office11 folder. Let me know how it goes.

Private Declare Sub Sleep Lib "kernel32" _
(ByVal dwMilliseconds As Long)

Public Function CreateExcel() As Excel.Application
Dim frm As Form
Dim iSection As Integer
Dim iTries As Integer
Dim bRunning As String

FinalAttempt:
'determine if Excel is running on the machine
On Error Resume Next
Set CreateExcel = GetObject(, "Excel.Application")

If Err.Number <> 0 Then
bRunning = False
Else
bRunning = True
End If

Err.Clear

'if it's running and we've successfully set the reference, exit the
function
'otherwise, plan B (initiate through shell)
If bRunning = True Then _
Exit Function

On Error GoTo ErrorHandler

iTries = 0

'making the call using a shell statement to accommodate potential for
mutliple versions of Excel
Shell "C:\Program Files\Microsoft Office\Office12\Excel.exe",
vbMinimizedFocus

'set focus to the active form so Excel can be logged to Running Object
Table (ROT)
'we'll always have action status form open when launching Excel
Forms!frmActionStatus.SetFocus

'earmark where we are for ErrorHandler if we need it
iSection = 1

Set CreateExcel = GetObject(, "Excel.application")

'resume normal error handling
iSection = 0

Exit Function

ErrorHandler:

If iSection = 1 Then
iTries = iTries + 1
If iTries < 20 Then
Sleep 500 ' wait 1/2 seconds
Resume 'resume code at the GetObject line
ElseIf iTries = 20 Then
'I've had difficulty in registering the ROT
'opening 2 Excel sessions seems to be a failsafe way of making it
happen
Resume FinalAttempt
Else
'it's getting ugly, wrap it up
MsgBox "GetObject still failing. Process ended.", _
vbMsgBoxSetForeground
End If
Else 'iSection = 0 so use normal error handling:
MsgBox Error$
End If
End Function

I've read the posts and MS links on early and late binding. It seems the
scenario the articles refer to are multiple users (PCs) with different, but

[quoted text clipped - 8 lines]

How do I use late binding to specify to create the spreadsheet as a 97-2003
worksheet?