asked on

VBA to extract multiple MS Access queries into a single workbook into specific worksheets with formatting

Can you write two VBA statements that extract different MS Access queries into a Workbook but into specific worksheets that keeps the formatting. For example create a spreadsheet with two tabs (Details & Total). Can you accomplish this with an OutputTo statement?
Ex. DoCmd.OutputTo acOutputQuery, "ParPlanDetails", acFormatXLS, " H:\Email\AccountingPlans.xlsx ", False, ""

The above enters the data into a single worksheet and it maintains the formatting. However a transfer statement can put the data into specified worksheets but doesn’t maintain the formatting:
Ex. DoCmd.TransferSpreadsheet acExport, , "QfltData", "H:\Email\AccountingPlans.xlsx", True, "Details"
DoCmd.TransferSpreadsheet acExport, , "QfltDataTotals", "H:\Email\AccountingPlans.xlsx", True, "Total"

Thanks

ASKER CERTIFIED SOLUTION

Dale Fye

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

Helen Feddema

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Helen Feddema

See my Access Archon #127 for an example of creating workbooks from an Excel template. Here is a link for downloading it:

http://www.helenfeddema.com/Files/accarch127.zip

The code in the sample database uses DAO recordsets, and writes data directly to specific cells of the new workbook.

CFMI

ASKER

Experts,
Thanks for the excellent advice. I was just called to work on a higher priority project and I am hoping to return to this on Monday. Have a nice weekend!

CFMI

ASKER

Hello Experts,
The below code receives an error message - User-defined type not defined and it highlights the "Dim fld As DAO.Field" statement:

Private Sub Command69_Click()
Dim intCol As Integer
Dim fld As DAO.Field
Dim xl As Excel.Application
Dim wbk As Excel.Workbook
Dim sht As Excel.Worksheet
Dim rs As DAO.Recordset
Set xl = CreateObject("Excel.application")
xl.Visible = True
Set wbk = xl.Workbooks.Add
Set sht = wbk.Sheets.Add
sht.Name = "New Sheet1"
sht.Range("A1").Value = "Details"
Set sht = wbk.Sheets.Add
sht.Name = "New Sheet2"
sht.Range("A2") = "Total"
Set sht = wbk.Sheets.Add
sht.Name = "From Recordset"
Set rs = CurrentDb.OpenRecordset("QfltData", , dbFailOnError)
sht.Range("A2").CopyFromRecordset rs
intCol = 1
For Each fld In rs.Fields
sht.cells(intCol, 1).Value = fld.Name
Next
rs.Close
Set rs = Nothing
Set xl = Nothing

End Sub

CFMI

ASKER

I had to update the reference to the correct object library and the code works!!! - Thanks...