Solved

VBA: Open File Browser to select path of multiple files

Posted on 2004-08-02
23
118,447 Views
Last Modified: 2011-08-18
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
Comment
Question by:rusco
  • 8
  • 7
  • 6
  • +1
23 Comments
 
LVL 18

Expert Comment

by:Data-Man
ID: 11691764
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
 

Author Comment

by:rusco
ID: 11693793
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
 
LVL 18

Expert Comment

by:Data-Man
ID: 11695565
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
 
LVL 27

Expert Comment

by:jjafferr
ID: 11697091
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
 
LVL 27

Expert Comment

by:jjafferr
ID: 11697672
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
 

Author Comment

by:rusco
ID: 11700692
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
 
LVL 27

Expert Comment

by:jjafferr
ID: 11701025
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
 

Author Comment

by:rusco
ID: 11704015
Hi jaffer,

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

thanks!
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 11704085
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 11704125
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
 
LVL 27

Expert Comment

by:jjafferr
ID: 11704348
Hi rusco

Were you able to download the file?

jaffer
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:rusco
ID: 11704730
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
 
LVL 27

Expert Comment

by:jjafferr
ID: 11704807
No problem
Nighty night

jaffer
0
 

Author Comment

by:rusco
ID: 11711941
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
 
LVL 18

Expert Comment

by:Data-Man
ID: 11711947
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
 
LVL 18

Accepted Solution

by:
Data-Man earned 125 total points
ID: 11711966
0
 

Author Comment

by:rusco
ID: 11712459
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
 
LVL 18

Expert Comment

by:Data-Man
ID: 11712524
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
 
LVL 27

Expert Comment

by:jjafferr
ID: 11712597
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
 

Expert Comment

by:LuigiC
ID: 13417940
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
 
LVL 18

Expert Comment

by:Data-Man
ID: 13419376
Lugi,
  Just don't include the 'ALLOWMULTISELECT' flag when opening the dialog.

Mike
0
 

Expert Comment

by:LuigiC
ID: 13421811
Thanks Mike

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

Cheers

Luigi
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 13421896
Glad you got it to work.

Don't forget to close out the question.

Regards,
Mike

0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now