• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5798
  • Last Modified:

Running Excel macros using an SSIS package?

I currently have a macro that opens a CSV file, manipulates the colums, add totals etc then saves it under a new file name.  I have to open Excel then run the macro manually.  I would like to use an SSIS package to perform this process to eliminate the manual intervention needed so it can be scheduled to run automatically.
Sub Act_Rev()
'
' Act_Rev Macro
' Macro recorded 03/13/2006 by John Pelletier
'
 
'
    Workbooks.Open Filename:="\\midpboolap02\emis_ftp\HR_FTP_in\act_rev.csv"
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight
    Columns("D:D").Select
    Selection.Copy
    Columns("E:E").Select
    ActiveSheet.Paste
    Range("E2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""00"")"
    Selection.Copy
    Range("E3").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("E:E").Select
    Selection.Copy
    Columns("D:D").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("M:M").Select
    Selection.Copy
    Columns("N:N").Select
    ActiveSheet.Paste
    Columns("O:O").Select
    ActiveSheet.Paste
    Range("N2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF((RC[-1]-(RC[-9]+RC[-8]+RC[-7]-RC[-5]-RC[-4]-RC[-3]))>0,((RC[-1]-(RC[-9]+RC[-8]+RC[-7]-RC[-5]-RC[-4]-RC[-3]))),0)"
    Selection.Copy
    Range("N3").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("N:N").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("O2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF((RC[-2]-(RC[-10]+RC[-9]+RC[-8]-RC[-6]-RC[-5]-RC[-4]))<0,((RC[-2]-(RC[-10]+RC[-9]+RC[-8]-RC[-6]-RC[-5]-RC[-4]))*-1),0)"
    Range("O2").Select
    Selection.Copy
    Range("O3").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("O:O").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("H:H").Select
    Selection.Insert Shift:=xlToRight
    Range("G2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[1]+RC[8]"
    Selection.Copy
    Range("G3").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("G:G").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("H:H").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("K:K").Select
    Selection.Copy
    Columns("L:L").Select
    Selection.Insert Shift:=xlToRight
    Range("K2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[1]+RC[5]"
    Selection.Copy
    Range("K3").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("K:K").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("L:L").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("N:O").Select
    Selection.Delete Shift:=xlToLeft
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
    Range("H2").Select
    Selection.Copy
    Range("H3").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("H:H").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("L2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-3]+RC[-2]+RC[-1]"
    Selection.Copy
    Range("L3").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("L:L").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("E:M").Select
    Selection.NumberFormat = "0.00"
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:="\\midpboolap02\emis_ftp\HR_FTP_in\ActXRev.csv", FileFormat:=xlCSV, _
        CreateBackup:=False
End Sub

Open in new window

0
jpelldev
Asked:
jpelldev
1 Solution
 
RobSampsonCommented:
Hi, you should not need to have to run the macro manually.  Here's a sample VBS file that opens an Excel file and runs a macro in it, then closes the file.  If you can get your SSIS package to run this VBS, it might work.

'===================
strExcelFile = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "test.xls"
strMacroName = "run_macro"
Set objExcel = CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Open(strExcelFile)
objExcel.Visible = True
objWB.Application.Run strMacroName
objWB.Close False
objExcel.Quit
'===================

Regards,

Rob.
0
 
Robberbaron (robr)Commented:
but the SSIS will run on the SQLserver box normally.

And Excel is unlikely to be unstalled there.

You could run RobS macro from a workstation automatically.  I have used a Reception PC startup script as I'm usually confident of that PC being manned each day.   Long holiday breaks can be a problem.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now