Function Main()
Dim xlApp


Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Open DTSGlobalVariables
("gvTemplateMacro").Value
xlApp.Workbooks.Open DTSGlobalVariables
("gvTemplateFile").Value
xlApp.Run ("Quail")
xlApp.ActiveWorkbook.Close True
xlApp.Quit

Set xlApp = Nothing
Main = DTSTaskExecResult_Success

End Function

When this code is run it errors saying Quail cannot be
found.
Quail is on gvTemplateMacro and I trying to open
gvTemplateFile
and apply the macro.

Is there a way to do this?

Not tested but what happens if you create 2 Excel.Application instances
instead of just the one ?

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"BJ" <bjrichard21 (AT) hotmail (DOT) com> wrote

Thanks Allen

The following code works!
Dim xlApp


Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Open DTSGlobalVariables
("gvTemplateMacro").Value

xlApp.Workbooks.Open DTSGlobalVariables("gvBGC").Value
xlApp.Run ("MACRO.xls!Module1.MACRO")
xlApp.ActiveWorkbook.Save

xlApp.Workbooks.Open DTSGlobalVariables("gvHGC").Value
xlApp.Run ("MACRO.xls!Module1.MACRO")
xlApp.ActiveWorkbook.Save

xlApp.Workbooks.Open DTSGlobalVariables("gvOVGC").Value
xlApp.Run ("MACRO.xls!Module1.MACRO")
xlApp.ActiveWorkbook.Save

xlApp.Workbooks.Open DTSGlobalVariables("gvBPGC").Value
xlApp.Run ("MACRO.xls!Module1.MACRO")
xlApp.ActiveWorkbook.Save

xlApp.Workbooks.Open DTSGlobalVariables("gvMDLLC").Value
xlApp.Run ("MACRO.xls!Module1.MACRO")
xlApp.ActiveWorkbook.Save

xlApp.ActiveWorkbook.Close True

xlApp.Quit

Set xlApp = Nothing
BJ