Results 1 to 12 of 12
  1. #1
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283

    Exporting file to select location

    Hello,



    I know how to code it to where I can export the data from a table in an excel spreadsheet to a set location using DoCmd.TransferSpreadsheet. But I was wondering if anyone knew how to make it so where you can choose where you want to save the file instead of having to have a standard folder path in the code?

    Thanks

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    here is the link to method object: https://msdn.microsoft.com/en-us/lib...ffice.11).aspx
    note the info: Folder Picker dialog box - lets users select a path. The path that the user selects is captured in the FileDialogSelectedItems collection

    once you have the path - then you dynamically insert it into your transferspreadsheet syntax

  3. #3
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    I get a compile error: User-defined type not defined

  4. #4
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    my bad; showing my age too - old 2003 info; haven't updated myself...but need to find the more current syntax when I have a few minutes......

  5. #5
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    set a reference to the Microsoft Office 15.0 Object

  6. #6
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    I'm sorry but I don't understand can you update the code and share to me? I'm using access 2007

  7. #7
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    in 2007 you can ignore the prior advice of referencing Ms Office 15 Object. Be sure that 12 is referenced. This is found in VBA editing when you are adding this code - up in Tools, References - the referenced ones are shown on top....if not there then scroll down to Microsoft Office and find it and check it. the code for folder picker is:

    Dim fd As FileDialog


    Set fd = Application.FileDialog(msoFileDialogFolderPicker)


    Dim vrtSelectedItem As Variant


    With fd


    If .Show = -1 Then


    For Each vrtSelectedItem In .SelectedItems


    MsgBox "The path is: " & vrtSelectedItem


    Next vrtSelectedItem
    Else
    End If
    End With


    Set fd = Nothing

  8. #8
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    I have the 14.0 library and FileDialog isn't in there. But I don't want to select a file. I want to export data from a table and save it as an excel file to any location instead of having a set file path

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I use the "Browse For Folder" module from Chip Pearson's site at: http://www.cpearson.com/excel/browsefolder.aspx

    Download the zip and extract the file. It has the extension ".bas". I saved it to the desktop.
    In the IDE you can import the file (FILE/Import File). It will create and name the standard module for you.

    There are two functions that can be used to browse to folder. I use "BrowseFolderExplorer" because it displays more info. The other option is "BrowseFolder".

    The an example of the code I use to browse to a folder is:
    Code:
    Private Sub cmdExportData_Click()
        On Error GoTo Err_cmdExportData_Click
    
    
        Dim strSaveFileName As String
        Dim sPath As String
    
        Dim bStatusBar As Variant
    
        'msgbox variables
        Dim Msg, Style, Title, Response
    
        sPath = CurrentProject.Path & "\"
    
        sPath = BrowseFolderExplorer("Select a Folder", msoFileDialogViewPreview, sPath)
    
        strSaveFileName = ""
        Me.Repaint
    
        If Len(Trim(sPath)) > 0 Then
    
           strSaveFileName = sPath & "\Statement Monthly Summary " & Me.cboMonth & " " & Me.cboYear & " Hours.xls"
           DoCmd.OutputTo acOutputQuery, "EQ_StatementMonthlySummary", acFormatXLS, strSaveFileName, False
    
        End If
    'rest of the code
    Works well..

  10. #10
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    it worked fine in my 2007 ... FileDialog is not part of the reference... Just the Microsoft Office Library itself.....be sure it is referenced.... and be sure to scroll down to Microsoft Office in alphabetical order to check the unreferenced as several have very similar names....

  11. #11
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    if I'm understanding correctly you are using DoCmd.TransferSpreadsheet with the file name and path. I use this occasionally and usually just leave the file path out of the method which forces it to ask the user for the path they want to save to.

  12. #12
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    I figured it out. thanks guys!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA to transfer a file from one location to another
    By Demerit in forum Programming
    Replies: 2
    Last Post: 09-21-2014, 02:58 PM
  2. Exporting Report to PDF specific location on quit - over-writing existing
    By augcorv@gmail.com in forum Import/Export Data
    Replies: 13
    Last Post: 03-21-2014, 02:08 PM
  3. change file location
    By Mbakker71 in forum Import/Export Data
    Replies: 4
    Last Post: 01-29-2014, 08:17 AM
  4. Replies: 10
    Last Post: 12-07-2012, 01:57 PM
  5. File Location
    By Mitch87 in forum Programming
    Replies: 1
    Last Post: 02-19-2010, 11:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums