Solved

Export files to a folder the user navigates to

Posted on 2014-12-01
5
100 Views
Last Modified: 2014-12-02
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
Comment
Question by:SteveL13
  • 2
  • 2
5 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
Comment Utility
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
 

Author Comment

by:SteveL13
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 250 total points
Comment Utility
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
 

Author Closing Comment

by:SteveL13
Comment Utility
Thank you to both of you.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now