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

Tech-Archive recommends: Fix windows errors by optimizing your registry

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)" wrote:

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"
<TiffanyBillingCoordReportDeveloper@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:25DCF365-E9A4-4154-BAA5-85AE028B682D@xxxxxxxxxxxxxxxx
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)" wrote:

Use the TransferSpread*** macro to do the export. The macro will name
the
work*** 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
Developer@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:273A1DAF-FEAE-4DEB-A5CC-52328D18FB6A@xxxxxxxxxxxxxxxx
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! :-)






.

Quantcast