f



Open Excel from Access

   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.Application

Set xl = New Excel.Application
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 


0
m.fisher (3)
4/2/2005 9:04:23 AM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

3 Replies
627 Views

Similar Articles

[PageSpeed] 40

M Fisher wrote:
>    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.Application
> 
> Set xl = New Excel.Application
> 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.

Use late binding:

Dim xl as object
Set xl = CreateObject("Excel.Application")

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]

0
nospam1955 (773)
4/2/2005 10:49:46 AM
That works fine - thanks

"Trevor Best" <nospam@besty.org.uk> wrote in message 
news:424e78ab$0$5472$da0feed9@news.zen.co.uk...
>M Fisher wrote:
>>    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.Application
>>
>> Set xl = New Excel.Application
>> 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.
>
> Use late binding:
>
> Dim xl as object
> Set xl = CreateObject("Excel.Application")
>
> 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]
> 


0
m.fisher (3)
4/2/2005 10:59:00 AM
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.Application.9
goes to Excel.Application.10).  This doesn't seem to work in reverse
(i.e., Excel.Application.10 won't be downgraded to Excel.Application.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_ROOT = &H80000000
Const HKEY_CURRENT_USER = &H80000001
Const HKEY_LOCAL_MACHINE = &H80000002
Const HKEY_USERS = &H80000003

Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As
Long) As Long
Private Declare Function RegOpenKey Lib "advapi32.dll" Alias
"RegOpenKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, phkResult
As Long) As Long
Private Declare Function RegQueryValueEx Lib "advapi32.dll" Alias
"RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String,
ByVal lpReserved As Long, lpType As Long, lpData As Any, lpcbData As
Long) As Long
Sub SetExcelReferences()
    On Error GoTo Trapper
    Select Case GetExcelCurrentVersion
        Case "Excel.Application.9"
            Application.VBE.ActiveVBProject.References.AddFromFile
("C:\Program Files\Microsoft Office\Office\Excel9.olb")
        Case "Excel.Application.10"
            Application.VBE.ActiveVBProject.References.AddFromFile
("C:\Program Files\Microsoft Office\Office\Excel10.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 GetExcelCurrentVersion() As String
    'Get a string from the registry
    GetExcelCurrentVersion = GetString(HKEY_CLASSES_ROOT,
"Excel.Application\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 = RegQueryStringValue(Ret, strValue)
    'Close the key
    RegCloseKey Ret
End Function
Function RegQueryStringValue(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(lDataBufSize, 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
                RegQueryStringValue = 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
                RegQueryStringValue = strData
            End If
        End If
    End If
End Function

0
billmiami2 (162)
4/2/2005 11:39:29 AM
Reply: