Excel automation from Access?

[pduncan]

I have a simple access/vba application that imports an excel spreadsheet using transfer spreadsheet. Next it runs a query and groups records into smaller spreadsheets. It then mails each spreadsheet to a recipient as determined by a lookup table.

Everything works well with this application except that I still need to sort the excel data prior to mailing it. If I were to make a macro in excel to do this, it would be this:

Sub Macro1()

    Cells.Select
    Selection.Subtotal GroupBy:=6, Function:=xlSum, TotalList:=Array(9, 10, 11, _
        12, 13), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

Anyway, is there a way to have access run this code on an excel spreadsheet, then mail it?

Thanks


Thanks, PDUNCAN

 

[jbpelletier]

Sure,

if u Access dont reconize that code u can edit it in an empty .xls file (ex: MyMacro in macro.xls) then call this code. (The file on witch you whant to execute that code must be open...)

sub test()
On Error Resume Next
Dim objXL As Object
Dim mypath As string

Set objXL = CreateObject("Excel.Application")
objXL.Application.Visible = True

'workabook that contain all macro u may use
mypath = "C:\TEMP"
objXL.Application.Workbooks.Open mypath & "\" & "macro.xls"

'Here you should make the destination page active
'And in Mymacro always refer to the "active sheet"

'run the macro
objXL.Application.Run "macro.xls!MyMacro"

objXL.Application.Workbooks("macro.xls").Close
Set objXL = Nothing
end sub

jb

 

[DougStevens]

Okay, here's one site that used often to find help on MS Access.

Check out this article on using Excel Automation from within Access.

http://www.mvps.org/access/modules/mdl0007.htm

Hope that helps.