May 7, 2009 at 12:56 pm
I need to export some data to Excel and then run 1 or more Excel macros. Export to Excel is easy enough to accomplish and there are different options. Getting the Excel Macro to run is another story. I’ve tried VB Script in SSIS, SQL-DMO (understanding it goes away), etc but I can get seem to get anything to work. Does any have an example they can post or reference.
The existing structure included one “master” Excel file (*.xla) with a number of different macros for different data sets. The master file is copied, populated with data and the needed macros for that data set are run. I’ve thought of populating the data from SQL Server and then running the macro when the file is opened but I would have to break up the master in to data set specific files since each has different macros.
Version... SQL 2005, Excel 2003
May 7, 2009 at 1:13 pm
CLR can do OLE automation to access the Excel object model. You can fire off a macro from there.
No, I haven't done this. I have automated Excel before, but not from CLR. But it can definitely be done. You might start from that angle and see if you can get it going that way.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 27, 2010 at 6:45 am
You can try the below mentioned code through SSIS package with Active X Script task
Function Main()
Dim Excel_app
Dim ExcelCode
Set Excel_app = CreateObject("Excel.Application")
set ExcelCode = Excel_app.workbooks.open("\\mrtb03.dr.snfores.com\tb\prodSCD\XXX\INFXXSUS\XXX_CVR_CS\Template\VBACode.xls")
Excel_app.Run "Start"
ExcelCode.Close
Excel_app.Quit
Set Excel_app = Nothing
Main = DTSTaskExecResult_Success
End Function
January 13, 2012 at 12:50 am
Hey SSCrazy, your coding is working. Thank you very much, you made my day:-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply