Open Excel from Access

[M Fisher]

I have an Access XP Database. On one of the forms I have a button that
opens and Excel Spreadsheet as follows:

Dim xl As Excel.Applicati on

Set xl = New Excel.Applicati on
xl.Application. Workbooks.Add
xl.Cells(1,1) = "Some Text From the database"

etc.

I have set a reference to the Excel 10 Object Library.

The problem I have is that I have to send this application to different
users in our company, and some have Excel 2000, in which case they get an
error re the Excel 10 Object Library. Is there a way that the code will
work regardless of what version of Excel is on the target pc.

Thanks

[Trevor Best]

Re: Open Excel from Access

M Fisher wrote:[color=blue]
> I have an Access XP Database. On one of the forms I have a button that
> opens and Excel Spreadsheet as follows:
>
> Dim xl As Excel.Applicati on
>
> Set xl = New Excel.Applicati on
> xl.Application. Workbooks.Add
> xl.Cells(1,1) = "Some Text From the database"
>
> etc.
>
> I have set a reference to the Excel 10 Object Library.
>
> The problem I have is that I have to send this application to different
> users in our company, and some have Excel 2000, in which case they get an
> error re the Excel 10 Object Library. Is there a way that the code will
> work regardless of what version of Excel is on the target pc.[/color]

Use late binding:

Dim xl as object
Set xl = CreateObject("E xcel.Applicatio n")

You lose the intellisense while coding so a tip is use the early bound
method you originally used with the reference then change it once you've
finished coding (and remove the reference as you still get a compile
error even if it's not used).

--
[Oo=w=oO]

[M Fisher]

Re: Open Excel from Access

That works fine - thanks

"Trevor Best" <nospam@besty.o rg.uk> wrote in message
news:424e78ab$0 $5472$da0feed9@ news.zen.co.uk. ..[color=blue]
>M Fisher wrote:[color=green]
>> I have an Access XP Database. On one of the forms I have a button
>> that opens and Excel Spreadsheet as follows:
>>
>> Dim xl As Excel.Applicati on
>>
>> Set xl = New Excel.Applicati on
>> xl.Application. Workbooks.Add
>> xl.Cells(1,1) = "Some Text From the database"
>>
>> etc.
>>
>> I have set a reference to the Excel 10 Object Library.
>>
>> The problem I have is that I have to send this application to different
>> users in our company, and some have Excel 2000, in which case they get an
>> error re the Excel 10 Object Library. Is there a way that the code will
>> work regardless of what version of Excel is on the target pc.[/color]
>
> Use late binding:
>
> Dim xl as object
> Set xl = CreateObject("E xcel.Applicatio n")
>
> You lose the intellisense while coding so a tip is use the early bound
> method you originally used with the reference then change it once you've
> finished coding (and remove the reference as you still get a compile error
> even if it's not used).
>
> --
> [Oo=w=oO]
>[/color]

[billmiami2@netscape.net]

Re: Open Excel from Access

I know of 2 ways to handle this.

1. Do your development from an environment where the MS Office version
is the lowest. In this case, it may be Office 2000. When users with
later versions of Office open the application, the references will
automatically be reset to the later version (i.e., Excel.Applicati on.9
goes to Excel.Applicati on.10). This doesn't seem to work in reverse
(i.e., Excel.Applicati on.10 won't be downgraded to Excel.Applicati on.9
and the application will fail).

2. Remove your reference to Excel. Upon application startup, detect
the current excel version from the registry (uses API call), then add
the appropriate reference programatically . Code for this is below.
You may have to modify it to suit your needs.

Bill Ehrreich

_______________ _______________ _______________ ______
Const REG_SZ = 1 ' Unicode nul terminated string
Const REG_BINARY = 3 ' Free form binary
Const HKEY_CLASSES_RO OT = &H80000000
Const HKEY_CURRENT_US ER = &H80000001
Const HKEY_LOCAL_MACH INE = &H80000002
Const HKEY_USERS = &H80000003

Private Declare Function RegCloseKey Lib "advapi32.d ll" (ByVal hKey As
Long) As Long
Private Declare Function RegOpenKey Lib "advapi32.d ll" Alias
"RegOpenKey A" (ByVal hKey As Long, ByVal lpSubKey As String, phkResult
As Long) As Long
Private Declare Function RegQueryValueEx Lib "advapi32.d ll" Alias
"RegQueryValueE xA" (ByVal hKey As Long, ByVal lpValueName As String,
ByVal lpReserved As Long, lpType As Long, lpData As Any, lpcbData As
Long) As Long
Sub SetExcelReferen ces()
On Error GoTo Trapper
Select Case GetExcelCurrent Version
Case "Excel.Applicat ion.9"
Application.VBE .ActiveVBProjec t.References.Ad dFromFile
("C:\Program Files\Microsoft Office\Office\E xcel9.olb")
Case "Excel.Applicat ion.10"
Application.VBE .ActiveVBProjec t.References.Ad dFromFile
("C:\Program Files\Microsoft Office\Office\E xcel10.olb")
'Add additional cases here
End Select
Exit Sub
Trapper:
If Err.Number = 32813 Then
'The reference already exists. Exit.
Exit Sub
Else
MsgBox Err.Description
End If
Exit Sub
End Sub
Function GetExcelCurrent Version() As String
'Get a string from the registry
GetExcelCurrent Version = GetString(HKEY_ CLASSES_ROOT,
"Excel.Applicat ion\CurVer", "")
End Function
Function GetString(hKey As Long, strPath As String, strValue As String)
Dim Ret
'Open the key
RegOpenKey hKey, strPath, Ret
'Get the key's content
GetString = RegQueryStringV alue(Ret, strValue)
'Close the key
RegCloseKey Ret
End Function
Function RegQueryStringV alue(ByVal hKey As Long, ByVal strValueName As
String) As String
Dim lResult As Long, lValueType As Long, strBuf As String,
lDataBufSize As Long
'retrieve nformation about the key
lResult = RegQueryValueEx (hKey, strValueName, 0, lValueType, ByVal
0, lDataBufSize)
If lResult = 0 Then
If lValueType = REG_SZ Then
'Create a buffer
strBuf = String(lDataBuf Size, Chr$(0))
'retrieve the key's content
lResult = RegQueryValueEx (hKey, strValueName, 0, 0, ByVal
strBuf, lDataBufSize)
If lResult = 0 Then
'Remove the unnecessary chr$(0)'s
RegQueryStringV alue = Left$(strBuf, InStr(1, strBuf,
Chr$(0)) - 1)
End If
ElseIf lValueType = REG_BINARY Then
Dim strData As Integer
'retrieve the key's value
lResult = RegQueryValueEx (hKey, strValueName, 0, 0,
strData, lDataBufSize)
If lResult = 0 Then
RegQueryStringV alue = strData
End If
End If
End If
End Function