Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 113
  • Last Modified:

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

0
SteveL13
Asked:
SteveL13
  • 2
  • 2
2 Solutions
 
PatHartmanCommented:
The following is code that I use.  cmdBrowse_Click() is the click event of a button on the form.  It opens the common dialog folder and places the selected folder in a control named txtPath.  Then the code that needs the path gets it from the control.

Private Sub cmdBrowse_Click()
    Me.txtPath = fChooseDirectory()
End Sub

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

0
 
SteveL13Author Commented:
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?
0
 
PatHartmanCommented:
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.
0
 
Phillip BurtonCommented:
If you add a Reference (Tools - References) to Microsoft Office 15.0 Object Library (the number might be different in your computer), you can use this, which you can insert before line 14, and substitute strGetFolderName with (say) strDesktopPath:

With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = "C:\"
    If .Show <> -1 Then
        Exit Sub 'No folder selected
    End If
    strGetFolderName = .SelectedItems(1) 'This is your folder.
End With

Open in new window

0
 
SteveL13Author Commented:
Thank you to both of you.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now