Exporting Queries with Parameters to a single Excel Workbook but Seperate Worksheets

[blac2398]

I have written a bit of VBA to run through a query which has a variable parameter of Country. Each time the query runs I can get this to export to individual Excel workbooks no problem but I want to have just a single workbook and each set of query results on a seperate Worksheet with the tab reading the parameter name.

I.e. I run the query for results in Canada I want this to export to a worksheet with the tab readining Canada and so on.

Does anyone know how I can achieve this from the VBA in Access?

Much Approeciated. Thanks

 

[StephenB]

Hello all. Resurrecting as I have a similar need.
Thanks for any insight.

BTW, Here's what I'm using.

DoCmd.OutputTo acQuery, "QryCIUSRs_21_day_forecast_summary_Crosstab", "xls", "\\nas2share3\Share3\Customer Services\CIU\Common\Business Analysis\UCSW Inventory History\2009\CSC 21 Day SR Forecast " & strdate & ".xls", False, "", 0

I have a field named "Team" and would like all records with a specific team on a different tab/sheet, and the name of the tab/sheet to be the value of in the team field.

Ex. All records with team Apple on tab "Apple", all records with team Blue on tab "Blue", etc...

 

[jdraw]

Hello all. Resurrecting as I have a similar need.
Thanks for any insight.

BTW, Here's what I'm using.

DoCmd.OutputTo acQuery, "QryCIUSRs_21_day_forecast_summary_Crosstab", "xls", "\\nas2share3\Share3\Customer Services\CIU\Common\Business Analysis\UCSW Inventory History\2009\CSC 21 Day SR Forecast " & strdate & ".xls", False, "", 0

I have a field named "Team" and would like all records with a specific team on a different tab/sheet, and the name of the tab/sheet to be the value of in the team field.

Ex. All records with team Apple on tab "Apple", all records with team Blue on tab "Blue", etc...

I think this code has the essence of what you are trying to do. It goes thru all tables in a particular database, and dumps the contents to separate worksheets in a single workbook. Each worksheet has the same name as the table.

While this is working with tables, you could use querydef and do similar.
If your issue is with the exporting to excel, hopefully this will give you some direction.

'---------------------------------------------------------------------------------------
' Procedure : TestExportWksheetsToWkBook
' Author    : user
' Date      : 1/22/2009
' Purpose   : To test exporting Access data to
'separate worksheets in 1 excel workbook
'---------------------------------------------------------------------------------------
'
Sub TestExportWksheetsToWkBook()
Dim db As DAO.Database
Dim tbl As TableDef
Dim i As Integer
Dim MyOutputxls As String
MyOutputxls = "I:\wordtest\output\TestExport.xls"
Set db = CurrentDb
i = 0
For Each tbl In db.TableDefs
    If Left(tbl.Name, 1) <> "~" And _
        Left(tbl.Name, 4) <> "MSys" Then
        i = i + 1
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, tbl.Name, MyOutputxls, True
    
    Else
    End If
Next
db.Close
Set db = Nothing
MsgBox "Finished processing " & i & "  worksheets at " & Now()
End Sub

 

[ewong1]

This was very helpful in getting the extract to work for queries on a blank workbook, but when I attempt to extract a query into an existing work book I get the following error message when I open it:

  <?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
- <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <logFileName>error268720_01.xml</logFileName> 
  <summary>Errors were detected in file 'C:\workspace\Uber_Barrel_Template.xlsx'</summary> 
- <removedParts summary="Following is a list of removed parts:">
  <removedPart>Removed Part: /xl/queryTables/queryTable1.xml part. (External data range)</removedPart> 
  </removedParts>
- <removedRecords summary="Following is a list of removed records:">
  <removedRecord>Removed Records: Named range from /xl/workbook.xml part (Workbook)</removedRecord> 
  </removedRecords>
  </recoveryLog>

It doesn't represent the data 100% accurately from my query either? One of the fields changes from a boolean to a text value in the extract and displays "GA" instead of 0 or false. Any thoughts on this would be appreciated.

Here is the VBA I'm using the generate the extract:

Sub btnExtract_Click()

Dim db As DAO.Database
Dim qry As QueryDef
Dim i As Integer
Dim MyOutputxls As String
MyOutputxls = "C:\workspace\Uber_Barrel_Template.xlsx"
Set db = CurrentDb
i = 0
For Each qry In db.QueryDefs
    If qry.Name = "qryModelExtract" Then
        i = i + 1
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, qry.Name, MyOutputxls, True
    
    Else
    End If
Next
db.Close
Set db = Nothing
MsgBox "Finished processing " & i & "  worksheets at " & Now()
End Sub