Windows File Open / Save Common Dialogues

[NJonge01]

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!

[Scott Price]

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

[NJonge01]

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

[CODE=vb]Private Sub cmdFileOpen_Cli ck()
Dim varResult As Variant
varResult = FileOpenSave(Tr ue)

Me.txtFileOpen = Replace(varResu lt, vbNullChar, " ")
End Sub

Private Function FileOpenSave(fO pen As Integer) As Variant
Dim ofn As tagOPENFILENAME
Dim lngFlags As Long
Dim strFilter As String
Dim lngResult As Long

strFilter = acbAddFilterIte m(strFilter, "Access Files (*.mda, *.mdb)", "*.MDA;*.MD B")
strFilter = acbAddFilterIte m(strFilter, "dBASE Files (*.dbf)", "*.DBF")
strFilter = acbAddFilterIte m(strFilter, "Text Files (*.txt)", "*.TXT")
strFilter = acbAddFilterIte m(strFilter, "All Files (*.*)", "*.*")
lngFlags = GetFlags()
FileOpenSave = acbCommonFileOp enSave(Filter:= strFilter, _
Flags:=lngFlags , OpenFile:=fOpen , _
FileName:=IIf(f Open, Me.txtFileOpen & "", Me.txtFileSave & ""), _
DialogTitle:=II f(fOpen, "Test File Open", "Test File Save"))
Debug.Print CStr(FileOpenSa ve)

End Function[/CODE]

And this function is the one that seems to do the bulk of the work:
[CODE=vb]Public Function acbCommonFileOp enSave( _
Optional ByRef Flags As Long = 0, _
Optional ByVal InitialDir As Variant, _
Optional ByVal Filter As String = vbNullString, _
Optional ByVal FilterIndex As Long = 1, _
Optional ByVal DefaultExt As String = vbNullString, _
Optional ByVal FileName As String = vbNullString, _
Optional ByVal DialogTitle As String = vbNullString, _
Optional ByVal hwnd As Variant, _
Optional ByVal OpenFile As Boolean = True) As Variant
' This is the entry point you'll use to call the common
' file open/save dialog. The parameters are listed
' below, and all are optional.
'
' In:
' Flags: one or more of the acbOFN_* constants, OR'd together.
' InitialDir: the directory in which to first look
' Filter: a set of file filters, set up by calling
' AddFilterItem. See examples.
' FilterIndex: 1-based integer indicating which filter
' set to use, by default (1 if unspecified)
' DefaultExt: Extension to use if the user doesn't enter one.
' Only useful on file saves.
' FileName: Default value for the file name text box.
' DialogTitle: Title for the dialog.
' hWnd: parent window handle
' OpenFile: Boolean(True=Op en File/False=Save As)
' Out:
' Return Value: Either Null or the selected filename
Dim ofn As tagOPENFILENAME
Dim strFileName As String
Dim strFileTitle As String
Dim fResult As Boolean
Dim strResult As String

If IsMissing(Initi alDir) Then InitialDir = CurDir
If IsMissing(hwnd) Then hwnd = Application.hWn dAccessApp

' Allocate string space for the returned strings.
strFileName = Left(FileName & String(256, 0), 256)
strFileTitle = String(256, 0)

' Set up the data structure before you call the function
With ofn
.lStructSize = Len(ofn)
.hwndOwner = hwnd
.strFilter = Filter
.nFilterIndex = FilterIndex
.strFile = strFileName
.nMaxFile = Len(strFileName )
.strFileTitle = strFileTitle
.nMaxFileTitle = Len(strFileTitl e)
.strTitle = DialogTitle
.Flags = Flags
.strDefExt = DefaultExt
.strInitialDir = InitialDir
' Didn't think most people would want to deal with
' these options.
.hInstance = 0
.lpfnHook = 0
.strCustomFilte r = vbNullString
.nMaxCustFilter = 0
End With

' This will pass the desired data structure to the
' Windows API, which will in turn it uses to display
' the Open/Save As Dialog.
If OpenFile Then
fResult = GetOpenFileName (ofn)
Else
fResult = GetSaveFileName (ofn)
End If

' The function call filled in the strFileTitle member
' of the structure. You'll have to write special code
' to retrieve that if you're interested.
If fResult Then
' You might care to check the Flags member of the
' structure to get information about the chosen file.
' In this example, if you bothered to pass in a
' value for Flags, we'll fill it in with the outgoing
' Flags value.
If Not IsMissing(Flags ) Then
Flags = ofn.Flags
End If

strResult = ofn.strFile
If ofn.Flags And acbOFN_ALLOWMUL TISELECT Then
' They might have selected multiple files.
Dim lngPos As Long
Dim astrFiles As Variant

' Look for the two null characters at the end of the data.
' Trim the string at that point.
lngPos = InStr(strResult , vbNullChar & vbNullChar)
If lngPos > 0 Then
strResult = Left(strResult, lngPos - 1)
Else
strResult = TrimNull(strRes ult)
End If
Else
strResult = TrimNull(strRes ult)
End If
acbCommonFileOp enSave = strResult
Else
acbCommonFileOp enSave = vbNullString
End If
End Function[/CODE]

[Scott Price]

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

[ADezii]

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.

[Scott Price]

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:
[CODE=vb]
Public Function SplitFilePath(F ilePath As String) As String

Dim slPos As Integer
Dim flPath As String

slPos = InStrRev(FilePa th, "\")
flPath = Left(FilePath, slPos)
SplitFilePath = flPath

End Function[/CODE]

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:

?SplitFilePath("C:\My Documents\Scott\Access Code\address.mdb")
C:\My Documents\Scott\Access Code\

Regards,
Scott

[NJonge01]

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.

[Scott Price]

Here is an example of using the msoFileDialogFo lderPicker:

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>Reference s. 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.Fil eDialog(msoFile DialogFilePicke r)

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.

[CODE=vb]Option Compare Database
Option Explicit

Private Sub Command0_Click( )

Dim fd As FileDialog
Dim varItems As Variant
Dim flPath As String

Set fd = Application.Fil eDialog(msoFile DialogFolderPic ker)

With fd
'.AllowMultiSel ect = True
.Filters.Clear

If .Show = True Then
For Each varItems In .SelectedItems
flPath = varItems
Next
Else
MsgBox "User cancelled action"
End If
End With

Set fd = Nothing
Me.Text1 = flPath

End Sub[/CODE]

Regards,
Scott

[NJonge01]

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

I hope you have a great weekend!

[NJonge01]

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.

[Scott Price]

Glad to know it's working for you! Thanks for posting back with what you've got.

Regards,
Scott

[NJonge01]

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!

[Scott Price]

Are you using the msoFileDialogFo lderPicker or the msoFileDialogFi lePicker?

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

[NJonge01]

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!

[CODE=vb]Private Sub cmdChooseDirect ory_Click()
Dim fd As FileDialog
Dim varItems As Variant
Dim fl_File As String

rc = Clear_Tabs_Tabl e()
Me.lst_Workshee ts.Requery
Me.Process_Shee ts.Requery


'FindFile
'Set fd = Application.Fil eDialog(msoFile DialogFolderPic ker)
Set fd = Application.Fil eDialog(msoFile DialogFilePicke r)

With fd
'.AllowMultiSel ect = True
'Set the title of the dialog box.
.Title = "Please select a Single Workspace Specification to Import"

'Clear out the current filters, and add our own.
.Filters.Clear

'Set a Default Folder
If Me.txtDefaultFo lder > " " Then
intitialdir = Me.txtDefaultFo lder
End If


If .Show = True Then
For Each varItems In .SelectedItems
fl_File = varItems
Next
Else
MsgBox "User cancelled action"
GoTo cmdChooseDirect ory_Click_Exit
End If
End With

Set fd = Nothing
Me.txtDirectory = fl_File
List_Tabs (fl_File)

Me.lst_Workshee ts.Requery

cmdChooseDirect ory_Click_Exit:
Exit Sub

cmdChooseDirect ory_Click_Err:
MsgBox Err.Description
Resume cmdChooseDirect ory_Click_Exit:
End Sub[/CODE]