I am using MS Office 2007 and Vista.
I have 3 queries that I would like to export to one Excel Workbook. The 3
queries are named "ash," "bas," and "chn." I would like to export themto an
Excel workbook named "Buyers." I have in this workbook three worksheets
named "ash," "bas," and "chn." I would like to export the three queries
above to the individual worksheets with the same name, within the workbook
"Buyers." When I try to set this export up in Access, I am only allow to go
as far a saying what workbook name I would like to export to, not the
individual worksheet. How can I accomplish what I would like to do?
Also, my data changes in these queries often. Can I set it up so that when
the data changes in the queries that the data will also change in the
spreadsheet automatically also?
Thank you!!
Hi, William,
These examples are from Access 2007.
The first code example to output queries to the same Excel workbook
does refresh the data in Excel.
The second example defines a unique Excel filename so that its data
never gets overwritten.
First example. This example is of a few lines that show the output to
Excel for a table rather than a query, but the result is the same.
The existing data in the Excel file on the tab called
"t0520ProcessVars" is deleted and the new data from Access is written
to that tab:
' output the Process Variables to an Excel file for safety
stPathFileName = stPath & "SeaFar-East+AppBkupTemp"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"t0520ProcessVars", stPathFileName, True, "t0520ProcessVars"
Note that I do not include the Excel file extension ".xls". In
another year or so I will change the argument
"acSpreadsheetTypeExcel9" to the "acSpreadsheetTypeExcel12" to take
advantage of the larger record capacity in Excel 2007, and I don't
want to go back and edit all of the extensions. I do the same with my
Excel imports into Access.
Second example:
Public Function vbSfProductCompareToExcel()
SysCmd acSysCmdSetStatus, "Output Product Comparison tables"
Dim rsMbsNames As DAO.Recordset
Dim s As String
Dim stMod As String
Dim stPath As String
Dim stPathFileName As String
Dim stVarFileName As String
Dim db As DAO.Database
Set db = CurrentDb()
stMod = "vb4044SfProductCompareToExcel"
Set rsTitleVars = db.OpenRecordset("t0502TitleVars")
stPath = rsTitleVars!Path
' check for a critical stop
If rsTitleVars!CriticalStop = True Then
GoTo AllDone
End If
rsTitleVars.Edit
rsTitleVars!Module = stMod
rsTitleVars.Update
stYear = CStr(Year(Now))
stMonth = CStr(Format(Month(Now), "00"))
stDay = CStr(Format(Day(Now), "00"))
stTime = CStr(Format(Now, "hhmmss"))
stTimeTag = stYear & "-" & stMonth & "-" & stDay & "-" & stTime
stFileName = "SeaFar-East" & "+ProductCompare+"
stFileNameDateTime = stFileName & stTimeTag
stPathFileName = stPath & stFileNameDateTime
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"q4042ProductComparePivot", stPathFileName, True,
"ProductComparePivot"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"teAssumptionsBase", stPathFileName, True, "AssumptionsBase"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"teAssumptionsCompare", stPathFileName, True, "AssumptionsCompare"
s = "Product Compare results sent to file: "
s = s & stPathFileName
MsgBox s
AllDone:
SysCmd acSysCmdClearStatus
End Function
