asked on

Export files to a folder the user navigates to

I have the following code in an onclick event of a command button on a form. But instead of having the file dumped on the user's desktop I would like a navigation window to open so the user can select the location they want the files to go to. How would I change the code?

Private Sub cmdExportXML_Click()

    DoCmd.RunCommand acCmdSaveRecord

Dim strXLFile As String
Dim strXLFile2 As String
Dim strXLFile3 As String
Dim strXLFile4 As String
Dim strDesktopPath As String
Dim strDesktopPath2 As String
Dim strDesktopPath3 As String
Dim strDesktopPath4 As String

    strDesktopPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    strDesktopPath2 = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    strDesktopPath3 = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    strDesktopPath4 = CreateObject("WScript.Shell").SpecialFolders("Desktop")

    strXLFile = strDesktopPath & "\Checklist - " & Forms!frmIHMGnotes.Text328 & " To Office " & Format(Me!txtConvertedDateOfVisit, "mmddyyyy") & ".xml"
    strXLFile2 = strDesktopPath2 & "\Checklist - " & Forms!frmIHMGnotes.Text328 & " To Office " & Format(Me!txtConvertedDateOfVisit, "mmddyyyy") & ".xsd"
    strXLFile3 = strDesktopPath3 & "\Running Notes - " & Forms!frmIHMGnotes.Text328 & " To Office " & Format(Me!txtConvertedDateOfVisit, "mmddyyyy") & ".xml"
    strXLFile4 = strDesktopPath4 & "\Running Notes - " & Forms!frmIHMGnotes.Text328 & " To Office " & Format(Me!txtConvertedDateOfVisit, "mmddyyyy") & ".xsd"

    If MsgBox("This function will export the current checklist and running notes data records to your desktop.  Do you wish to continue?", vbYesNo) = vbYes Then
        Application.ExportXML acExportTable, "LOCALtblTEMPIHMGNotes", strXLFile, strXLFile2
        Application.ExportXML acExportTable, "tblTEMPMemberRunningNotes", strXLFile3, strXLFile4
        MsgBox "Please check your desktop.  The files should appear there."
    
    Me.cmdExportXML.Enabled = False
    
        Else  ' answer is NO
        Exit Sub
    End If

End Sub

Open in new window

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SteveL13

ASKER

Pat,

Can I assume then that I copy/paste this into a module:

Public Function fChooseDirectory()

    'Declare a variable as a FileDialog object.
    'Dim fd As FileDialog

   '''' Const msoFileDialogFolderPicker = 4 'use for late binding
    
    Dim fd As Object
    
    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)

    'Declare a variable to contain the path
    'of each selected item. Even though the path is a String,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant

    'Use a With...End With block to reference the FileDialog object.
    With fd

        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the action button.
        If .Show = -1 Then

            'Step through each string in the FileDialogSelectedItems collection.
            For Each vrtSelectedItem In .SelectedItems

                'vrtSelectedItem is a String that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example simply displays the path in a message box.

                'Only one item will be returned since the file dialog is a folder picker
                'MsgBox "The path is: " & vrtSelectedItem
                fChooseDirectory = vrtSelectedItem
                Exit Function
            Next vrtSelectedItem
        'The user pressed Cancel.
        Else
        End If
    End With

    'Set the object variable to Nothing.
    Set fd = Nothing
    fChooseDirectory = "Error - nothing chosen"
End Function

Open in new window

And then replace:

strDesktopPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")

with

Me.txtPath = fChooseDirectory()

Or am I confused?

PatHartman

Should work. Give it a try. Just make sure your form has a control named txtPath. It doesn't have to be visible if you don't want it to be. I tend to leave it visible since that lets the user simply type into the box rather than navigate. I also use defaults for many locations so I prepopulate the box then let the user override the default.

SOLUTION

Phillip Burton