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.

 

[smashclash]

Hmmm,

I copied this database from the Network drive to my hard drive incase I screwed something up I didn't want to ruin the good file. Now I'm trying to copy the module into the network database and when I run it I'm getting "Compile error:" User-defined type not defined.

The VB is highlighting "appXL As Excel.Application" after Dim.
Then when I hit debug it highlights the name of the module. Could it be because Excel is on my computer and the database is on a network hard drive?


What did I forget to do?

 

[bat17]

To control the code from elsewhere you just need to set up a button on a form with a call to the code on its click event.
Call TestXl

To pass information to the Excel Macros you will need to add arguments to them.
So
"Public Sub Outside_Services()"
becomes
"Public Sub Outside_Services(strText As String)"
In the XL macro you will need to add a line of code to add the variable to the sheet.
Range("a1") = strText


To pass the data to XL you need to tweak the code that I gave you to pass the variable.
I would set up a text box on the form that you have added the button to run the code. You can then insert the Text that you want to show in Excel in there.

Sub TestXl()

Dim appXL As Excel.Application
Dim wk As Excel.Workbook
Dim bolGo As Boolean
Dim strParameter As String
Set appXL = New Excel.Application

strParameter = Forms![zztemp]![txtToExcel] ' refers to a text box on a form

' The path/name of the XL file that holds your macro goes here.
Set wk = appXL.Workbooks.Open("C:\a2\book1.xls")
appXL.Visible = True
'then start listing all of the macros that you use to format data here.

DoCmd.OutputTo acQuery, "QueryNameHere", "MicrosoftExcel(*.xls)", "", True, ""
appXL.Run ("book1.xls!Outside_Services"), (strParameter) ' change this for the book name and Macro required


The error you are getting means that you need to set up a reference.
In a module go Tool>references…. Look down the list and select the Microsoft Excel 9.0 Object Library. (The number may vary depending on your version of XL)

HTH

Peter

 

[mdmilner]

Under Tools-References on the Networked Database, add Microsoft Excel 9.0 Object Library

Mike

 

[smashclash]

I was able to setup the Form and command button. I put a textbox in the form and labeled it, "Date". I left the message as unbound. However, I'm getting a 438 error - Object doesn't support this property or method.

My string parameter looks like:

strParameter = [Forms]![Supply_Export]![Date]

(Supply_Export is the name of my form)

Any ideas?

 

[smashclash]

This is a copy of my code:

Sub Supply_Export()

Dim appXL As Excel.Application
Dim wk As Excel.Workbook
Dim bolGo As Boolean
Dim strParameter As String
Set appXL = New Excel.Application

strParameter = [Forms]![Supply_Export]![Date]

' The path/name of the XL file that holds your macro goes here.
Set wk = appXL.Workbooks.Open("J:\SJH\MACROS.xls")
appXL.Visible = True
'then start listing all of the macros that you use to format data here.

DoCmd.OutputTo acQuery, "Agency Detail Report", "MicrosoftExcel(*.xls)", "", True, ""
appXL.Run ("MACROS.xls!Agency_Detail.Agency_Detail") ' change this for the book name and Macro required

bolGo = MsgBox("Supply by Dept", vbOKOnly)
DoCmd.OutputTo acQuery, "New Supply by Dept", "MicrosoftExcel(*.xls)", "", True, ""
appXL.Run ("MACROS.xls!Supply_by_Dept.Supply_by_Dept") ' change this for the book name and Macro required

bolGo = MsgBox("Supply Detail", vbOKOnly)
DoCmd.OutputTo acQuery, "Supply Detail Report", "MicrosoftExcel(*.xls)", "", True, ""
appXL.Run ("MACROS.xls!Supply_by_Detail.Supply_by_Detail") ' change this for the book name and Macro required

bolGo = MsgBox("Natural Class", vbOKOnly)
DoCmd.OutputTo acQuery, "New Natural Class", "MicrosoftExcel(*.xls)", "", True, ""
appXL.Run ("MACROS.xls!Natural_Class.Natural_Class") ' change this for the book name and Macro required

bolGo = MsgBox("Outside Services", vbOKOnly)
DoCmd.OutputTo acQuery, "New Outside services Detail", "MicrosoftExcel(*.xls)", "", True, ""
appXL.Run ("MACROS.xls!Outside_Services.Outside_Services") ' change this for the book name and Macro required


'finaly clean up behind yourself and quit excel
appXL.Quit
Set wk = Nothing
Set appXL = Nothing
End Sub

Not sure if this will help or not

As far as the Excel macro's go. For each macro I have to add "strText As String" inside the ( ) at the top of the macro. Also change Range("a3")=strText.

That is all I have to change in the excel macro's correct?

 

[bat17]

I dont have time to review you code just at the moment but one quick thought. Date is a reserved word in Access and you may be confusing things so try changing to dDate or something.

peter

 

[smashclash]

I was able to get the form to work. I put the text box in and changed the Date thing to dDate like you suggested. However, I am now getting a '449' error - Argument not optional. The debugger then highlights:

appXL.Run ("MACROS.xls!Agency_Detail.Agency_Detail")

Below is a copy of my access code. I'll post one of the excel macro codes in another reply.

Sub supply_export()

Dim appXL As Excel.Application
Dim wk As Excel.Workbook
Dim bolGo As Boolean
Dim strParameter As String
Set appXL = New Excel.Application

strParameter = [Forms]![Supplies]![dDate]

' The path/name of the XL file that holds your macro goes here.
Set wk = appXL.Workbooks.Open("J:\SJH\MACROS.xls")
appXL.Visible = True
'then start listing all of the macros that you use to format data here.

DoCmd.OutputTo acQuery, "Agency Detail Report", "MicrosoftExcel(*.xls)", "", True, ""
appXL.Run ("MACROS.xls!Agency_Detail.Agency_Detail") ' change this for the book name and Macro required

bolGo = MsgBox("Supply by Dept", vbOKOnly)
DoCmd.OutputTo acQuery, "New Supply by Dept", "MicrosoftExcel(*.xls)", "", True, ""
appXL.Run ("MACROS.xls!Supply_by_Dept.Supply_by_Dept") ' change this for the book name and Macro required

bolGo = MsgBox("Supply Detail", vbOKOnly)
DoCmd.OutputTo acQuery, "Supply Detail Report", "MicrosoftExcel(*.xls)", "", True, ""
appXL.Run ("MACROS.xls!Supply_by_Detail.Supply_by_Detail") ' change this for the book name and Macro required

bolGo = MsgBox("Natural Class", vbOKOnly)
DoCmd.OutputTo acQuery, "New Natural Class", "MicrosoftExcel(*.xls)", "", True, ""
appXL.Run ("MACROS.xls!Natural_Class.Natural_Class") ' change this for the book name and Macro required

bolGo = MsgBox("Outside Services", vbOKOnly)
DoCmd.OutputTo acQuery, "New Outside services Detail", "MicrosoftExcel(*.xls)", "", True, ""
appXL.Run ("MACROS.xls!Outside_Services.Outside_Services") ' change this for the book name and Macro required


'finaly clean up behind yourself and quit excel
appXL.Quit
Set wk = Nothing
Set appXL = Nothing
End Sub

 

[smashclash]

Here is my excel code. I cut out a lof the macro because it just takes up unnecessary space. The changes I made so it will work with access are:

Sub Agency_Detail(strText As String)

(I put the strText As String into the Excel macro)
and

Range("A3") = strText

(I replaced the text that was normally in cell A3 with this code)

Sub Agency_Detail(strText As String)
'
' Agency_Detail Macro
' Macro recorded 10/14/2003 by T
'

'
   'macro deleted here

    Range("A1").Select
    ActiveCell.FormulaR1C1 = "SOUTH JERSEY HOSPITAL"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "AGENCY DETAIL REPORT"
    
    Range("A3") = strText
    
    
   'most of the macro was deleted here
   
    Range("A3").Select
   
    End With
    
End Sub

 

[smashclash]

Am I leaving something out of the Excel macros or Access modules? I deleted the "strText As String" from the code and got the whole thing to run but it doesn't insert the text I put in the textbox in the access form.

Sub Agency_Detail(strText As String)

 

[bat17]

I think that the promblem lies in the way you are calling the macro.
insted of
appXL.Run ("MACROS.xls!Agency_Detail.Agency_Detail")

use

appXL.Run ("MACROS.xls!Agency_Detail"), (strParameter)

Bit of a job to work code with out having someting to test it on

HTH

Peter