Open a folder of Excel files from Command Button in Access 2

[jpkeller55]

I am using the following code on an Access 2002 form that opens an excel file (FLNcalculator.xls) - works fine.
Is there a way to make the button open the folder so the user could select any of the excel files stored in that folder?

Private Sub Command16_Click()
Set Sh = CreateObject("WScript.Shell")
Sh.Run Chr(34) & "W:\PAH\Fln\FLNcalculator.xls" & Chr(34)
End Sub

Thanks!

 

[grtammi]

Have a look at faq705-1971.

HTH

Greg

"for me, the action is the juice.

 

[CautionMP]

jpkeller55,
You can do it without the API:

Private Sub Command16_Click()
Shell "explorer.exe W:\PAH\Fln\"
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[IGPCS]

Greg,
the thread FAQ is the best option i use it almost on every program that i need to open external files i use the fHandle and it works with charm (Great)

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[jpkeller55]

IGPCS:
I looked at the FAQ and pasted that code into a Module. I am not sure how to make it run from the command button on my form. I would like to try this code to see its functionality.

CautionMP:
I tried your code and it worked. It opens a button on the taskbar. Is there a way to make the window open on the desktop?

Thanks to both for your help! jpkeller55

 

[IGPCS]

ok i will paste it here

The first code copy it into a new module and save the module fHamdle then the second code i pated take that line and patse it to the button click event but type you directory including the file name and extension in stead of "C:\File.mdb"

Option Compare Database

Private Declare Function apiShellExecute 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 WIN_NORMAL = 1         'Open Normal
Public Const WIN_MAX = 2            'Open Maximized
Public Const WIN_MIN = 3            'Open Minimized

Private Const ERROR_SUCCESS = 32&
Private Const ERROR_NO_ASSOC = 31&
Private Const ERROR_OUT_OF_MEM = 0&
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&

Function fHandleFile(stFile As String, lShowHow As Long)
Dim lRet As Long, varTaskID As Variant
Dim stRet As String
    'First try ShellExecute
    lRet = apiShellExecute(hWndAccessApp, vbNullString, _
            stFile, vbNullString, vbNullString, lShowHow)
            
    If lRet > ERROR_SUCCESS Then
        stRet = vbNullString
        lRet = -1
    Else
        Select Case lRet
            Case ERROR_NO_ASSOC:
                'Try the OpenWith dialog
                varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " _
                        & stFile, WIN_NORMAL)
                lRet = (varTaskID <> 0)
            Case ERROR_OUT_OF_MEM:
                stRet = "Error: Out of Memory/Resources. Couldn't Execute!"
            Case ERROR_FILE_NOT_FOUND:
                stRet = "Error: File not found.  Couldn't Execute!"
            Case ERROR_PATH_NOT_FOUND:
                stRet = "Error: Path not found. Couldn't Execute!"
            Case ERROR_BAD_FORMAT:
                stRet = "Error:  Bad File Format. Couldn't Execute!"
            Case Else:
        End Select
    End If
    fHandleFile = lRet & _
                IIf(stRet = "", vbNullString, ", " & stRet)
End Function

and now this on the on click event

fHandleFile "C:\File.mdb", WIN_NORMAL

now this should work please let me know

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[jpkeller55]

IGPCS, that works great. Thanks for taking the time to explain...much appreciated. jpkeller

 

[IGPCS]

Your Welcome happy holidays

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY