Results 1 to 5 of 5
  1. #1
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169

    TransferSpreadsheet question

    I am using the following code that copies a query into an Excel spreadsheet.
    Code:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "myQuery", "C:\Documents and Settings\jpkeller\jk.xls", True
    How do I tweak the code so that the standard Windows File Open/Save dialog box opens so the user can save in another file location?



    Thanks for any help.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Found this http://msdn.microsoft.com/en-us/library/Aa219843 Got this to work:
    Code:
    Dim fd As FileDialog
    Dim si As Variant
    Dim path As String
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    If fd.Show = True Then
        For Each si In fd.SelectedItems
            path = si
        Next
    Else
        'nothing, user canceled
    End If
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Table1", path & "\test.xls", True
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169
    Hi June, I tried the code but got a compile error message stating: User-defined type not defined and the code Dim fd As FileDialog was highlighted. I am using 2003 so maybe that is not recognized?

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    There's like a reference you're missing (june will tell you which one but I suspect it's an activex reference) In your VB window if you click on TOOLS>References there's a list of items. If you don't have the correct reference chosen the code won't work.

    EDIT: Microsoft Office 11.0 Object Library Look for this reference in your list, if you don't have it pick the one that's appropriate to your version of Access

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Yes, I expect it is the Office library reference you need to activate. Since I already had it selected in my project didn't get that error.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Transferspreadsheet Method
    By allenjasonbrown@gmail.com in forum Programming
    Replies: 11
    Last Post: 06-30-2011, 11:40 AM
  2. Transferspreadsheet Help Please
    By graviz in forum Programming
    Replies: 0
    Last Post: 11-30-2009, 02:56 PM
  3. Can't get TransferSpreadsheet to work
    By Valeda in forum Import/Export Data
    Replies: 0
    Last Post: 07-19-2007, 08:04 AM
  4. transferSpreadsheet
    By rabbit in forum Import/Export Data
    Replies: 1
    Last Post: 06-09-2006, 04:01 PM
  5. transferspreadsheet function
    By jeffj in forum Import/Export Data
    Replies: 3
    Last Post: 03-13-2006, 11:59 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