How to I reference an Excel Module form MS Access?

[June Macleod]

My MS Access 2000 application creates and populates and excel spreadsheet.

I would like to find a method by which I can create and populate an Excel
Module with some code which controls a custom toolbar.

Can anyone tell me how I reference an excel module from access.

Thanks

June Macleod

 

[Shao]

You can use the TransferSpreadsheet action in Visual basic
to import or export data between the current Access
database and a spreadsheet file.

DoCmd.TransferSpreadsheet [transfertype][,
spreadsheettype], tablename, filename[, hasfieldnames][,
range
]

 

[Gerard Sayers]

You can access the Excel Object Model and can do that, it would be quite
complex though.

You need to open Excel in code then give it instructions i.e.

In Access VBA from the Tools Menu select References
- Find then Microsoft Excel library and check mark it

Use the following code to open and close excel

'Start a new instance of Excel
Dim appExcel as new Excel.Application

'Show Excel
appExcel.Visible = True

'Open your workbook
set MyWorkbook = appExcel.Workbooks.Open(...)

'Exit Excel
appExcel.Close

Explore the object model to find how to add and change modules, never done
it personally.... .... .... ....

After taking a bit of a look around Excel, I can see that you would need to
use the VBIDE library known in Tools->References as Micorosft Visual Basic
for App Extensisbility (Check mark it)

To add code to a module would involve accessing the following model object,

MyWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule.AddFromString("
Code that you want to Add")

Hope this helps