Access macro: How to output 3 queries to 3 tabs in 1 Excel workboo

[Tiffany - Billing Coord/Report Developer]

I was hired at my company about 2 months ago, and I am trying to automate
monthly reports that have been created in Access 2007. In the past,
everything was done manually. I have already automated up to the point that
the main reports have parameters and output nicely rather than having to go
into SQL view and change the code every time the reports are run.

Now my problem is that most clients have 2 or 3 completely different queries
included in the monthly Excel workbooks we send to them (using Excel 2007,
but reports are saved as 97-03 to avoid compatability issues with clients).
I would like to use a macro to open (in this example) 3 different queries and
output to 3 tabs in the same Excel workbook, but can't get the OutputTo
function to output multiple queries.

Help! Figuring out how to do this will literally cut hours off of the
reporting process I have to go through each month!

Thank you!

 

[Ken Snell \(MVP\)]

Use the TransferSpreadsheet macro to do the export. The macro will name the
worksheet the same name as the query that is being exported. So, exprt three
queries that have different names, but export them into the same EXCEL
workbook.
--

Ken Snell
<MS ACCESS MVP>


"Tiffany - Billing Coord/Report Developer" <Tiffany - Billing Coord/Report
(e-mail address removed)> wrote in message
news:[email protected]...

 

[Tiffany - Billing Coord/Report Developer]

I haven't used that function before, but it worked great! I'm still a bit
new to macros and queries by form though I've used Access extensively.

I do have one other question though. I have set the macro up with all 3
queries being exported, and then "runapp" opens the workbook. Is there any
way to have a macro I've already created in Excel (that creates pivot tables
based on the 3 tabs) to run automatically? I want the macro to run from
Access because I want it to ONLY run when I actually run the Access macro
(rather than anyone who opens the workbook inadvertently creating more pivot
tables because of an excel macro that runs when the file is opened). I am
basically doing all of this so that I can use a switchboard to run my monthly
reports and automate them as much as possible due to month-end time
limitations.

Thank you for your help!

Tiff

 

[Ken Snell \(MVP\)]

Here is a VBA subroutine that can be used to run an EXCEL macro in an EXCEL
file that is already open.

'********************************
'* Call an EXCEL macro from VBA *
'********************************

Public Sub RunAnExcelMacro()
Dim xls As Object, xwkb As Object
Dim strFile As String, strMacro As String
strFile = "ExcelFilename.xls"
strMacro = "MacroName"
Set xls= CreateObject("Excel.Application")
xls.Visible = True
Set xwkb = xls.Workbooks.Open("C:\" & strFile)
xls.Run strFile & "!" & strMacro
xwkb.Close False
Set xwkb = Nothing
xls.Quit
Set xls = Nothing
End Sub
--

Ken Snell
<MS ACCESS MVP>


"Tiffany - Billing Coord/Report Developer"

 

[Tiffany - Billing Coord/Report Developer]

I think that will work, but there is one problem. This doesn't work for a
file that's already open...it opens an occurrence of the file. That part
isn't an issue. I took the call line out of the code that runs the 3 queries
and outputs them to the workbook. The problem is, this opens the file as a
read-only, which is disabling my macros. :-(

Sorry to keep bugging you!

 

[Ken Snell \(MVP\)]

If the EXCEL file is already open, then the VBA code is much simpler:

Public Sub RunAnExcelMacro(strWorkbookName As String)
Dim xls As Object, xwkb As Object
Dim strMacro As String
strMacro = "MacroName"
Set xls = GetObject(, "Excel.Application")
Set xwkb = xls.Workbooks(strWorkbookPathName)
xls.Run strFile & "!" & strMacro
Set xwkb = Nothing
Set xls = Nothing
End Sub

--

Ken Snell
<MS ACCESS MVP>


"Tiffany - Billing Coord/Report Developer"

 

[Ken Snell \(MVP\)]

Sorry, typo:

Public Sub RunAnExcelMacro(strWorkbookName As String)
Dim xls As Object, xwkb As Object
Dim strMacro As String
strMacro = "MacroName"
Set xls = GetObject(, "Excel.Application")
Set xwkb = xls.Workbooks(strWorkbookName)
xls.Run strFile & "!" & strMacro
Set xwkb = Nothing
Set xls = Nothing
End Sub
--

Ken Snell
<MS ACCESS MVP>

 

[Tiffany - Billing Coord/Report Developer]

It didn't work with that exact coding, but I only had to make a small change
or two, like saving the Excel Macro to the workbook rather than a personal
macro book. Then I took off the part at the end that closes the report so
that I can add a custom label every time. THANK YOU SO MUCH for all of your
help!

Here's what I ended up with as my access coding, and it works like a charm!


' FPL_Monthly_Report
'
'------------------------------------------------------------
Function FPL_Monthly_Report()
On Error GoTo FPL_Monthly_Report_Err

DoCmd.TransferSpreadsheet acExport, 8, "FPL Monthly Fee Detail",
"\\cxdnetapp3\accounting\Tiffany\FPL Monthly Report.xls", True, ""
DoCmd.Close acQuery, "FPL Monthly Fee Detail"
DoCmd.TransferSpreadsheet acExport, 8, "FPL Monthly Plan Detail",
"\\cxdnetapp3\accounting\Tiffany\FPL Monthly Report.xls", True, ""
DoCmd.Close acQuery, "FPL Monthly Plan Detail"
DoCmd.TransferSpreadsheet acExport, 8, "FPL Monthly Premium Detail",
"\\cxdnetapp3\accounting\Tiffany\FPL Monthly Report.xls", True, ""
DoCmd.Close acQuery, "FPL Monthly Premium Detail"
DoCmd.Close acMacro, "FPL Monthly Report"

Dim xls As Object, xwkb As Object
Dim strFile As String, strMacro As String
strFile = "FPL Monthly Report.xls"
strMacro = "FPLMonthlyReport"
Set xls = CreateObject("Excel.Application")
xls.Visible = True
Set xwkb = xls.Workbooks.Open("\\cxdnetapp3\accounting\Tiffany\" & strFile)
xls.Run strMacro

FPL_Monthly_Report_Exit:
Exit Function

FPL_Monthly_Report_Err:
MsgBox Error$
Resume FPL_Monthly_Report_Exit

End Function

 

[Ken Snell \(MVP\)]

You're welcome. Good luck.

--

Ken Snell
<MS ACCESS MVP>



"Tiffany - Billing Coord/Report Developer"