Browse for folder/file dialogue box in VBA ?

[liamfitz]

I found the following code snippet, on an Excel VBA web-site, which I'm sure only needs some slight modification for use in Access. I'm getting an error on the method '.GetOpenFilename' associated with Application, ( for parsing the selected filename it appears, to a declared string variable 'Filename' ), any ideas would be gratefully received. Thanks.

Private Sub Command310_Click()
Dim Filter As String, Title As String, msg As String
Dim i As Integer, FilterIndex As Integer
Dim Filename As Variant
' File filters
Filter = "Word Files (*.docx),*.docx," & _
        "PDF Files (*.pdf),*.pdf," & _
        "All Files (*.*),*.*"
'   Default filter to *.*
    FilterIndex = 3
' Set Dialog Caption
Title = "Select File(s) to Open"
' Select Start Drive & Path
ChDrive ("E")
ChDir ("E:\Chapters\chap14")
With Application
    ' Set File Name Array to selected Files (allow multiple)
    Filename = .GetOpenFilename(Filter, FilterIndex, Title, , True)
    ' Reset Start Drive/Path
    ChDrive (Left(.DefaultFilePath, 1))
    ChDir (.DefaultFilePath)
End With
' Exit on Cancel
If Not IsArray(Filename) Then
    MsgBox "No file was selected."
    Exit Sub
End If
' Open Files
For i = LBound(Filename) To UBound(Filename)
    msg = msg & Filename(i) & vbCrLf ' This can be removed
    Workbooks.Open Filename(i)
Next i
MsgBox msg, vbInformation, "Files Opened" ' This can be removed
End Sub

 

[spikepl]

Here are some ideas:

1. Scroll down to the bottom of this page and inspect Similar Threads (this goes to prove how important it is to give a sensible title to one's post)


2. Whatever problem you have, most likely someone had it before, and it's probably dealt with on AWF. Use the search facilities or search using google:

site:www.access-programmes.co.uk YourSearchTerms

(Excel objects/methods are NOT the same as Access objects/methods)

 

[JANR]

If you want to use Excel to browse for files, then create an excel object to get methodes of excel application

ex:

Dim Filter As String, Title As String, msg As String
Dim i As Integer, FilterIndex As Integer
Dim Filename As Variant
[COLOR="Red"]Dim objExcel As Object[/COLOR]

[COLOR="red"]Set objExcel = CreateObject("Excel.Application")[/COLOR]

' File filters
Filter = "Word Files (*.docx),*.docx," & _
        "PDF Files (*.pdf),*.pdf," & _
        "All Files (*.*),*.*"
'   Default filter to *.*
    FilterIndex = 3
' Set Dialog Caption
Title = "Select File(s) to Open"
' Select Start Drive & Path
ChDrive ("d")
ChDir ("d:\")
With [COLOR="red"]objExcel[/COLOR].Application
        ' Set File Name Array to selected Files (allow multiple)
        Filename = .GetOpenFilename(Filter, FilterIndex, Title, , True)
        ' Reset Start Drive/Path
        ChDrive (Left(.DefaultFilePath, 1))
        ChDir (.DefaultFilePath)
End With
' Exit on Cancel
If Not IsArray(Filename) Then
    MsgBox "No file was selected."
    Exit Sub
End If
' Open Files
For i = LBound(Filename) To UBound(Filename)
    With [COLOR="red"]objExcel[/COLOR]
        .Workbooks.[COLOR="red"]Add[/COLOR] Filename(i)
        .Visible = True
        .UserControl = True
    End With
Next i

When working with automation be carefull not to leave orphan intances of the excel object, see this link: http://www.btabdevelopment.com/ts/excelinstance

JR

 

[liamfitz]

Thank you to both contributors. There are some useful pointers in your responses.