By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,855 Members | 865 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,855 IT Pros & Developers. It's quick & easy.

Open Excel from Access

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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]

Nov 13 '05 #2

P: n/a
That works fine - thanks

"Trevor Best" <no****@besty.org.uk> wrote in message
news:42**********************@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]

Nov 13 '05 #3

P: n/a
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.AddFrom File
("C:\Program Files\Microsoft Office\Office\Excel9.olb")
Case "Excel.Application.10"
Application.VBE.ActiveVBProject.References.AddFrom File
("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

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.