Another Access to Excel & run macro automatically... 1

[blondends]

Hi,

I have read a lot posts talking about this subject but i just can't get some of the replies to work. So i have decided to post my own question...

I want to export the contents of a query to a new excel file and then to run an excel macro (that formats the data - and sub totals) at the press of a command button.


So far this is the code that i have:

DoCmd.OutputTo acOutputTable, lcQueryName, acSpreadsheetTypeExcel8, strReportName, True, ""



This works but now for the bit i really cant seem to get right, i want to now run this macro code:

Cells.Select
Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range("F2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers, DataOption3 _
:=xlSortNormal
Selection.Subtotal GroupBy:=7, Function:=xlSum, TotalList:=Array(8, 9, 10, _
11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, _
37, 38, 39), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Selection.AutoFormat Format:=xlRangeAutoFormatSimple, Number:=False, Font _
:=False, Alignment:=True, Border:=True, Pattern:=False, Width:=False


So how do i do it? This is really doing my head in now!!

Thanks in advance for any ideas and suggestions.

Chris

 

[PHV]

You may try something like this:
DoCmd.OutputTo acOutputTable, lcQueryName, acSpreadsheetTypeExcel8, strReportName, False, ""
Dim myWB As Excel.Workbook
Set myWB = GetObject(strReportName)
MyWB.Application.Cells.Sort Key1:=MyWB.Application.Range("G2"), Order1:=xlAscending, Key2:=MyWB.Application.Range("F2") _
, Order2:=xlAscending, Key3:=MyWB.Application.Range("B2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers, DataOption3 _
:=xlSortNormal
MyWB.Application.Cells.Subtotal GroupBy:=7, Function:=xlSum, TotalList:=Array(8, 9, 10, _
11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, _
37, 38, 39), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
MyWB.Application.Cells.AutoFormat Format:=xlRangeAutoFormatSimple, Number:=False, Font _
:=False, Alignment:=True, Border:=True, Pattern:=False, Width:=False
MyWB.Save
MyWB.Application.Quit
Set MyWB = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[blondends]

Hi PHV,

Thanks for the very quick reply. I have copied your code and then when i ran the code i got an error...

Compile Error: User-defined type not defined.

At this bit of code...

myWB As Excel.Workbook

This is an error that i have seen while trying out code in other posts.

Any ideas ?

Cheers,

Chris

 

[PHV]

You have to reference the Microsoft Excel Library:
When in VBE(Alt+F11), menu Tools -> References ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[blondends]

Cool, that has sorted out that error... but you guessed it i have another. At this point:

DoCmd.OutputTo acOutputTable, lcQueryName, acSpreadsheetTypeExcel8, strReportName, True, ""

This error is returned:

Run-Time error (3709):
The search key was not found in any record.

The only thing that i can think it is is that it now says acOutputTable not acExport.

Anyway a star for the help so far !!!

Cheers,

Chris

 

[blondends]

Hold on, i have sorted that last error out now by following my own train of thought.

Is there a way of automatically installing the excel reference library via code - cause this database is going to use on many pc's, and going round them all would be a realy pain.

Cheers,

Chris

 

[PHV]

Is lcQueryName a parametized query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[blondends]

Umm, not quite sure what you mean. But here is a stab - the query does have criteria (e.g. date range entered on a form). Is that what you mean?

Chris

 

[PHV]

So, the the forms must be open and the date range textboxes properly populated when you launch the OutputTo.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[blondends]

Ok,

So what is the difference between acOutputTable and acExport?

Chris

 

[PHV]

No F1 key on your keyboard ?

 

[blondends]

Umm yeah - sorry i am very new to VBA. Cheers for your help. Didn't mean to bug you.

Chris