Avatar of SteveL13
SteveL13
Flag for United States of America 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

Microsoft Access

Avatar of undefined
Last Comment
SteveL13

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
PatHartman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SteveL13

ASKER
Thank you to both of you.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck