Excel Automation from Access

[Guest]

I have this code in access which transfers 4 tables into an excel
spreadsheet. I have a macro in excel that does the formatting.

Is there a way to trigger the excel formatting macro from the access code,
instead of me manually having to open the spreadsheet and using the menu to
run the macro?

Heres the access code:

Private Sub cmdAssocGenerator_Click()
Dim FullFileName, Table1, Table2, Table3, Table4 As String

Table1 = "dbo_Assoc10-Demographics&Volume&CB"
Table2 = "dbo_Assoc10-Equipment"
Table3 = "dbo_Assoc10-InvoiceMast"
Table4 = "dbo_Assoc10-InvoiceMast QA"
FullFileName = "n:\DWarehouse\Dal\" & txtFileName.Value & ".xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, Table1,
FullFileName, Yes
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, Table2,
FullFileName, Yes
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, Table3,
FullFileName, Yes
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, Table4,
FullFileName, Yes

MsgBox "DONE!", vbOKOnly, "Exporting Finished"


End Sub


And here is the name of my excel macro:

personal.xls!Can_format

 

[Guest]

I also have a follow up question. Would it be possible to store the excel
formatting code in Access? that way someone else in the company could use
this, and not have to have my formatting macro stored in excel on their
computer?

 

[Guest]

You can use the Shell function to open the spreadsheet and the RunMacro
method to run the macro.

 

[Guest]

I'm exporting to a new spreadsheet, not a saved one. how would i use the
shell command for this particular situation?

Where do i put the runmacro command????in the Access code?

 

[Guest]

If you still have questions, there is an EXCELLENT book called "Excel 2000
VBA" by John Green (published by Wrox) that should answer all your questions.

Also, check out the mvps website for code examples. Look up Modules and
APIs. I found Dev Ashish's fIsAppRunning an enormous help.
http://www.mvps.org/access/modules/index.html

In general, yes, you can run the formatting from Access VBA. I currently
have 11 (and growing) Excel reports created with automation and then
automatically emailed to users (including a user-form with 2 popup listboxes
created each time with current Access data the user can "multipik") .

 

[Guest]

Billy,

Try this out:

dim exApp as Excel.Application
dim exBook as Excel.Workbook

set exApp = new Excel.Application
set exBook = exApp.Workbooks.Open(myTemplateFileName)
exApp.Run "ThisWorkbook.MyFormattingMacro"
exBook.Save mySaveFileName
exBook.Close
exApp.Quit
set exBook = nothing
set exApp = nothing

What I tend to do, is make a template Excel sheet (it can be saved as xls
you don't have to save as xlt), and right a macro in there to format my
sheets once I've got them in place, if I need to use TransferSpreadsheet I
may use a FileSystemObject to copy the template sheet over to the new
filename, transfer the tables to the new file, open up that file as an excel
object, run the macro, then save and close. Hope that helps.