Access Modules

[smashclash]

I use Access Macro's to spit out a few reports all from one database. I just noticed the module tab in Access and I have a question. Currently, I run my macro and various tables are created that are excel spreadsheets. After I run these macro's all the Excel sheets are open. I then have Macro's in excel for each report I run to format them the way I want.

My question is, can I put those excel module (macro's) into the Access database so when the reports are finished running they auto format themselves in access rather than having me format them in Excel? I do not do any formatting of the reports after Access spits them out. I simply click on the run macro in excel and that's it.

 

[dtaylor]

Hi There -

You cannot put the excel macro's in access but you can launch the excel macro's from access elimating the need to open up each spreadsheet and launching your format macro's.

here is the code to launch an excel macro from access - I am assuming your using 2000 with ADO
Create a new module in access and add a reference to Microsoft Excel 9.0 Object Library

Sub RunEXLMcr()
Dim db As ADOB.Connection
Dim appXL As Excel.Application
Dim wk as Excel.WorkBook

Set db = CurrentProject.Connection
Set appXL = New Excel.Application
Set wk = appXL.WorkBooks.Open("EXCEL FILE NAME AND PATH")

appXL.Run "Macro Name"
appXL.Quit

Set appXL = Nothing
Set db = Nothing

Exit Sub
End Sub


You could use this for each of your workbooks. In your current macro in access after you export each table add a line launching this code.

Hope this helps or at least points you in the right direction!

Dan

 

[Greg Truby]

Dan,

Just curious, why the connection object? I don't see where it's getting used. Am I missing something?

 

[dtaylor]

Greg Truby - my bad .... I copied this out of db where I am using the current connection. I should of deleted those lines with everthing else I deleted!

thanks for pointing that out

smashclash - disregard the db connections lines


Dan

 

[smashclash]

Hi There -

You cannot put the excel macro's in access but you can launch the excel macro's from access elimating the need to open up each spreadsheet and launching your format macro's.

here is the code to launch an excel macro from access - I am assuming your using 2000 with ADO
Create a new module in access and add a reference to Microsoft Excel 9.0 Object Library

Sub RunEXLMcr()
Dim db As ADOB.Connection
Dim appXL As Excel.Application
Dim wk as Excel.WorkBook

Set db = CurrentProject.Connection
Set appXL = New Excel.Application
Set wk = appXL.WorkBooks.Open("EXCEL FILE NAME AND PATH")

appXL.Run "Macro Name"
appXL.Quit

Set appXL = Nothing
Set db = Nothing

Exit Sub
End Sub


You could use this for each of your workbooks. In your current macro in access after you export each table add a line launching this code.

Hope this helps or at least points you in the right direction!

Dan

Could someone please help me with dtaylors reply?

Here's is what I assume I need to change in bold.


Sub New Access Macro name ()
Dim appXL As Excel.Application
Dim wk as Excel.WorkBook

Set db = CurrentProject.Connection
Set appXL = New Excel.Application
Set wk = appXL.WorkBooks.Open(" J:/South XXXXX ")

appXL.Run " Excel Macro name "
appXL.Quit

Set appXL = Nothing
Set db = Nothing

Exit Sub
End Sub

Is this right? I feel like I'm missing something.

How do I add the line in the macro to launch this code?

 

[dtaylor]

Hi smashclash -

as was pointed out i shouldn't of provided an example establishing a connection - you can take out those lines..

Sub New Access Macro name ()
Dim appXL As Excel.Application
Dim wk as Excel.WorkBook

Set appXL = New Excel.Application
Set wk = appXL.WorkBooks.Open(" J:/South XXXXX ")

appXL.Run " Excel Macro name "
appXL.Quit

Set appXL = Nothing
Set db = Nothing

Exit Sub
End Sub


This should run ok - were you getting an eror messag? Probably because yuo still had a line set the current connection. I did not mean to post the database connection back when.

Take out the "Set db = CurrentProject.Connection"

and the bolded lines are all you need to change.

was there something else you are concerned about?

Also do not have any spaces in the routine name..
e.g., Sub NewAccessMacroName() -- or whatever you want the name to be

Dan

 

[smashclash]

I'm pretty good with Excel macro's but Access is something totally new for me. I don't really have a clue how to set this up. I believe I need to copy this information into a "module" in the access box. However, I don't know how to link this module to a report that comes out of the Access macro. Any advice? BTW, thanks for the help so far.

Also, the workbook path I have to put in the module is the workbook that contains my macro's or the workbook that the excel macro is going to run on?

 

[dtaylor]

Gotcha - with the access file open goto - modules - select 'New' - this will bring up the a new module. just copy this code to this module - then save and name the module.

once you have your module saved - just run the routine as you would in from the excel visual basic editor. put the mouse cursor within the routine (after Sub xxxx() and before End Sub). then from the tool bar select - Run .....


if you still have probs repost..

dan

Edit - ok i am at work and did not fully read your last post...

The path needs to be the workbook containing your macros.

This macro will only run an excel macro that is already setup to fucntion. How are you extacting your reports?

If your excel workbooks are already setup - with the one containing macros pointing to the correct workbook to update - you do not need to link this code to your report.

But I do not know how your are extracting your data from access or how you are currently running your master format macro agaisnt your report.


Dan

 

[dtaylor]

one thing i did not notice earlier but:

Set wk = appXL.WorkBooks.Open(" J:/South XXXXX ")

should be:

Set wk = appXL.WorkBooks.Open(" J:\South XXXXX.xls ")

 

[bat17]

What sort of Formatting are you trying to achieve? and when you say that you

use Access Macro's to spit out a few reports

are you actualy generating reports using Reports in Access or just outputting the values from a query?

Peter