format excel spread sheet

[Guest]

I am trying to run a macro to format the exported spreadsheet. Here is my code

XL.Visible = False
'XL.Workbooks.Open "C:\Documents and Settings\U245003\My
Documents\Automation\Macros.XLS" 'this is where my macros are stored.
'XL.Workbooks.Open '"C:\Documents and Settings\U245003\My
Documents\Automation\Results\format.xls"
'XL.Run "Clean_TMS_WF_Report"
'XL.Quit
'XL.Visible = False

this does not work because.
after opening macros.xls, it is closing macros.xls before opening format.xls.
all my macros are saved in macros.xls so that all users can use this. since
it is nor in personal.xls, i have to open macro.xls before running the macro.

when i run this code i get this error "macro not found"

Please advice

and thanks a lot for all your help. I really appreciate this.

 

[Guest]

I am trying to run a macro to format the exported spreadsheet. Here is my code

XL.Visible = False
'XL.Workbooks.Open "C:\Documents and Settings\U245003\My
Documents\Automation\Macros.XLS" 'this is where my macros are stored.
'XL.Workbooks.Open '"C:\Documents and Settings\U245003\My
Documents\Automation\Results\format.xls"
'XL.Run "Clean_TMS_WF_Report"
'XL.Quit
'XL.Visible = False

this does not work because.
after opening macros.xls, it is closing macros.xls before opening format.xls.
all my macros are saved in macros.xls so that all users can use this. since
it is nor in personal.xls, i have to open macro.xls before running the macro.

when i run this code i get this error "macro not found"

Please advice

and thanks a lot for all your help. I really appreciate this.

 

[Guest]

I don't see where you got any replies to this so far. I have a database that
creates a recordset and exports it to Excel, where the customer then wants it
formatted a certain way. To do that I open the newly created Excel file with
Access VBA, then insert the ***EXCEL*** VBA into it as a macro, then tell
Excel to run that macro, then shut down Excel. For this to work, the Excel
VBA has to be stored in a text file, but once it's run, that code is a
permanent part of the Excel file you created (not that I can think of a
reason you'd ever need to run it again). Fairly simple to code, and works
dandy. If you haven't already solved your problem, let me know and I can
post or E-mail my code.