Can Access create Excel VBA

[Stephen sjw_ost]

Hello again,

I have a crazy question that I have not been able to figure out or find an
answer for, by way of looking thru help files or discussions.

Can Access;
1. create a temp Excel file
2. create a module within the temp excel file
3. write Excel code into the new Excel module
4. run the code writen into the Excel module
5. close the temp Excel file without saving the temp Excel file
All programically thru Access?

The code to be written to the Excel module is a SaveAs command that would
tell Excel to save a file specified from a Form in the Access DB.

Any help is greatly appreciated.
Stephen

 

[Tom van Stiphout]

On Sun, 11 Jan 2009 14:26:03 -0800, Stephen sjw_ost

Yes. All applications that support Automation can be "driven" by
another app. Search for CreateObject("Excel.Application") in your
favorite search engine.

-Tom.
Microsoft Access MVP

 

[Stephen sjw_ost]

Thank you for your response.

Yes I know they are interchangable. The #1 & #5 in my list, I know how to do
using this code from the help files;

Dim xlApp As Object ' Declare variable to hold the reference.

Set xlApp = CreateObject("excel.application")
' You may have to set Visible property to True
' if you want to see the application.
xlApp.Visible = True
' Use xlApp to access Microsoft Excel's
' other objects.
xlApp.Quit ' When you finish, use the Quit method to close
Set xlApp = Nothing ' the application, then release the reference.

What I need to find out how to do is #'s 2-4;

2. create a module within the temp excel file
3. write Excel code into the new Excel module
4. run the code writen into the Excel module

These are the parts that I have been having soooo much trouble
finding/figuring out. Do you know of any sample code that might show how to
create, write to and then run a module in Excel FROM Access?

Thanks again for all of your help
Stephen

 

[Dave Patrick]

Maybe this helps.

http://www.cpearson.com/Excel/VBE.aspx

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect