Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 118777
  • Last Modified:

VBA: Open File Browser to select path of multiple files

Hello!

I need someone to help me with the code on how to open a file browser dialog box that can be used to select more than one ".xls" file. Similar to the one used in Outlook when selecting attachments that are more than one. I want to be able to get the complete path along with the filename of the selected/highlighted items and put them in a array. Any help would be great :-)

I'm using Windows XP with Access 2000 for this one.

Thanks!
0
rusco
Asked:
rusco
  • 8
  • 7
  • 6
  • +1
1 Solution
 
Data-ManCommented:
If you have acccess to Excel 2002 or greater you can use the this code by setting a reference and creating an object variable within your Access 2000 code.  I made a few changes, but this is air code.  If you don't have access to Excel 2002, disregard...you may have to use an API to get the file open dialog.....Mike


Sub UseFileDialogOpen()

    Dim lngCount As Long
    Dim strFileNames() as String
    Dim objExcel As Excel.Application
    Set objExcel = new Excel.Application
    ' Open the file dialog
    With objExcel.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = True
        .Show

        ' Display paths of each file selected
        For lngCount = 1 To .SelectedItems.Count
            ReDim Preserve strFileNames(lngcount-1) 'Array starts at zero.
            strFileNames(lngCount-1) = .SelectedItems(lngCount)
        Next lngCount

    End With
    objExcel.Quit
    set objExcel = nothing
End Sub
0
 
ruscoAuthor Commented:
Hi Mike,

Thanks for the code. Sorry to say that in the environment that we will be using the Access program for there is a possibility that some of the users will not have Excel 2002 installed in their system. Thanks for replying =)
0
 
Data-ManCommented:
Check out this link....it has the code for an API call.  API's are great....lots of code not so great, but they always work.

http://www.experts-exchange.com/Databases/MS_Access/Q_20116033.html?query=file+open+dialog+api&topics=39

Mike
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jjafferrCommented:
Hi rusco

Here what you can do:
1- Open the Directory (not File) dialog, to select the Directory where your files are (I can provide code).
2- Once the Directory is selected, use the fso (File System Object) to add the files to a Listbox (I can provide code).
3- In the Listbox properties, set Multiple selection to Yes.
4- Now you can do your Multiple file selection.
5- Make a command button to loop throw the selected Listbox items and put them in a array (I can provide code).

If you always search from the same Directory, then You can Omit #1, and put the Directory in #2,
If you always search for xls files, then you can omit the other types from being built in the Listbox.

jaffer
0
 
jjafferrCommented:
Hi rusco

When you are lucky, then you are lucky :o)

1-
Make a module, call it MultipleFileSelection, and paste this code there:

Option Explicit

' 28-May-2001
' Valid in VB 6 only!
' When using the Common Dialog control to select multiple files
' from the File Open dialog box, the FileName property of the
' Common Dialog control will return both the path and names of
' the selected files. If the Common Dialog control only allows
' long file names to be selected, the path and file names are
' returned in a null character delimited format, with the path
' listed first, and the files listed subsequently
' This sample shows you how to get the path and the selected files
' Demo by Brian Matumbura
' For comments email: brianm@zncc.co.zw


Public Function GetFiles(Optional ByVal sTitle As String = "Open files...") As String
  ' sTitle: Optional Title of Dialog
  Dim sFilenames As String
  Dim cdlOpen As Object
  On Error GoTo ProcError
  ' Get the desired name using the common dialog
  Set cdlOpen = CreateObject("MSComDlg.CommonDialog")
  ' set up the file open dialog file types
  With cdlOpen
    ' setting CancelError means the control will
    ' raise an error if the user clicks Cancel
    .CancelError = True
    .Filter = "All Files (*.*)|*.*|Text Files (*.txt)|*.*.txt|INI Files (*.ini)|*.ini|Images (*.bmp;*.jpg;*.gif)|*.bmp;*.jpg;*.gif"
    .FilterIndex = 1
    .DialogTitle = sTitle
    .MaxFileSize = &H7FFF ' 32KB filename buffer
    ' set up Common Dialog flags
    ' same as .Flags = cdlOFNHideReadOnly Or cdlOFNPathMustExist Or cdlOFNLongNames Or cdlOFNAllowMultiselect or cdlOFNExplorer
    .Flags = &H4 Or &H800 Or &H40000 Or &H200 Or &H80000
    .ShowOpen
    ' get the selected name
    sFilenames = .FileName
  End With
ProcExit:
  GetFiles = sFilenames
  Set cdlOpen = Nothing
  Exit Function
ProcError:
  If Err.Number = &H7FF3 Then Resume Next 'Cancel selected - Ignore
  MsgBox Err.Description & "(" & Err.Number & ")", vbExclamation, "Open error"
  sFilenames = ""
  Resume ProcExit
End Function


2-
Make a Form from Design view,
Make a Listbox, call it List1,
Make a Lable for the Listbox, call it Label1,
Make a command button, call it command2, and place the following code On Click of Command2:

Private Sub Command2_Click()

  Dim strFileNames() As String
  Dim i As Integer
 
  strFileNames = Split(GetFiles, Chr(0))

    If UBound(strFileNames) > 0 Then

      'Path is stored in index 0 of array files in index 1...
      Label1.Caption = "Files selected from: " & strFileNames(0)
      For i = 1 To UBound(strFileNames)
        If i = 1 Then
            AddFiles = strFileNames(0) & strFileNames(i)
        Else
            AddFiles = AddFiles & ";" & strFileNames(0) & strFileNames(i)
        End If
      Next
    Else
      AddFiles = "(No files selected)"
    End If
   
    Me.List1.RowSource = AddFiles

End Sub

--------------------------------
You can get he Path and File Name you are looking for, like this:

For i = 1 To UBound(strFileNames)
 mtFiles(i)=strFileNames(0) & strFileNames(i)
next i

This way, the array myFiles() will contain all your files.


jaffer
0
 
ruscoAuthor Commented:
Hi Jaffer,

I tried using the code you gave me but I seem to be getting an "ActiveX Component can't create object(429)' error.

Hi Mike,

I like the idea of the API and tried out the code from the link you gave. Unfortunately it only can only select one file from open file browse 'dialog box'. I can't figure out yet how to change the API to make it able to select multiple files.

Thanks!

rusco
0
 
jjafferrCommented:
rusco

Make sure you have your reference to:

Visual Basic For Applications
Microsoft Access XX.X object Library
Microsoft Visual Basic for Applications Extensibility 5.3

To do the Reference, while in VB, Menu Bar > Tools > References.

If it doesn't work, let me know so that I can upload it to a site where you can download it.

jaffer
0
 
ruscoAuthor Commented:
Hi jaffer,

I added the reference but I'm still getting the same error as before. Any suggestions?

thanks!
0
 
jjafferrCommented:
0
 
jjafferrCommented:
if you open the module, you will see this line

    .Filter = "All Files (*.*)|*.*|Text Files (*.txt)|*.*.txt|INI Files (*.ini)|*.ini|Images (*.bmp;*.jpg;*.gif)|*.bmp;*.jpg;*.gif"

it is here where you can either Edit OR Add pointing to *.xls so that you can filter the xls files directly.

BTW, Click on the Open button, the dialog will popup, there you can select your multiple files.

I am using WinME and Access 2k

jaffer
0
 
jjafferrCommented:
Hi rusco

Were you able to download the file?

jaffer
0
 
ruscoAuthor Commented:
Hi Jaffer!

Just downloaded it now. Will test it out and get back to you about it tomorrow morning as it's kinda late here. Thanks!

rusco
0
 
jjafferrCommented:
No problem
Nighty night

jaffer
0
 
ruscoAuthor Commented:
Hi Jaffer!

Tried it out just now. Unfortunately it still gives me the same error as I stated above. I tried it out in two pc's (on my personal desktop and the one I use at work). Any ideas?

Thanks!

Mike,

Hi Mike! Any new developments with regards to the API? Thanks!

rusco
0
 
Data-ManCommented:
Add the ALLOWMULTISELECT flag to the code.  I checked it out and this flag is a constant in the code.  You should be able to add the flag.  You might have to add a loop around the code to return all selected files.

Might be worth while to do a search on google or some other site for the name of the api call and the word ALLOWMULTISELECT.

Mike
0
 
ruscoAuthor Commented:
Mike,

Thanks! I the API to work properly. It's able to now select multiplefiles. Must have missed the ALLOWMULTISELECT flag when I read the API.

0
 
Data-ManCommented:
Glad you got it to work....using an API means the code will run on almost every machine...you don't have to worry about ocx or controls...Mike
0
 
jjafferrCommented:
rusco
I am sorry to hear this, as this mdb is working fine with me.
but I gues, like Mike said "API's are great....lots of code not so great, but they always work."
where as other codes depend on some installed components on the pc.

Good job Mike.

jaffer
0
 
LuigiCCommented:
This is wonderful.

Just one little tweak required if possible.

How do I modify this code to allow the user to select one only access file (.mdb)?

...ie not multiple files and only files of type *.mdb

Cheers

Luigi
0
 
Data-ManCommented:
Lugi,
  Just don't include the 'ALLOWMULTISELECT' flag when opening the dialog.

Mike
0
 
LuigiCCommented:
Thanks Mike

That's all it took. I removed that flag and hey presto!

Cheers

Luigi
0
 
Data-ManCommented:
Glad you got it to work.

Don't forget to close out the question.

Regards,
Mike

0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 8
  • 7
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now