By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,171 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Windows File Open / Save Common Dialogues

P: 44
I use this "Access Cookbook" from Ken Getz that comes with some pretty nice sample code and applications.

I use some file open features they have but I don't know enough about what they've created to use just the parts I want.

I'm trying to use the Windows File Open type dialogue to identify a directory and then return that directory as a string to a function that will ultimately process all the excel spreadsheets in that file and select specific data to append to a table in my Access DB.

I seem to be going down one of 2 paths... either using the FileDialog object or the Windows API drectly. I just don't know what I need to do to get the directory returned to me.

Thanks in advance for any help!
Mar 20 '08 #1
Share this Question
Share on Google+
13 Replies


Scott Price
Expert 100+
P: 1,384
I assume that you are using the File Dialog in order to have a user identify the directory where their particular files reside?

Could you post the File Dialog code you are using now?

Basically you'll end up setting a Variant datatype variable to hold the .SelectedItems returned from the user selection. You can then test this variable as you would any string variable.

Regards,
Scott
Mar 20 '08 #2

P: 44
I assume that you are using the File Dialog in order to have a user identify the directory where their particular files reside?

Could you post the File Dialog code you are using now?

Basically you'll end up setting a Variant datatype variable to hold the .SelectedItems returned from the user selection. You can then test this variable as you would any string variable.

Regards,
Scott
Thanks Scott! Here's the code.

The form has this on click sub that calls a function which in turn calls another function below

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFileOpen_Click()
  2.     Dim varResult As Variant
  3.     varResult = FileOpenSave(True)
  4.  
  5.     Me.txtFileOpen = Replace(varResult, vbNullChar, " ")
  6. End Sub
  7.  
  8. Private Function FileOpenSave(fOpen As Integer) As Variant
  9.     Dim ofn As tagOPENFILENAME
  10.     Dim lngFlags As Long
  11.     Dim strFilter As String
  12.     Dim lngResult As Long
  13.  
  14.     strFilter = acbAddFilterItem(strFilter, "Access Files (*.mda, *.mdb)", "*.MDA;*.MDB")
  15.     strFilter = acbAddFilterItem(strFilter, "dBASE Files (*.dbf)", "*.DBF")
  16.     strFilter = acbAddFilterItem(strFilter, "Text Files (*.txt)", "*.TXT")
  17.     strFilter = acbAddFilterItem(strFilter, "All Files (*.*)", "*.*")
  18.     lngFlags = GetFlags()
  19.     FileOpenSave = acbCommonFileOpenSave(Filter:=strFilter, _
  20.      Flags:=lngFlags, OpenFile:=fOpen, _
  21.      FileName:=IIf(fOpen, Me.txtFileOpen & "", Me.txtFileSave & ""), _
  22.      DialogTitle:=IIf(fOpen, "Test File Open", "Test File Save"))
  23.      Debug.Print CStr(FileOpenSave)
  24.  
  25. End Function
And this function is the one that seems to do the bulk of the work:
Expand|Select|Wrap|Line Numbers
  1. Public Function acbCommonFileOpenSave( _
  2.  Optional ByRef Flags As Long = 0, _
  3.  Optional ByVal InitialDir As Variant, _
  4.  Optional ByVal Filter As String = vbNullString, _
  5.  Optional ByVal FilterIndex As Long = 1, _
  6.  Optional ByVal DefaultExt As String = vbNullString, _
  7.  Optional ByVal FileName As String = vbNullString, _
  8.  Optional ByVal DialogTitle As String = vbNullString, _
  9.  Optional ByVal hwnd As Variant, _
  10.  Optional ByVal OpenFile As Boolean = True) As Variant
  11.     ' This is the entry point you'll use to call the common
  12.     ' file open/save dialog. The parameters are listed
  13.     ' below, and all are optional.
  14.     '
  15.     ' In:
  16.     ' Flags: one or more of the acbOFN_* constants, OR'd together.
  17.     ' InitialDir: the directory in which to first look
  18.     ' Filter: a set of file filters, set up by calling
  19.     ' AddFilterItem. See examples.
  20.     ' FilterIndex: 1-based integer indicating which filter
  21.     ' set to use, by default (1 if unspecified)
  22.     ' DefaultExt: Extension to use if the user doesn't enter one.
  23.     ' Only useful on file saves.
  24.     ' FileName: Default value for the file name text box.
  25.     ' DialogTitle: Title for the dialog.
  26.     ' hWnd: parent window handle
  27.     ' OpenFile: Boolean(True=Open File/False=Save As)
  28.     ' Out:
  29.     ' Return Value: Either Null or the selected filename
  30.     Dim ofn As tagOPENFILENAME
  31.     Dim strFileName As String
  32.     Dim strFileTitle As String
  33.     Dim fResult As Boolean
  34.     Dim strResult As String
  35.  
  36.     If IsMissing(InitialDir) Then InitialDir = CurDir
  37.     If IsMissing(hwnd) Then hwnd = Application.hWndAccessApp
  38.  
  39.     ' Allocate string space for the returned strings.
  40.     strFileName = Left(FileName & String(256, 0), 256)
  41.     strFileTitle = String(256, 0)
  42.  
  43.     ' Set up the data structure before you call the function
  44.     With ofn
  45.         .lStructSize = Len(ofn)
  46.         .hwndOwner = hwnd
  47.         .strFilter = Filter
  48.         .nFilterIndex = FilterIndex
  49.         .strFile = strFileName
  50.         .nMaxFile = Len(strFileName)
  51.         .strFileTitle = strFileTitle
  52.         .nMaxFileTitle = Len(strFileTitle)
  53.         .strTitle = DialogTitle
  54.         .Flags = Flags
  55.         .strDefExt = DefaultExt
  56.         .strInitialDir = InitialDir
  57.         ' Didn't think most people would want to deal with
  58.         ' these options.
  59.         .hInstance = 0
  60.         .lpfnHook = 0
  61.         .strCustomFilter = vbNullString
  62.         .nMaxCustFilter = 0
  63.     End With
  64.  
  65.     ' This will pass the desired data structure to the
  66.     ' Windows API, which will in turn it uses to display
  67.     ' the Open/Save As Dialog.
  68.     If OpenFile Then
  69.         fResult = GetOpenFileName(ofn)
  70.     Else
  71.         fResult = GetSaveFileName(ofn)
  72.     End If
  73.  
  74.     ' The function call filled in the strFileTitle member
  75.     ' of the structure. You'll have to write special code
  76.     ' to retrieve that if you're interested.
  77.     If fResult Then
  78.         ' You might care to check the Flags member of the
  79.         ' structure to get information about the chosen file.
  80.         ' In this example, if you bothered to pass in a
  81.         ' value for Flags, we'll fill it in with the outgoing
  82.         ' Flags value.
  83.         If Not IsMissing(Flags) Then
  84.             Flags = ofn.Flags
  85.         End If
  86.  
  87.         strResult = ofn.strFile
  88.         If ofn.Flags And acbOFN_ALLOWMULTISELECT Then
  89.             ' They might have selected multiple files.
  90.             Dim lngPos As Long
  91.             Dim astrFiles As Variant
  92.  
  93.             ' Look for the two null characters at the end of the data.
  94.             ' Trim the string at that point.
  95.             lngPos = InStr(strResult, vbNullChar & vbNullChar)
  96.             If lngPos > 0 Then
  97.                 strResult = Left(strResult, lngPos - 1)
  98.             Else
  99.                 strResult = TrimNull(strResult)
  100.             End If
  101.         Else
  102.             strResult = TrimNull(strResult)
  103.         End If
  104.         acbCommonFileOpenSave = strResult
  105.     Else
  106.         acbCommonFileOpenSave = vbNullString
  107.     End If
  108. End Function
Mar 20 '08 #3

Scott Price
Expert 100+
P: 1,384
First of all, as always, but especially when posting that amount of code please use the [code] tags provided! No-one here will take the time to wade through over 130 lines of code without at least the added help the code tags give. They are simple to use: Simply select the code text, then click the # icon on the top of this window. You can then manually edit the first tag to look like this for VBA: [code=vb]. That's all there is to it! Thanks.

Secondly, your code returns a variable already containing the chosen file path. This is called "strResult" in the last function that you posted. You will simply need to split the string variable looking for the last '\' mark to define where the path ends and the file name begins.

I'm on my way out the door but will get back to you with instructions on how to find the path portion of the strResult variable.

Regards,
Scott
Mar 20 '08 #4

ADezii
Expert 5K+
P: 8,597
I use this "Access Cookbook" from Ken Getz that comes with some pretty nice sample code and applications.

I use some file open features they have but I don't know enough about what they've created to use just the parts I want.

I'm trying to use the Windows File Open type dialogue to identify a directory and then return that directory as a string to a function that will ultimately process all the excel spreadsheets in that file and select specific data to append to a table in my Access DB.

I seem to be going down one of 2 paths... either using the FileDialog object or the Windows API drectly. I just don't know what I need to do to get the directory returned to me.

Thanks in advance for any help!
With a Project this simple, why use the API and all the unneccessary code? The Microsoft Office File Dialog will be more than adequate for your needs, and it should eliminate 90+ percent of the code.
Mar 20 '08 #5

Scott Price
Expert 100+
P: 1,384
I completely agree with ADezii! The code you have really isn't necessary for what you have told us your needs are.

However, should you desire to continue as you are going, you will need a function like the following to split your string variable to find the code:
Expand|Select|Wrap|Line Numbers
  1. Public Function SplitFilePath(FilePath As String) As String
  2.  
  3. Dim slPos As Integer
  4. Dim flPath As String
  5.  
  6. slPos = InStrRev(FilePath, "\")
  7. flPath = Left(FilePath, slPos)
  8. SplitFilePath = flPath
  9.  
  10. End Function
This can go into a standard code module (named something different than the Function Name!) and can be called from anywhere in your code, or even from a query.

Results:

Expand|Select|Wrap|Line Numbers
  1. ?SplitFilePath("C:\My Documents\Scott\Access Code\address.mdb")
  2. C:\My Documents\Scott\Access Code\
Regards,
Scott
Mar 21 '08 #6

P: 44
Thanks for the help. I'll make sure to use the "Tags" in the future when adding any code.

also, I'll check on the other Microsoft Office File Open option. I'm not familiar with it.
Mar 21 '08 #7

Scott Price
Expert 100+
P: 1,384
Here is an example of using the msoFileDialogFolderPicker:

To use this you will need to set a reference to the Microsoft Office 11.0 Object Library. In the VBA editor window go to Tools>References. There should be a similar reference already set to Microsoft Access 11.0 Object Library, scroll down the list until you see the Office 11.0 reference and select it. (Note the 11.0 version is for Office/Access 2003)

This will take the chosen folder and place it into a text box named Text1 on the form from which it is called.

Be aware that when the user navigates to a particular folder, they will not be able to see any files! This can be a bit disconcerting for the user if they are expecting the more common FilePicker control. You can change the code below to use the FilePicker instead by changing line 10 to read: Set fd = Application.FileDialog(msoFileDialogFilePicker)

In this case you will then need to extract the folder path from the user choice by using the function I posted earlier, or a similar one. I can give you instructions in how to do this if you like.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Command0_Click()
  5.  
  6. Dim fd As FileDialog
  7. Dim varItems As Variant
  8. Dim flPath As String
  9.  
  10. Set fd = Application.FileDialog(msoFileDialogFolderPicker)
  11.  
  12. With fd
  13.     '.AllowMultiSelect = True
  14.     .Filters.Clear
  15.  
  16.     If .Show = True Then
  17.         For Each varItems In .SelectedItems
  18.             flPath = varItems
  19.         Next
  20.     Else
  21.         MsgBox "User cancelled action"
  22.     End If
  23. End With
  24.  
  25. Set fd = Nothing
  26. Me.Text1 = flPath
  27.  
  28. End Sub
Regards,
Scott
Mar 21 '08 #8

P: 44
Again... THANKS! Everything you've posted has been very educational and valuable to me.

I hope you have a great weekend!
Mar 21 '08 #9

P: 44
I used the code scott gave me, and then I pass that directory to a function calling the "Findfile" Office Function to generate a list of files for the user to select. Then, I can process all the files they select using a previously created function that was working on one file at a time.

thanks! This has really helped me out.
Mar 21 '08 #10

Scott Price
Expert 100+
P: 1,384
Glad to know it's working for you! Thanks for posting back with what you've got.

Regards,
Scott
Mar 21 '08 #11

P: 44
Hi,

I'm simply trying to add a filter

Below Scotts .Filters.Clear, I added this line:

.Filters.Add "Excel Workbooks", "*.xls"

I receive a not supported message after trying several variations.

Thanks!
Mar 26 '08 #12

Scott Price
Expert 100+
P: 1,384
Are you using the msoFileDialogFolderPicker or the msoFileDialogFilePicker?

The folder picker does not support file filters, since it is for picking folders, and doesn't even show files.

It sounds like your wants/needs have changed since the first post... Could you restate the problem you are facing now, and post what code you are using?

Regards,
Scott
Mar 26 '08 #13

P: 44
HI Scott,

Yes, my apologies. I've moved onto a second function where its best to process 1 file at a time. I am using the 'file picker' in the sub below (hopefully I used the 'tag' correctly!)

Thanks!

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdChooseDirectory_Click()
  2. Dim fd As FileDialog
  3. Dim varItems As Variant
  4. Dim fl_File As String
  5.  
  6. rc = Clear_Tabs_Table()
  7. Me.lst_Worksheets.Requery
  8. Me.Process_Sheets.Requery
  9.  
  10.  
  11. 'FindFile
  12. 'Set fd = Application.FileDialog(msoFileDialogFolderPicker)
  13. Set fd = Application.FileDialog(msoFileDialogFilePicker)
  14.  
  15. With fd
  16.     '.AllowMultiSelect = True
  17.     'Set the title of the dialog box.
  18.       .Title = "Please select a Single Workspace Specification to Import"
  19.  
  20.       'Clear out the current filters, and add our own.
  21.       .Filters.Clear
  22.  
  23.     'Set a Default Folder
  24.     If Me.txtDefaultFolder > " " Then
  25.         intitialdir = Me.txtDefaultFolder
  26.     End If
  27.  
  28.  
  29.     If .Show = True Then
  30.         For Each varItems In .SelectedItems
  31.             fl_File = varItems
  32.         Next
  33.     Else
  34.         MsgBox "User cancelled action"
  35.         GoTo cmdChooseDirectory_Click_Exit
  36.     End If
  37. End With
  38.  
  39. Set fd = Nothing
  40. Me.txtDirectory = fl_File
  41. List_Tabs (fl_File)
  42.  
  43. Me.lst_Worksheets.Requery
  44.  
  45. cmdChooseDirectory_Click_Exit:
  46. Exit Sub
  47.  
  48. cmdChooseDirectory_Click_Err:
  49. MsgBox Err.Description
  50. Resume cmdChooseDirectory_Click_Exit:
  51. End Sub
Mar 27 '08 #14

Post your reply

Sign in to post your reply or Sign up for a free account.