trying to use access to open an excel spreadsheet when 2 versions of excel are in use

[slangan]

Hi ,
I have a macro in excel and I am trying to run it from an access database.
All works well on my machine but when I moved to another it had a problem.
I have since realised that the reason is that the 2 versions of Excel are different . Mine is Excel 2003 and the other is Excel 2002. This means that my path has OFFICE11 and the other has OFFICE10 which is why I get the message that it can't find the path on the other machine.
I wanted to know if there was an easy way to handle this other than creating 2 macro's ( in access ) - 1 for 2002 users and 1 for 2003 users.
I am running access 97.

Any suggestions?

Thanks

[ADezii]

Hi ,
I have a macro in excel and I am trying to run it from an access database.
All works well on my machine but when I moved to another it had a problem.
I have since realised that the reason is that the 2 versions of Excel are different . Mine is Excel 2003 and the other is Excel 2002. This means that my path has OFFICE11 and the other has OFFICE10 which is why I get the message that it can't find the path on the other machine.
I wanted to know if there was an easy way to handle this other than creating 2 macro's ( in access ) - 1 for 2002 users and 1 for 2003 users.
I am running access 97.

Any suggestions?

Thanks

Before you do anything, verify the Excel Version. I'm not sure if this will work in Access 97, but it is worth a try:

Dim objExcel As Excel.Application, strExcelPath As String
 
Set objExcel = New Excel.Application
 
Select Case objExcel.Version
  Case "10.0"
    strExcelPath = "C:\Program Files\Microsoft Office\OFFICE10\"
    'processing for Excel 2002
    '...
  Case "11.0"
    strExcelPath = "C:\Program Files\Microsoft Office\OFFICE11\"
    'processing for Excel 2003
    '...
End Select
 
'Debug.Print "Excel Path = " & strExcelPath
 
Set objExcel = Nothing

[Jim Doherty]

Hi ,
I have a macro in excel and I am trying to run it from an access database.
All works well on my machine but when I moved to another it had a problem.
I have since realised that the reason is that the 2 versions of Excel are different . Mine is Excel 2003 and the other is Excel 2002. This means that my path has OFFICE11 and the other has OFFICE10 which is why I get the message that it can't find the path on the other machine.
I wanted to know if there was an easy way to handle this other than creating 2 macro's ( in access ) - 1 for 2002 users and 1 for 2003 users.
I am running access 97.

Any suggestions?

Thanks

Another method for you to consider for your Acc97 app would be to find the executable file ie the EXCEL.EXE file associated with an excel spreadsheet and base your decision on the fully formed return value filepath provided by a simple call to the windows API. This would then provide for targetting the excel.exe file wherever the installation of EXCEL actually is on the host PC

Paste the following into a new module and save it

 Option Explicit 
Const cMAX_PATH = 260
Const ERROR_NOASSOC = 31
Const ERROR_FILE_NOT_FOUND = 2&
Const ERROR_PATH_NOT_FOUND = 3&
Const ERROR_BAD_FORMAT = 11&
Const ERROR_OUT_OF_MEM = 0
 
Private Declare Function apiFindExecutable Lib "SHELL32.DLL" _
    Alias "FindExecutableA" _
    (ByVal lpFile As String, _
    ByVal lpDirectory As String, _
    ByVal lpResult As String) _
    As Long
 
Function fFindEXE(stFile As String, _
                    stDir As String) _
                    As String
 
Dim lpResult As String
Dim lngRet As Long
    lpResult = Space(cMAX_PATH)
    lngRet = apiFindExecutable(stFile, stDir, lpResult)
 
    If lngRet > 32 Then
        fFindEXE = lpResult
    Else
        Select Case lngRet:
            Case ERROR_NOASSOC: fFindEXE = "Error: No Association"
            Case ERROR_FILE_NOT_FOUND: fFindEXE = "Error: File Not Found"
            Case ERROR_PATH_NOT_FOUND: fFindEXE = "Error: Path Not Found"
            Case ERROR_BAD_FORMAT: fFindEXE = "Error: Bad File Format"
            Case ERROR_OUT_OF_MEM: fFindEXE = "Error: Out of Memory"
        End Select
    End If
End Function
 

and type the followng into the immediate window where jim.xls represents the name of your spreadsheet and C:\jim represents the folder in which your spreadsheet is stored

 ?fFindEXE("jim.xls","c:\jim")

In the case of a default installation of office 2000 the return result would be

'C:\Program Files\Microsoft Office\Office\EXCEL.EXE'

You of course base your logic on whatever filepath is returned in your particular case

Regards

Jim

[ADezii]

Hi ,
I have a macro in excel and I am trying to run it from an access database.
All works well on my machine but when I moved to another it had a problem.
I have since realised that the reason is that the 2 versions of Excel are different . Mine is Excel 2003 and the other is Excel 2002. This means that my path has OFFICE11 and the other has OFFICE10 which is why I get the message that it can't find the path on the other machine.
I wanted to know if there was an easy way to handle this other than creating 2 macro's ( in access ) - 1 for 2002 users and 1 for 2003 users.
I am running access 97.

Any suggestions?

Thanks

Actually, there is a simpler method:

Dim objExcel As Excel.Application, strExcelPath As String
Set objExcel = New Excel.Application

strExcelPath = objExcel.Path & "\"

Debug.Print strExcelPath

Set objExcel = Nothing

OUTPUT:

C:\Program Files\Microsoft Office\Office\

[Jim Doherty]

Actually, there is a simpler method:

Dim objExcel As Excel.Application, strExcelPath As String
Set objExcel = New Excel.Application

strExcelPath = objExcel.Path & "\"

Debug.Print strExcelPath

Set objExcel = Nothing

OUTPUT:

C:\Program Files\Microsoft Office\Office\

Hey ...way to go ADezzi now why didn't I think of that one Head full of code or cold maybe haha

Jim

[cyberdwarf]

Something like this might be more useful (I assume that the Excel installation path might be changed from the default and therefore not prove to be so reliable):-

Dim objExcel As Excel.Application, strExcelPath As String
Set objExcel = New Excel.Application
 
Debug.Print objExcel.Version
Set objExcel = Nothing

Steve