check for excel version before opening file exported from access query (1 Viewer)

[wiklendt]

hi everyone,

i adapted some code from Ken Higg's FE loader code in order to open an excel file (his was to open an access FE). this works fabulously, but now i need to update it to use whichever excel version is installed (folder path for 2007 includes "Office12", while all previous versions are "Office11", as far as i can tell).

i can do this by manually changing the constant, but have had no luck working it using VBA.

this is what i have:

    If intAnswer = vbYes Then 'open the excel file for the user
       
        'Location of MS Excel .exe (versions prior to Excel 2007)
        Const cstrMSExcelAPP = "C:\Program Files\Microsoft Office\Office11\EXCEL.exe"

        'Location of MS Excel .exe (Excel 2007 version)
        'Const cstrMSExcelAPP = "C:\Program Files\Microsoft Office\Office12\EXCEL.exe"
   
        Dim intX As Integer             'Utility var
        Dim strCommandLine As String    'Application to run and file to open
   
        'Build string
        strCommandLine = cstrMSExcelAPP & " /cmd " & Chr(34) & strExportFile & Chr(34)
   
        'Open exported excel file
        intX = Shell(strCommandLine, 3)
       
    Else
        Exit Sub
    End If

i just comment out whichever one is appropriate, but i'd like it to be automatic especially seeing as one day i might not be working in the same company and they'll one day upgrade to excel 2007 (or higher) and i want to my DB work!

i changed the "Const" to:

        Const cstrMSExcelAPP = Nz(Dir("C:\Program Files\Microsoft Office\Office11\EXCEL.exe"), "C:\Program Files\Microsoft Office\Office12\EXCEL.exe")

but it causes the button press to have no effect (with no error either). i've tried a few variations on the theme, including this additional code (and colourful variations thereof):

        Dim strExcelPath, strExcelPath1, strExcelPath2 As String
       
        'path for versions 2003 or earlier
        strExcelPath1 = "C:\Program Files\Microsoft Office\Office11\EXCEL.exe"
        'path for versions 2007 or later
        strExcelPath2 = "C:\Program Files\Microsoft Office\Office12\EXCEL.exe"
       
        If IsNull(Dir(strExcelPath1)) Then
            If IsNull(Dir(strExcelPath2)) Then
                MsgBox "Microsoft Excel not found. Cannot open file."
            Else
                strExcelPath = strExcelPath2
            End If
        Else
            strExcelPath = strExcelPath1
        End If
       
        'Location of MS Excel .exe
        Const cstrMSExcelAPP = strExcelPath

but no luck. it seems that if there is anything after "Const" that is different from the single path then it just flatlines.

can anyone help here?

 

[namliam]

And what will happen when we get office 2009/2010/2015?? Paths change...

Why not use VBA to find it for you?? Some links:
http://www.access-programmers.co.uk/forums/showthread.php?t=179960&highlight=Excel.application
http://www.access-programmers.co.uk/forums/showthread.php?t=181393&highlight=Excel.application
http://www.access-programmers.co.uk/forums/showthread.php?t=182220&highlight=Excel.application

Good luck !

 

[DCrake]

Wil.

Here is some code that will open any file in its native software without the instruction of which version to use

Place this in the forms declaration section

Private Declare Function ShellExecute Lib "shell32.dll" _
   Alias "ShellExecuteA" _
   (ByVal hWnd As Long, ByVal lpszOp As String, _
    ByVal lpszFile As String, ByVal lpszParams As String, _
    ByVal LpszDir As String, ByVal FsShowCmd As Long) _
    As Long
Private Declare Function GetDesktopWindow Lib "User32" () As Long

Const SW_SHOWNORMAL = 1

Dim sPath As String

In my for it works on the double click from a listbox, but you can adapt it to suit your needs.

    sPath = Me.LstFoundFiles.Column(1)
    strFile = sPath & Me.LstFoundFiles.Column(0)
    nDT = GetDesktopWindow()
    nApp = ShellExecute(nDT, "Open", strFile, "", "C:\", SW_SHOWNORMAL)
    DoEvents

David

 

[wiklendt]

Wil.

Here is some code that will open any file in its native software without the instruction of which version to use

Place this in the forms declaration section

Private Declare Function ShellExecute Lib "shell32.dll" _
   Alias "ShellExecuteA" _
   (ByVal hWnd As Long, ByVal lpszOp As String, _
    ByVal lpszFile As String, ByVal lpszParams As String, _
    ByVal LpszDir As String, ByVal FsShowCmd As Long) _
    As Long
Private Declare Function GetDesktopWindow Lib "User32" () As Long

Const SW_SHOWNORMAL = 1

Dim sPath As String

In my for it works on the double click from a listbox, but you can adapt it to suit your needs.

    sPath = Me.LstFoundFiles.Column(1)
    strFile = sPath & Me.LstFoundFiles.Column(0)
    nDT = GetDesktopWindow()
    nApp = ShellExecute(nDT, "Open", strFile, "", "C:\", SW_SHOWNORMAL)
    DoEvents

David

i had to declare in addition:

        Dim strFile As String
        Dim nDT, nApp

but wasn't sure what type for nDT and nApp?. still an open delcare as above still allows the code to function thanks DCrake, you're always a massive help!

my version is:

        Dim strFile As String
        Dim nDT, nApp

        strFile = strExportFile
        nDT = GetDesktopWindow()
        nApp = ShellExecute(nDT, "Open", strFile, "", "C:\", SW_SHOWNORMAL)
        DoEvents

(edit: where "strExportFile" i have declared/assigned data to upstream of this code)

the form declarations i just copy/pasted as is.

that's a really nice and neat little snippet of code - much more elegant than other massive modules i've seen that do a lot less than this. plus, i'm pretty sure i noticed that this code opens the file/excel much faster...

 

[DCrake]

I think the logic behind it is that when you use Shell you sometimes have to tell it which software to open it with, however ShellExecute lets the file type dictate the resident software. Works with .PDF, .Doc, .Txt, etc

David

 

[wiklendt]

I think the logic behind it is that when you use Shell you sometimes have to tell it which software to open it with, however ShellExecute lets the file type dictate the resident software. Works with .PDF, .Doc, .Txt, etc

David

it's beautiful elegant and versatile, and like namliam suggested - the code does the work for you