Run Excel VBA from Access module

[vs88]

I'm working with data that is periodically exported by an Access 2003
macro into an Excel worksheet. Then an Excel VBA module is manually
imported into the workbook and run to further manipulate the data.

The exported data is always written to a new Excel workbook from
Access, and is used by many different people, so it's not feasible to
install the Excel macro in a personal workbook for each user.

The Excel macro creates new column data using conditional formulas, and
several pivot tables.

Is there a way to incorporate the Excel VBA code into an Access module
and run it on the exported worksheet from Access?

Or is there some other way I've overlooked to automate this?

 

[Dave Patrick]

Option Explicit
Dim filePath, oExcel, oSheet

filePath = "c:\Test.xls"
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open(filepath)
Set oSheet = oExcel.ActiveWorkbook.Worksheets(1)
oExcel.Run "macro1"
oExcel.ActiveWorkbook.Save
oExcel.ActiveWorkbook.Close
set oSheet = Nothing
Set oExcel = Nothing

--
Regards,

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

| I'm working with data that is periodically exported by an Access 2003
| macro into an Excel worksheet. Then an Excel VBA module is manually
| imported into the workbook and run to further manipulate the data.
|
| The exported data is always written to a new Excel workbook from
| Access, and is used by many different people, so it's not feasible to
| install the Excel macro in a personal workbook for each user.
|
| The Excel macro creates new column data using conditional formulas, and
| several pivot tables.
|
| Is there a way to incorporate the Excel VBA code into an Access module
| and run it on the exported worksheet from Access?
|
| Or is there some other way I've overlooked to automate this?
|

 

[vs88]

Thanks for the code. The real dilemma here is where do I store the
macro code for the Excel piece? Access outputs a new Excel workbook
each time the job is run.

 

[Dave Patrick]

Yes you'll probably need to create an excel template XLT then have the
workbook code do a save as some other file name. Just an idea anyway.

--
Regards,

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

| Thanks for the code. The real dilemma here is where do I store the
| macro code for the Excel piece? Access outputs a new Excel workbook
| each time the job is run.
|

 

[John Nurick]

Put the Excel code into the Access function, modifying it as required to
account for the changed context. In particular, use Range objects to
identify the cells you want to operate on rather than using the
Selection object. This is Dave's code slightly modified:

filePath = "c:\Test.xls"

Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open(filepath)

Set oSheet = oExcel.ActiveWorkbook.Worksheets(1)

With oSheet
.Range("B:B").Insert
.Cells(1,2).Formula = 25.4
.Cells(2,2).Formula = 39.4
.Cells(3,2).Formula = "=A1*A2"
'anything you want to do
End With

Set oSheet = Nothing

With oExcel
.ActiveWorkbook.Save
Do While .Workbooks.Count > 0
.Workbook(.Workbooks.Count).Close False
Loop
.Quit
End With

Set oExcel = Nothing