Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Opening an Excel file from a variable file name (Access2000)

    I would like to be able to open an excel file from access for which I have the file name stored in a table. In VB, I have set up a variable with a dlookup to get the file name. I think I need to use the Shell function but I can't resolve the syntax for using a variable as the filename. Any help would be very much appreciated.
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opening an Excel file from a variable file name (Access2000)

    There are (at least) three methods you can use. In what follows, I have used strFileName as name for the variable; replace it by the name you use.

    1. Shell

    You need to specify the path to Excel.exe and the path to the document as strings:

    Shell Chr(34) & "C:Program FilesMicrosoft OfficeOffice10Winword.exe" & Chr(34) & " " & Chr(34) & strFileName & Chr(34)

    The instruction above should be on one line; your browser may break the line into two. The Chr(34)'s insert double quotes into the string that acts as argument to Shell. Depending on the speed of your PC, you may see a command prompt window appear and disappear. You'll run into problems if Excel hasn't been installed in the default location.

    2. FollowHyperlink.

    If you open an Excel file from a hyperlink, you don't have to specify the path to the executable:

    Application.FollowHyperlink strFileName

    Very easy, but it will probably activate the Web toolbar, which may irritate users.

    3. ShellExecute API function.

    Put the following at the top of a module:

    ' API-functie

    Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
    (ByVal hwnd As Long, _
    ByVal lpOperation As String, _
    ByVal lpFile As String, _
    ByVal lpParameters As String, _
    ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long

    Public Const SW_SHOWMAXIMIZED As Long = 3

    Use it as follows to open an Excel file:

    Dim lngResult As Long

    lngResult = ShellExecute(hWndAccessApp, "Open", _
    GetPath(strFileName, 0&, 0&, SW_SHOWMAXIMIZED)

    If lngResult <= 32 Then
    MsgBox "Couldn't open file.", vbExclamation
    End If

    This is the longest code, but it works very well without side effects.

  3. #3
    New Lounger
    Join Date
    Oct 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening an Excel file from a variable file name (Access2000)

    Thanks Hans, I used the 2nd option using Followhyperlink. The web tools won't bother us too much.
    Thanks again for your quick reply.
    Jon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •