jpelldev
asked on
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.