Results 1 to 10 of 10
  1. #1
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    ecport query transferspreadsheet won't work

    I'm trying to use the transfer spreadsheet function to create a query in vba then export the results to excel



    I can get the query to appear in the query list. It opens from there too. But the transfer spreadsheet function gets stuck returning an error.

    Anyone any any ideas why. It's a union query.

    I get get an error saying the query can't be found. But I can see it in the query list!!

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Is it a timing issue? Does the TransferSpreadsheet work by itself?

  3. #3
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Thanks for prompt reply!! I've gone a different route now but does anyone know if using the same names query always only opens one tab in excel. I need to run it any number of times up to 10 so may need ten tabs.

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Yes the query will use the tab with the name of the query every time, unless you specify the name of the tab when exporting.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Don't know if this will help, but you might check out Ken Snell's site.
    This link demos how to write to many worksheets in one workbook: http://www.accessmvp.com/kdsnell/EXC...ExportSameFile

  6. #6
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Thanks. Not sure if I need it though. Each query would be based on a different group (gp1 to gp10.

    If I'm right process would be :

    Define sql based on checkbox1
    Createquerydef (gp1,sqlstring)
    Transferspreadsheet function.
    Delete query
    Loop to next checkbox


    I'll give it a go and get back soon

    Thanks for your help. Priceless!

  7. #7
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Follow up - ok so I pass each checkbox to a sub including the groupname

    gp is a string variable passed to the Sub. It looks like "7jk/mm1"

    Code:
    query_run = gp
    check_delete (gp)
        ' create .xlsx file if it doesn't already exist, and add the first worksheet
        Set qdfnew = cdb.CreateQueryDef(query_run, sqlstring)
        cdb.QueryDefs.Refresh
        'Set qdf = Nothing
        
         Set qdfnew = Nothing
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, query_run, xlsxPath, True
        
        Set qdfnew = Nothing
        DoCmd.DeleteObject acQuery, query_run
        
    
       
        Set cdb = Nothing
    I was hoping it would give me an excel file with lots of tabs (one for each group I passed) but it only leaves me with an excel file with the last group.

    Any ideas?

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You MUST change the name of the query/table to get a new worksheet.
    Executing the following code results in an Excel workbook with 2 worksheets:
    Code:
    Public Sub TestTranfr()
        Const strFileName As String = "TestExport"
        
        Dim strSQL As String
        Dim strTemp As String
        Dim xlsxPath As String
        
        xlsxPath = "F:\Forum\" & strFileName & ".xlsx"
    
        strTemp = "qrytesting"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strTemp, xlsxPath, True
    
        strTemp = "Query1"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strTemp, xlsxPath, True
    
    End Sub
    Of course, they are saved queries.
    The link I provided in Post #5 shows how to use a loop to create multiple queries to create multiple worksheets in one workbook.
    It does require modification to meet your needs though.....

    Without seeing your full code, this is the best I can do.

  9. #9
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Embarrasment. The variable gp caused a different query name. But higher up in code I was deleting the excel file. Hence I only saw last group!! Thx so much everyone. Massive help.

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    No problems.... I've had those days also.

    Ready to mark this solved??

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

Similar Threads

  1. Replies: 4
    Last Post: 11-10-2016, 08:11 AM
  2. Replies: 4
    Last Post: 12-06-2014, 08:49 PM
  3. Replies: 1
    Last Post: 05-02-2012, 11:40 AM
  4. Transferspreadsheet Help Please
    By graviz in forum Programming
    Replies: 0
    Last Post: 11-30-2009, 02:56 PM
  5. Can't get TransferSpreadsheet to work
    By Valeda in forum Import/Export Data
    Replies: 0
    Last Post: 07-19-2007, 08:04 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