Include Stored Macros when Exporting to Excel

[Bonediggler1]

Hi-

This may be a bit of a unique question. I'm wondering if when exporting data to excel it is possible to attach/include a stored VBA file?

I inherited a process which uses an Access DB to process data and then export 10s of workbooks, each which thereafter needs to be formatted - via manually importing a VBA file and then running it as a macro.

If I could find a way to combine the two steps it would be great.

Thanks for your help!

 

[bubba100]

You can run an Excel macro from Access.

 

[Bonediggler1]

Ok. I'm wondering though if when creating an excel file from Access it is possible to attach a previously written/saved VBA file (.bas) along with it. Running the macro is step 2...

 

[bubba100]

Can't help with that I have no experience with a bas file. It is possible to with automation have Access control Excel. You can use Access to "call" an Excel macro. It is "possible" using Access to write a macro in Excel. This last method is by FAR the hardest and normally don't suggest the method. There is a security setting in Excel that will need to manually switched off.

 

[PHV]

A starting point:
yourExcelApplication.VBE.ActiveVBProject.VBComponents.Import("\path\to\yourModule.bas")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Bonediggler1]

Thank you PHV, this is a great start.

However, after a fair amount of searching it doesn't appear there is a workaround for manually checking the "Trust access to Visual Basic Project" box in macro security.

This prevents automation of module imports. Any ideas?