Results 1 to 8 of 8

Exporting multiple queries to one single excel file

  1. #1
    sk88 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    153

    Exporting multiple queries to one single excel file

    Hi all,

    I am wondering if anyone can help me figure out what I should be doing in order for me to export multiple queries to one single excel file.

    Currently, I have this EXPORT button in my main form where when I click on it, it ask me to save the file/export to my computer. The exported file thus consists of only ONE queries but I wanted more queries to be exported in different worksheet under the same excel file.

    So far my codes are:

    Public Sub ExportXLS()

    #If Not CC_Debug Then
    On Error GoTo ErrProc
    #End If

    Const cQuery As String = "qryExportMetrics"


    Dim fc As FileChooser
    Dim strFileName As String

    Set fc = New FileChooser
    fc.DialogTitle = "Select file to save"
    fc.OpenTitle = "Save"
    fc.Filter = "Excel Files (*.xls)"
    strFileName = Nz(fc.SaveFile, "")
    Set fc = Nothing

    ' If user selected nothing or canceled, quit
    If Len(strFileName) = 0 Then
    Exit Sub
    ' If file already exists, delete it
    ElseIf Len(Dir(strFileName)) > 0 Then
    Kill strFileName
    End If

    DoCmd.TransferSpreadsheet _
    acExport, _
    acSpreadsheetTypeExcel9, _
    cQuery, _
    strFileName, _
    HasFieldNames:=True




    ExitProc:
    Exit Sub
    ErrProc:
    ErrMsg Err, Err.Description, Err.Source
    Resume ExitProc
    End Sub


    Any feedback?

    Thanks in advance

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    3,718
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "TableName", "TargetFile", , "worksheetname"

    This is the general format of exporting excel files

    you would have to substitute "worksheetname" with the name of the sheet you want to export to

    for instance if you wanted to export to a worksheet called goobers you'd have

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "TableName", "TargetFile", , "goobers$"

    the $ indicates a worksheet name rather than a specific range to export to. Note I haven't tested this but I did something similar earlier today for an import

  3. #3
    sk88 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    153
    Thanks rpeare...

    what if I want to export two sheet (i.e. two queries) Do write the codes like:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "TableName", "TargetFile", , "worksheetname" "worksheetname2"

    The codes which I posted earlier works fine for one sheet only.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    3,718
    are you trying to export two different queries to the same worksheet or trying to export two different queries to the same spreadsheet?

    If it's the former you would change the source query in the TABLENAME portion of the expression and the WORKSHEET name to a new worksheet name.

    If it's the latter you would likely have to define a range and if the queries are variable length it would be very difficult to do and I would question why you are not building one query that has all of the information you require and export that.

  5. #5
    sk88 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    153
    The reason I would like to have two queries in one single excel workbook but have them in two seperate worksheet are because they are two different data set that I would be like to have for future use.

    Merging two tables in to one queries and exporting them to one single worksheet would be my very last option.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    3,718
    You have completely muddled what I asked

    Are you doing this type of export:

    AccessQuery1-----> ExcelFile1 ------> Worksheet1
    AccessQuery2-----> ExcelFile1 ------> Worksheet2

    If you are what I suggested should work, you should just have to do what I said.

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "AccessQuery1", "ExcelFile1", , "Worksheet1$"
    for the first one (assuming these are your object names, you have to substitute in your actual object names and a path with the excel file not just the file name itself)
    one transfer for each query with a new worksheet name with a $ after it.

  7. #7
    sk88 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    153
    Sorry rpeare...

    this is what I just tried following your instruction.

    Private Sub cmdExport_Click()

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryExportMetrics", "ExcelFile1", , "Worksheet1$"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryCapacityBuilding", "ExcelFile1", , "Worksheet2$"


    End Sub

    I am sure I went wrong somewhere again... :S

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    3,718
    if that's the exact code you used it won't work, as I said you need to put in the path and file name of your target file

    so let's say your target file was Output.xls and it resided on your c:\test folder

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryExportMetrics", "c:\test\output.xls", , "Worksheet1$"

    This also assumes you have a tab called worksheet1 in that xls file to export to.

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

Similar Threads

  1. Exporting query to Excel file with password?
    By jvera524 in forum Access
    Replies: 0
    Last Post: 12-06-2010, 09:24 AM
  2. Exporting Queries to Excel Sheets, 1 Student per Workbook
    By StudentTeacher in forum Programming
    Replies: 8
    Last Post: 09-02-2010, 10:48 AM
  3. Exporting to Excel Date Stamped File
    By BED in forum Import/Export Data
    Replies: 1
    Last Post: 08-07-2010, 04:53 PM
  4. Exporting Access queries to Excel
    By dbDamo in forum Import/Export Data
    Replies: 2
    Last Post: 09-22-2009, 12:42 AM
  5. Excel exporting in single cells
    By Paolo29011982 in forum Import/Export Data
    Replies: 0
    Last Post: 07-29-2009, 03:48 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
  •  
Tech Forums: Microsoft Office Forums