Running Macros w/vba

  • Thread starter Thread starter alien2_51
  • Start date Start date
I'm populating data inside an Excel spreadsheet from within a DTS package
once the data is there I need run a Macro whcih outputs the data contained
in each worksheet to an XML file which I then need to validate against an
XSD... I never have the file open in the Excel UI, so I'm asking how to run
a Macro using VBA and automation.. Here's a sample of one of my
ActiveXScripts in my DTS package... I'll need to access the macro in code...

Function Main()
Dim lobjFileObject
Dim lobjFolder
Dim lobjFiles
Dim e_app
Dim e_wbook
Dim e_wksheet
Dim ColCounter

Set lobjFileObject = CreateObject("Scripting.FileSystemObject")
Set lobjFolder =
lobjFileObject.GetFolder(DTSGlobalVariables("TempateUNC").Value &
"Working\")
Set lobjFiles = lobjFolder.Files

Set e_app = CREATEOBJECT("Excel.Application")

'clean out working directory
For Each File In lobjFiles
Set e_wbook = e_app.Workbooks.Open(DTSGlobalVariables("TempateUNC").Value &
"Working\" & File.Name)
Set e_wksheet = e_wbook.Worksheets(1)
e_wksheet.RANGE("B3:B3").Value = DTSGlobalVariables("ReportQuarter").Value
e_wksheet.RANGE("B4:B4").Value = DTSGlobalVariables("ReportYear").Value
e_wksheet.RANGE("B7:B7").Value = DatePart("yyyy", now) & "-" & GetMonth &
"-" & GetDay
e_wbook.Save
e_wbook.Close
Next

e_app.Quit

'destroy object references
Set lobjFileObject = Nothing
Set lobjFolder = Nothing
Set lobjFiles = Nothing
Set e_wbook = Nothing
Set e_app = Nothing
Main = DTSTaskExecResult_Success
End Function
 
OK I found this in the online docs...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/xlmthrun.asp
Run Method


Syntax 1: Runs a macro or calls a function. This can be used to run a macro
written in Visual Basic or the Microsoft Excel 4.0 macro language, or to run
a function in a DLL or XLL.

Syntax 2: Runs the Microsoft Excel 4.0 macro at this location. The range
must be on a macro sheet.

Syntax 1

expression.Run(Macro, Arg1, Arg2, ...)

Syntax 2

expression.Run(Arg1, Arg2, ...)

expression Optional for Application, required for Range. An expression
that returns the application that contains the macro, or a range on a macro
sheet that contains a Microsoft Excel 4.0 macro.

Macro Required Variant for Syntax 1 (not used with Syntax 2). The macro to
run. This can be either a string with the macro name, a Range object
indicating where the function is, or a register ID for a registered DLL
(XLL) function. If a string is used, the string will be evaluated in the
context of the active sheet.

Arg1, Arg2, ... Optional Variant. The arguments that should be passed to
the function.

Remarks

You cannot use named arguments with this method. Arguments must be passed by
position.

The Run method returns whatever the called macro returns. Objects passed as
arguments to the macro are converted to values (by applying the Value
property to the object). This means that you cannot pass objects to macros
by using the Run method.



Tried it it says it can't find the macro... I know it's there... In the
documentation it says that if a string is used, (is what I'm using) then the
string will be evaluated in the context of the active sheet... The macro
isn't on the sheet it's in the workbook...

I'm confused....

Please help...



TIA,

Dan
 
Hi

First, the macro must be "Public":

Public Function TestFunc(SomeArg As String) As String
TestFunc = "Test: " & SomeArg
End Function

I think "Public" is the default, but better safe than sorry ...

Second, from your example, write something like:

e_app.Run("TestFunc", "Some argument")

Now, if you know the exact name of the workbook (e.g. "myworkbook.xls"), you
might qualify the above with:

e_app.Run("myworkbook.xls!TestFunc", "Some argument")

This should do the trick ... at least, it works on MY machine ;-)

BTW, it is not true, that you can't pass objects as arguments through Run
.... I've done it, and it works, at least on Excel XP. As long as the object
in question originated from within Excel, it doesn't seem to mind.

Regards
Jesper
 
Jesper,

Thank you for your reply...


Jesper V. Andersen said:
Hi

First, the macro must be "Public":
Click to expand...

This may be the problem, the workbook is a template we're using for TREAD
reporting we recieved from NHTSA. It's password protected so I can't open
the VBA editor... So I can't even tell if it's public or not... I can tell
you that when I try and run the macro using the convention you describe it
thorws an error saying it cannot find the macro... I think I'll have to
contact NHTSA...

Dan
 
Back
Top