[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

how to select my own destination path when using Macro TransferSpreadsheet

Dear Experts,

Now I am using Macro "TransferSpreadsheet" to export the file, but the problem is I have no idea how to be able to select my own destination path, not a fixed path. Could you please help me?

Thanks a lot.

Best regards,
Stone

0
white_stone
Asked:
white_stone
  • 3
  • 3
1 Solution
 
thenelsonCommented:
This is a macro not VBA- yes? In the fide name textbox put the path and file name to the file you want to transfer to.
0
 
white_stoneAuthor Commented:
Thanks for your reply, thenelson.
But the thing is I would like to be able to browse the destination path.
Can your help?

Many thanks.
Stone.
0
 
thenelsonCommented:
You can't do it from a macro. Here is how you would do it from VBA.

You can call the window's file dialog from Access.  The Windows's file dialog is the window that almost all programs use to open or save a file. To see the file dialog, goto File, Open in Access.
Calling the window's file dialog from Access requires a reference set to Microsoft Office x.xx Object Library (Visual Basic Editor, Tools, References).

Simple File Dialog call:

Dim strPathFileName As String

'In the "With Application.FileDialog" line, you can use:
'   msoFileDialogFilePicker, msoFileDialogFolderPicker, msoFileDialogOpen or msoFileDialogSaveAs
With Application.FileDialog(msoFileDialogOpen)
    .Title = "Select file"
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "Access Files", "*.mdb", 1
    .Filters.Add "All", "*.*", 2
    .InitialFileName = "C:\MyFile.mdb"
    If .Show Then
        strPathFileName = .SelectedItems(1)
    Else
        MsgBox "No file selected!"
    End If
End With


More complicated File Dialog call:

Dim fd As FileDialog

'In the "Application.FileDialog" line, you can use:
'   msoFileDialogFilePicker, msoFileDialogFolderPicker, msoFileDialogOpen or msoFileDialogSaveAs
'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)

'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.
    If .Show = -1 Then   'The user pressed the action button.

        '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.
            MsgBox "The path is: " & vrtSelectedItem

        Next vrtSelectedItem
    Else   'The user pressed Cancel.
        MsgBox "No file selected!"
    End If
End With
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
white_stoneAuthor Commented:
Thanks alot. I think I can adjust your code to what I want.
0
 
white_stoneAuthor Commented:
Thanks alot. I think I can adjust your code as what I want.
0
 
thenelsonCommented:
You're welcome.  Glad to help and thank you very much for the points with "A" grade!

Happy computing!

Nelson
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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