check for excel version before opening file exported from access query

wiklendt

wiklendt

i recommend chocolate
Local time
Tomorrow, 08:39
Joined
Mar 10, 2008
Messages
1,746
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:
Code: 
    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:
Code: 
        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):

Code: 
        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?
 
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
Code: 
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.

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

David
 
DCrake said:
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
Code: 
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.

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

i had to declare in addition:

Code: 
        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:

Code: 
        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...
 
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
 
DCrake said:
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
Click to expand...

it's beautiful :) elegant and versatile, and like namliam suggested - the code does the work for you :)
 
You must log in or register to reply here.

Similar threads

NickCoe(UK)
Getting the true location of VBA reference files in 365 ctr.
Replies
17
Views
327
NickCoe(UK)
NickCoe(UK)
J
Use VBA to export form data to Excel, but keep getting "We Found a Problem..." when opening the file in Excel
Replies
5
Views
127
June7
June7
K
How can I add this code to the storage location of my file before scanning and then convert it to a PDF file?
2
Replies
25
Views
599
The_Doc_Man
The_Doc_Man
A
Solved Compile error : User-defined type not defined (Dim xlApp As Excel.Application), due to problem with 'Microsoft Excel 16.0 Object Library' Reference
Replies
8
Views
1,614
theDBguy
theDBguy
I
Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)
Replies
4
Views
900
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom