Solved

Export files to a folder the user navigates to

Posted on 2014-12-01
5
102 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
ID: 40474246
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
ID: 40474450
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
ID: 40474498
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
ID: 40475444
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
ID: 40476083
Thank you to both of you.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

920 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

14 Experts available now in Live!

Get 1:1 Help Now