New to stackoverflow and hoping I can get some help. I'm getting this error:

TITLE: Package Validation Error

Package Validation Error


ADDITIONAL INFORMATION:

Error at CSV_File_Reformat [ActiveX Script Task]: User script threw an exception: "Error Code: 0
Error Source= Microsoft VBScript compilation error
Error Description: Expected end of statement
Error on Line 12
".

Error at CSV_File_Reformat: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)


BUTTONS:

OK

This code works when running it in VBA, but when I try it as VBScript in an SSIS package it throws this error.

Here is the code:

Function Weekly_Upload_Reformat_IPS_File()

Dim xl_app
Dim wb
Dim lastRow


Set xl_app = CreateObject("Excel.Application")
Set wb = Workbooks.Open("\\fs1-pdc-plpgh\Transportation - DW\IPS\TFS_FULL_DATA_WEEKLY_*")

For Each wb In Application.Workbooks
   If wb.Name Like "TFS_FULL_DATA_WEEKLY_*" Then wb.Activate
Next wb

Application.DisplayAlerts = False

Rows("1:1").Select

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Range("A1").FormulaR1C1 = "Detail"
Range("B1").FormulaR1C1 = "Pro Number"
Range("C1").FormulaR1C1 = "Primary Bill of Lading"
Range("D1").FormulaR1C1 = "PO Number"
Range("E1").FormulaR1C1 = "Invoice Number"
Range("F1").FormulaR1C1 = "Date-Received at IPS"
Range("G1").FormulaR1C1 = "Date-Shipment"
Range("H1").FormulaR1C1 = "Date-Invoice"
Range("I1").FormulaR1C1 = "Date-Delivery Date"
Range("J1").FormulaR1C1 = "Delivery Time"
Range("K1").FormulaR1C1 = "Carrier-SCAC Code"
Range("L1").FormulaR1C1 = "Carrier-Name"
Range("M1").FormulaR1C1 = "Carrier-Major Mode"
Range("N1").FormulaR1C1 = "Carrier-Account Number"
Range("O1").FormulaR1C1 = "Received From"
Range("P1").FormulaR1C1 = "EDI/Paper (E/P)"
Range("Q1").FormulaR1C1 = "Invoice Type (Org/BD/Supp)"
Range("R1").FormulaR1C1 = "Accessorial-Description"
Range("S1").FormulaR1C1 = "Accessorial-Net"
Range("T1").FormulaR1C1 = "Shipper Name"
Range("U1").FormulaR1C1 = "Shipper Address"
Range("V1").FormulaR1C1 = "Shipper City"
Range("W1").FormulaR1C1 = "Shipper State"
Range("X1").FormulaR1C1 = "Shipper Country"
Range("Y1").FormulaR1C1 = "Shipper Zip"
Range("Z1").FormulaR1C1 = "Consignee Contact"
Range("AA1").FormulaR1C1 = "Consignee Name"
Range("AB1").FormulaR1C1 = "Consignee Address"
Range("AC1").FormulaR1C1 = "Consignee City"
Range("AD1").FormulaR1C1 = "Consignee State"
Range("AE1").FormulaR1C1 = "Consignee Country"
Range("AF1").FormulaR1C1 = "Consignee Zip"
Range("AG1").FormulaR1C1 = "Invoice Status (Pay/Rej/Hold)"
Range("AH1").FormulaR1C1 = "Currency"
Range("AI1").FormulaR1C1 = "Status/Week Ending Date"
Range("AJ1").FormulaR1C1 = "Check Number"
Range("AK1").FormulaR1C1 = "Process Loc (DataEntry/Audit)"
Range("AL1").FormulaR1C1 = "Payment Type (Payment/Credit/Debit)"
Range("AM1").FormulaR1C1 = "Terms"
Range("AN1").FormulaR1C1 = "Dim Factor"
Range("AO1").FormulaR1C1 = "Weight-Actual"
Range("AP1").FormulaR1C1 = "Weight-As Wgt"
Range("AQ1").FormulaR1C1 = "WGT Measure:pounds/kilos"
Range("AR1").FormulaR1C1 = "Pieces"
Range("AS1").FormulaR1C1 = "Number of Containers"
Range("AT1").FormulaR1C1 = "International Code"
Range("AU1").FormulaR1C1 = "Delivery Term (Door-Door/Airp-Airp/Etc)"
Range("AV1").FormulaR1C1 = "Service Level (Next Day/2nd Day/Etc)"
Range("AW1").FormulaR1C1 = "Client Mode"
Range("AX1").FormulaR1C1 = "Index Number"
Range("AY1").FormulaR1C1 = "Freight Class"

Range("B:E, AJ:AJ, AX:AX").Select
Selection.NumberFormat = "0"

Cells.Replace What:=",", Replacement:="", LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="'", Replacement:="", LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="""", Replacement:="", LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Columns("W:W").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("W1").FormulaR1C1 = "Shipper City"
Range("W2").FormulaR1C1 = "=TRIM(RC[-1])"
lastRow = Range("V" & Rows.Count).End(xlUp).Row
Range("W2").AutoFill Destination:=Range("W2:W" & lastRow)
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Columns("V:V").Delete

Columns("AD:AD").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("AD1").FormulaR1C1 = "Consignee City"
Range("AD2").FormulaR1C1 = "=TRIM(RC[-1])"
lastRow = Range("AC" & Rows.Count).End(xlUp).Row
Range("AD2").AutoFill Destination:=Range("AD2:AD" & lastRow)
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Columns("AC:AC").Delete

Columns("AZ:AZ").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("AZ1").FormulaR1C1 = "Freight Class"
Range("AZ2").FormulaR1C1 = "=TRIM(RC[-1])"
lastRow = Range("AY" & Rows.Count).End(xlUp).Row
Range("AZ2").AutoFill Destination:=Range("AZ2:AZ" & lastRow)
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Columns("AY:AY").Delete

ActiveSheet.Range("A:AY").AutoFilter Field:=9, Criteria1:= _
    "0001-01-01"
Range("I2:I" & lastRow).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.ClearContents
ActiveSheet.Range("A:AY").AutoFilter Field:=9

ActiveWorkbook.SaveAs Filename:="\\fs1-pdc-plpgh\Transportation - DW\IPS\Staging\IPS Upload.csv", _
    FileFormat:=xlCSV, CreateBackup:=False

End Function

1 Answers

0
reportgunner On Best Solutions

VBScript is not interchangeable with VBA. VBScript is a subset of VBA, so you can't simply copy paste an excel macro to a .vbs file and run it.

Otherwise your error "Error Description: Expected end of statement" means that you are missing quotes on a string or you're missing a one part of an equation (for example if you write my_var = without anything on the other side of the =)

Also I think that there is no Like keyword in VBScript, use this instead Left(wb.Name, Len("TFS_FULL_DATA_WEEKLY_")) = "TFS_FULL_DATA_WEEKLY_"

I recommend that you put the VBA code in the excel file and then use VBScript to open the file and trigger the macro