use of Common Dialog Box to Locate and Launch MS Access file(s) 1

[h4fod]

Hi
I am having difficultly launching a file, in my case an MS Access file when selected using the Common Dialog Box object using source code below. Do I need to add additional source code to 'shell' the access application? Code samples are ideal for understanding how dialog is used to filter and select files.
In anticipation, many thanks.

Private Sub cmdopenDatabase_Click()
With comDlg
        comDlg.DialogTitle = "Open database File"
        comDlg.Filter = "Microsoft Access Files (*.mdb)|*.mdb"
        comDlg.FilterIndex = 1
        comDlg.Flags = cdlOFNFileMustExist + cdlOFNHideReadOnly
        comDlg.CancelError = True
End With


' Enables error handling to catch cancel error
On Error Resume Next
' display the dialog box
comDlg.ShowOpen


If Err Then
    MsgBox "Dialog Cancelled"
    Exit Sub
End If
MsgBox "You selected the following Access Database File: " & comDlg.FileName

End Sub

 

[HughLerwill]

>Do I need to add additional source code to 'shell' the access application?

Yes you do, the Common Dialog will only give you the selected path\filename in a string.

 

[h4fod]

Hi
Thanks for your reply. Still struggling having looked at many Internet references to use of Shell command hence delay. I have adjusted my code to set the path of the Access executable on my computer and assigned the filepath established using common Dialog to a string variable (this is OK). Error trap traps an error so there is a problem with my source. Do I need additional arguments in the shell command?
many thanks.

Private Sub cmdopenDatabase_Click()
Dim strPathToFile As String
Dim strAppPath As String
With comDlg
        comDlg.DialogTitle = "Open database File"
        comDlg.Filter = "Microsoft Access Files (*.mdb)|*.mdb"
        comDlg.FilterIndex = 1
        comDlg.Flags = cdlOFNFileMustExist + cdlOFNHideReadOnly
        comDlg.CancelError = True
End With


' Enables error handling to catch cancel error
On Error Resume Next
' display the dialog box
comDlg.ShowOpen
strPathToFile = comDlg.FileName
MsgBox strPathToFile

strAppPath = "c:\Program Files\Microsoft Office\Office\MSAccess.exe"
Shell (AppPath & "\" & strPathToFile)

If Err Then
    MsgBox "Dialog Cancelled"
    Exit Sub
End If


End Sub




[\code]

 

[h4fod]

Hi
Thanks for your reply. Still struggling having looked at many Internet references to use of Shell command hence delay. I have adjusted my code to set the path of the Access executable on my computer and assigned the filepath established using common Dialog to a string variable (this is OK). Error trap traps an error so there is a problem with my source. Do I need additional arguments in the shell command?
many thanks.

Private Sub cmdopenDatabase_Click()
Dim strPathToFile As String
Dim strAppPath As String
With comDlg
        comDlg.DialogTitle = "Open database File"
        comDlg.Filter = "Microsoft Access Files (*.mdb)|*.mdb"
        comDlg.FilterIndex = 1
        comDlg.Flags = cdlOFNFileMustExist + cdlOFNHideReadOnly
        comDlg.CancelError = True
End With


' Enables error handling to catch cancel error
On Error Resume Next
' display the dialog box
comDlg.ShowOpen
strPathToFile = comDlg.FileName
MsgBox strPathToFile

strAppPath = "c:\Program Files\Microsoft Office\Office\MSAccess.exe"
Shell (AppPath & "\" & strPathToFile)

If Err Then
    MsgBox "Dialog Cancelled"
    Exit Sub
End If


End Sub

 

[Andrzejek]

You may find this handy:
open any file with default file association:

Public Sub OpenDocument(strDocPath As String)

Dim G As Long
G = Shell("RUNDLL32.EXE URL.DLL,FileProtocolHandler " & strDocPath, vbNormalFocus)
    
End Sub

So if you pass "C:\SomeFolder\MyFile.mdb" and this is associated with Access, you will open it in Access on your computer. It works great with other files: pdf with Acribat, doc with Word, xls with Excel and any other one.

Have fun.

---- Andy

 

[h4fod]

Hi Andy
Many thanks. A 'cool' solution to the problem - works well (as it says on the can for all apps proded file associations are correct)! Can you explain why, there is need to assign a return value when using shell? Is this just a syntax requirement because the variable is not subsequently used?

Again, many thanks for your help.
Mike

 

[strongm]

>because the variable is not subsequently used

Technically it should be checked for success or an error condition.

 

[Andrzejek]

I've got this code a long time ago from someone (could be someone from this Forum) and have used it successfully for a long time with no errors. But if it needs some error handling (checking the value of G maybe, what do you think strongm?) it could be improved.

As far as assigning Shell to a variable, I would guess Shell is a function returning a value, so it has to assign the value to something.


Have fun.

---- Andy

 

[strongm]

>checking the value of G maybe

Um ... just read what I said:

>>because the variable is not subsequently used

>Technically it should be checked for success or an error condition.

Shell returns an error code (in this case being assigned to G). It should be checked.

 

[h4fod]

Thanks both for your helpful responses. Will now look up return values and amend my code accordingy.
Mike