Include XLS Macro in Access 2007 Code?

rockafela9

New member
Local time
Today, 09:54
Joined
Jan 18, 2013
Messages
2
Hello All,

First off, I would like to thank the entire community for your help in creating the database I use. Although I've never directly posted a question, just reading through the different threads has answered so many of my questions. So THANK YOU

I have recently run into a sticky situation that, unfortunately, is specific to my database, so its hard for me to find a specifc answer for it.

So my issue is: I've created a Code that runs a query and stamps the current date into the file name that's saved to my computer: The code looks like this (works fine):



Public Function DynamicFileNameExampleFunction()
Dim Current_Date As String
Dim File_Name As String
Current_Date = Format$(Now(), "mm\-dd\-yyyy")
File_Name = "X:\Ops\Portfolio Administration\Admin\Account List\Staff Assignments " + Current_Date + ".xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Staff Portfolio Assignments", [File_Name]

End Function



Now, when this query is exported, I then open the file and run an XLS Macro to format the file how I need it (this includes bold, formatting header, font, freezing panes, etc)

Question:

How can I have Access automatically run the XLS macro, without me having to open the file and running in manually?

I guess there are two ways to go about getting this done: 1) Find the correlating Access code that is used in my XLS macro and write it up in Access form (don't want to do), or 2) include a command in my existing Access code to run a specific XLS macro.(prefer)


Is it possible to command Access to run a XLS macro?

If there is any confusion (which I'm sure there is), please let me know.

Thanks again for all your help!!!

Mark
 
Thanks beetle

The only problem with the code in the link is the fact that every time I run my existing code, the excel workbook has a new name (current date). Based on step 1 in the link you provided (pasted below), it looks like the workbook has to have the same, consistent name.


'Step 1:* Start Excel, then open the target workbook.
* * Set xl = CreateObject("Excel.Application")
* * xl.Workbooks.Open ("C:\Book1.xlsm")


I tried using the File name in my access code, but it doesn't work (code can't find file):

File_Name = "X:\Ops\Portfolio Administration\Admin\Account List\Staff Assignments " + Current_Date + ".xlsx"


Is there a way to run the macro, referencing the file created in my existing code?
 
Last edited:
I think because you're creating a new file each time from within Access, you'll need to open the file and execute your macro code from Access using the
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open (File_Name)
apply all your macro code to xl object and save, close

I don't see how you can create a new workbook and insert some code to run on Workbook_Open event.
David
 

Users who are viewing this thread

Back
Top Bottom