Option Explicit
Sub File_Uploads_1()
Dim i As Long
Dim lastRowIndex, last_row, x_row, target_last_row, nextrow, lastrow As Long
Dim PO_NUM_collection, ITEM_NUM_collection, TRACKING_collection, Serials_collection, MTM_NUM_collection, CARRIER_collection, ARTICLE_NUM_collection, CONV_DATE_collection, CONV_QTY_collection, CONV_NUM_collection As String
Dim po_num_col, file_name_col, first_Row, item_num_col, TRACKING_col, article_num_col, conv_date_col, conv_quantity_col, target_column As Integer
Dim conv_number_col, MTM_num_col, Carrier_col, serials_num_col, error_rpts, Last_Col, Header_row, first_header, Header_col As Integer
Dim data_type, filename, folderpath, header_value As String
Dim wb, hopper As Workbook
Dim Hopper_data, target_data, source_sheet As Worksheet
Dim wb_name, One_off_WBs, ext, destinationpath, true_name, hopperpath, hoppername As String
Dim Last_Err_Rpt, firstrow As Integer
Dim fso As Object
Dim rLastCell As Range
Dim strFolder As String: strFolder = "I:\ASM\All\CRT\Reporting\In Consolidation Process\Reports\"
Dim strFileSpec As String: strFileSpec = strFolder & "*.*"
Dim strFileName As String
Dim Target_col1, Target_col2, Target_col3, Target_col4, Target_col5, Target_col6, Target_col7, Target_col8, Target_col9, Target_col10 As Integer
Dim Source_col1, Source_col2, Source_col3, Source_col4, Source_col5, Source_col6, Source_col7, Source_col8, Source_col9, Source_col10 As Integer
Dim sourcebook As Workbook
Dim batch_num, Batch_col, error_count As Integer
Dim DCA, DCXX, order_num_col, Cust_po_num_col As Integer
Dim DCB, xrow As Long
Dim strExt As String
Dim batch_audit_Col, batch_audit_Col_end As Integer
Dim validation_counter, x As Integer
batch_num = 1
'checks to see if this batch has already been run on a given day. If so, the option is given to cancel the batch run.
If Dash.Range("D2").Value = Date Then
If MsgBox("Run Batch?", vbYesNo + vbQuestion) = vbNo Then
MsgBox "Cancelling Batch."
End
Else
MsgBox "Running Batch."
End If
Else
MsgBox "Running Batch " & batch_num
End If
Application.DisplayAlerts = False
Application.CalculateBeforeSave = False
Application.Calculation = xlManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Dash.Range("I3:J100").ClearContents
Set fso = CreateObject("Scripting.FileSystemObject")
'sets the file path for key files
'opens "the hopper".
folderpath = "I:\ASM\All\CRT\Reporting\In Consolidation Process\Reports\"
hopperpath = "I:\ASM\All\CRT\Reporting\In Consolidation Process\Master Automation\"
hoppername = "Automated Report Data Hopper.xlsx"
Set hopper = Workbooks.Open(hopperpath & hoppername)
Set Hopper_data = Workbooks(hoppername).Worksheets("Hopper")
If MsgBox("Clear Hopper Data? If you are running batch one, the answer is generally yes.", vbYesNo + vbQuestion) = vbYes Then
With Hopper_data
If .FilterMode = True Then .ShowAllData
DCA = Application.Match("PO_Nr", .Rows("1:1"), 0)
DCXX = Application.Match("Serial Numbers", .Rows("1:1"), 0)
DCB = .Columns(Application.Match("PO_Nr", .Rows("1:1"), 0)).Find("*", , , , xlRows, xlPrevious).Row
.Range(.Cells(2, DCA), .Cells(DCB + 1, DCXX)).ClearContents
End With
End If
'Builds folder directory with current date in report archive folder location seen below
If Dir("I:\ASM\All\CRT\Reporting\In Consolidation Process\Master Automation\Dated Report Archive\" & Format(Date, "yyyy-mm-dd"), vbDirectory) = "" Then
MkDir ("I:\ASM\All\CRT\Reporting\In Consolidation Process\Master Automation\Dated Report Archive\" & Format(Date, "yyyy-mm-dd") & "\")
End If
destinationpath = "I:\ASM\All\CRT\Reporting\In Consolidation Process\Master Automation\Dated Report Archive\" & Format(Date, "yyyy-mm-dd") & "\"
'sets data ranges inside "the hopper" file
With Hopper_data
Target_col1 = Application.Match("PO_Nr", .Rows("1:1"), 0)
Target_col2 = Application.Match("ItemNr", .Rows("1:1"), 0)
Target_col3 = Application.Match("ArticleNr", .Rows("1:1"), 0)
Target_col4 = Application.Match("ConvDate", .Rows("1:1"), 0)
Target_col5 = Application.Match("ConvQuantity", .Rows("1:1"), 0)
Target_col6 = Application.Match("ConvNumber", .Rows("1:1"), 0)
Target_col7 = Application.Match("Manufpartnum", .Rows("1:1"), 0)
Target_col8 = Application.Match("Carrier", .Rows("1:1"), 0)
Target_col9 = Application.Match("Trackinginfo", .Rows("1:1"), 0)
Target_col10 = Application.Match("Serial Numbers", .Rows("1:1"), 0)
End With
strFileName = Dir(strFileSpec)
With Hopper_data
'big loop that processes the data from bout a dozen files and compile appends this data into "the hopper".
Do While strFileName <> ""
loopsetter:
On Error GoTo nextsheet:
Source_col10 = ""
Source_col7 = ""
strExt = CreateObject("Scripting.FileSystemObject").GetExtensionName(strFileName)
If strExt = "xls" Or strExt = "XLS" Or strExt = "xlsx" Or strExt = "XLSX" Or strExt = "csv" Or strExt = "xlsb" Or strExt = "xlsm" Or strExt = "CSV" Or strExt = "XLSB" Or strExt = "XLSM" Then
If InStr(1, strFileName, "Account Activity - TECH Data") > 0 Then
Set wb = Workbooks.Open(folderpath & strFileName)
Set source_sheet = Workbooks(strFileName).Worksheets("TECH Data Activity - DO NOT REP")
nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
firstrow = Application.Match("Customer Order#", source_sheet.Columns("B"), 0)
lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
If lastrow > firstrow Then
Source_col1 = Application.Match("Customer Order#", source_sheet.Rows(firstrow), 0)
Source_col2 = Application.Match("Position #", source_sheet.Rows(firstrow), 0)
Source_col4 = Application.Match("Date", source_sheet.Rows(firstrow), 0)
Source_col5 = Application.Match("Ordered", source_sheet.Rows(firstrow), 0)
Source_col6 = Application.Match("3M Order #", source_sheet.Rows(firstrow), 0)
Source_col7 = Application.Match("Item", source_sheet.Rows(firstrow), 0)
Source_col8 = Application.Match("Carrier", source_sheet.Rows(firstrow), 0)
Source_col9 = Application.Match("Tracking #", source_sheet.Rows(firstrow), 0)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col2), source_sheet.Cells(lastrow, Source_col2)).Copy Destination:=.Cells(nextrow, Target_col2)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col9), source_sheet.Cells(lastrow, Source_col8)).Copy Destination:=.Cells(nextrow, Target_col8)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col8), source_sheet.Cells(lastrow, Source_col9)).Copy Destination:=.Cells(nextrow, Target_col9)
End If
ElseIf InStr(1, strFileName, "Tracking - Tech Data - Tracking Tech Data") > 0 Then
Set wb = Workbooks.Open(folderpath & strFileName)
Set source_sheet = Workbooks(strFileName).Worksheets("Sheet1")
nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
firstrow = Application.Match("Order #", source_sheet.Columns("B"), 0)
lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
If lastrow > firstrow Then
Source_col1 = Application.Match("Cust_PO", source_sheet.Rows(firstrow), 0)
Source_col2 = Application.Match("Pos", source_sheet.Rows(firstrow), 0)
Source_col3 = Application.Match("Cust. Item #", source_sheet.Rows(firstrow), 0)
Source_col4 = Application.Match("Dt. Shipped", source_sheet.Rows(firstrow), 0)
Source_col5 = Application.Match("Qty", source_sheet.Rows(firstrow), 0)
Source_col7 = Application.Match("ItemNum", source_sheet.Rows(firstrow), 0)
Source_col8 = Application.Match("Carrier", source_sheet.Rows(firstrow), 0)
Source_col9 = Application.Match("Tracking ", source_sheet.Rows(firstrow), 0)
source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col2), source_sheet.Cells(lastrow, Source_col2)).Copy Destination:=.Cells(nextrow, Target_col2)
source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col3), source_sheet.Cells(lastrow, Source_col3)).Copy Destination:=.Cells(nextrow, Target_col3)
source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col8), source_sheet.Cells(lastrow, Source_col8)).Copy Destination:=.Cells(nextrow, Target_col8)
source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col9), source_sheet.Cells(lastrow, Source_col9)).Copy Destination:=.Cells(nextrow, Target_col9)
End If
ElseIf InStr(1, strFileName, "Lenovo Sea Shipments - CustomReport") > 0 Then
Set wb = Workbooks.Open(folderpath & strFileName)
Set source_sheet = Workbooks(strFileName).Worksheets("Order list_1")
nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
firstrow = Application.Match("Sales Order Line Number", source_sheet.Columns("B"), 0)
lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
If lastrow > firstrow Then
Source_col1 = Application.Match("Customer Purchase Order Number", source_sheet.Rows(firstrow), 0)
Source_col4 = Application.Match("Firm Ship Date", source_sheet.Rows(firstrow), 0)
Source_col5 = Application.Match("Order Quantity", source_sheet.Rows(firstrow), 0)
Source_col6 = Application.Match("Sales Order Number", source_sheet.Rows(firstrow), 0)
Source_col7 = Application.Match("Product ID", source_sheet.Rows(firstrow), 0)
Source_col8 = Application.Match("Carrier Name", source_sheet.Rows(firstrow), 0)
Source_col9 = Application.Match("Carrier Tracking Number", source_sheet.Rows(firstrow), 0)
source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col8), source_sheet.Cells(lastrow, Source_col8)).Copy Destination:=.Cells(nextrow, Target_col8)
source_sheet.Range(source_sheet.Cells(firstrow + 2, Source_col9), source_sheet.Cells(lastrow, Source_col9)).Copy Destination:=.Cells(nextrow, Target_col9)
End If
ElseIf InStr(1, strFileName, "Tech Data Open Order Book") > 0 Then
Set wb = Workbooks.Open(folderpath & strFileName)
Set source_sheet = Workbooks(strFileName).Worksheets("Open Order Book")
nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
firstrow = Application.Match("PARTNER PURCHASE ORDER", source_sheet.Columns("B"), 0)
lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
If lastrow > firstrow Then
Source_col1 = Application.Match("PARTNER PURCHASE ORDER", source_sheet.Rows(firstrow), 0)
Source_col2 = Application.Match("ITEM LINE", source_sheet.Rows(firstrow), 0)
Source_col4 = Application.Match("ESTIMATED SHIP DATE", source_sheet.Rows(firstrow), 0)
Source_col5 = Application.Match("QUANTITY", source_sheet.Rows(firstrow), 0)
Source_col6 = Application.Match("HP SALES ORDER", source_sheet.Rows(firstrow), 0)
Source_col7 = Application.Match("SKU", source_sheet.Rows(firstrow), 0)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col2), source_sheet.Cells(lastrow, Source_col2)).Copy Destination:=.Cells(nextrow, Target_col2)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
End If
ElseIf InStr(1, strFileName, "Tech Data US Ship Status US") > 0 Then
Set wb = Workbooks.Open(folderpath & strFileName)
Set source_sheet = Workbooks(strFileName).Worksheets(1)
nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
firstrow = Application.Match("Document N", source_sheet.Columns("B"), 0)
lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
If lastrow > firstrow Then
Source_col1 = Application.Match("Purchase O", source_sheet.Rows(firstrow), 0)
Source_col2 = Application.Match("Item Numbe", source_sheet.Rows(firstrow), 0)
Source_col3 = Application.Match("Customer M", source_sheet.Rows(firstrow), 0)
Source_col4 = Application.Match("Bill.date", source_sheet.Rows(firstrow), 0)
Source_col5 = Application.Match("ConfirmQty", source_sheet.Rows(firstrow), 0)
Source_col6 = Application.Match("Document N", source_sheet.Rows(firstrow), 0)
Source_col7 = Application.Match("Material", source_sheet.Rows(firstrow), 0)
Source_col8 = Application.Match("Forwarding Agent Name", source_sheet.Rows(firstrow), 0)
Source_col9 = Application.Match("Tracking Number/PRO", source_sheet.Rows(firstrow), 0)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col2), source_sheet.Cells(lastrow, Source_col2)).Copy Destination:=.Cells(nextrow, Target_col2)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col3), source_sheet.Cells(lastrow, Source_col3)).Copy Destination:=.Cells(nextrow, Target_col3)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col8), source_sheet.Cells(lastrow, Source_col8)).Copy Destination:=.Cells(nextrow, Target_col8)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col9), source_sheet.Cells(lastrow, Source_col9)).Copy Destination:=.Cells(nextrow, Target_col9)
End If
ElseIf InStr(1, strFileName, "TechData Weekly Reports for") > 0 Then
Set wb = Workbooks.Open(folderpath & strFileName)
Set source_sheet = Workbooks(strFileName).Worksheets("qryOOR_Partner_MasterReport")
nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
firstrow = Application.Match("BillingPONumber", source_sheet.Columns("B"), 0)
lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
If lastrow > firstrow Then
Source_col1 = Application.Match("BillingPONumber", source_sheet.Rows(firstrow), 0)
Source_col4 = Application.Match("RevisedShipWeek", source_sheet.Rows(firstrow), 0)
Source_col5 = Application.Match("POQuantity", source_sheet.Rows(firstrow), 0)
Source_col6 = Application.Match("HowardOrder", source_sheet.Rows(firstrow), 0)
Source_col7 = Application.Match("POSKU", source_sheet.Rows(firstrow), 0)
Source_col8 = Application.Match("Carrier", source_sheet.Rows(firstrow), 0)
Source_col9 = Application.Match("TrackingNumber", source_sheet.Rows(firstrow), 0)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col8), source_sheet.Cells(lastrow, Source_col8)).Copy Destination:=.Cells(nextrow, Target_col8)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col9), source_sheet.Cells(lastrow, Source_col9)).Copy Destination:=.Cells(nextrow, Target_col9)
End If
ElseIf InStr(1, strFileName, "Tech Data Shipment report") > 0 Then
Set wb = Workbooks.Open(folderpath & strFileName)
Set source_sheet = Workbooks(strFileName).Worksheets("qryTechData_ship")
nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
firstrow = Application.Match("PO.", source_sheet.Columns("B"), 0)
lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
If lastrow > firstrow Then
Source_col1 = Application.Match("PO.", source_sheet.Rows(firstrow), 0)
Source_col4 = Application.Match("Ship Date", source_sheet.Rows(firstrow), 0)
Source_col5 = Application.Match("Shipment.", source_sheet.Rows(firstrow), 0)
Source_col6 = Application.Match("Order.", source_sheet.Rows(firstrow), 0)
Source_col7 = Application.Match("Part.", source_sheet.Rows(firstrow), 0)
Source_col10 = Application.Match("UNIT_SERIAL_NBR", source_sheet.Rows(firstrow), 0)
Source_col8 = Application.Match("Carrier", source_sheet.Rows(firstrow), 0)
Source_col9 = Application.Match("Pro-Number-1", source_sheet.Rows(firstrow), 0)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col8), source_sheet.Cells(lastrow, Source_col8)).Copy Destination:=.Cells(nextrow, Target_col8)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col9), source_sheet.Cells(lastrow, Source_col9)).Copy Destination:=.Cells(nextrow, Target_col9)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col10), source_sheet.Cells(lastrow, Source_col10)).Copy Destination:=.Cells(nextrow, Target_col10)
End If
ElseIf InStr(1, strFileName, "Buffalo Estimated Ship Dates") > 0 Then
Set wb = Workbooks.Open(folderpath & strFileName)
Set source_sheet = Workbooks(strFileName).Worksheets("Sheet1")
nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
firstrow = Application.Match("Customer PO Number", source_sheet.Columns("B"), 0)
lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
If lastrow > firstrow Then
Source_col1 = Application.Match("Customer PO Number", source_sheet.Rows(firstrow), 0)
Source_col4 = Application.Match("Estimated Shipment Week", source_sheet.Rows(firstrow), 0)
Source_col5 = Application.Match("Qty", source_sheet.Rows(firstrow), 0)
Source_col7 = Application.Match("Item", source_sheet.Rows(firstrow), 0)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
End If
ElseIf InStr(1, strFileName, "TechDataUSA_Shipment") > 0 Then
Set wb = Workbooks.Open(folderpath & strFileName)
Set source_sheet = Workbooks(strFileName).Worksheets(1)
nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
firstrow = Application.Match("cust_no", source_sheet.Columns("B"), 0)
lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
If lastrow > firstrow Then
Source_col1 = Application.Match("po_no", source_sheet.Rows(firstrow), 0)
Source_col4 = Application.Match("ship_date", source_sheet.Rows(firstrow), 0)
Source_col5 = Application.Match("ship_qty", source_sheet.Rows(firstrow), 0)
Source_col6 = Application.Match("so_no", source_sheet.Rows(firstrow), 0)
Source_col7 = Application.Match("part_no", source_sheet.Rows(firstrow), 0)
Source_col8 = Application.Match("carrier", source_sheet.Rows(firstrow), 0)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col8), source_sheet.Cells(lastrow, Source_col8)).Copy Destination:=.Cells(nextrow, Target_col8)
End If
ElseIf InStr(1, strFileName, "TechDataUSA_BackLog") > 0 Then
Set wb = Workbooks.Open(folderpath & strFileName)
Set source_sheet = Workbooks(strFileName).Worksheets(1)
nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
firstrow = Application.Match("cust_name", source_sheet.Columns("B"), 0)
lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
If lastrow > firstrow Then
Source_col1 = Application.Match("po_no", source_sheet.Rows(firstrow), 0)
Source_col4 = Application.Match("etd", source_sheet.Rows(firstrow), 0)
Source_col5 = Application.Match("on_order", source_sheet.Rows(firstrow), 0)
Source_col6 = Application.Match("so_no", source_sheet.Rows(firstrow), 0)
Source_col7 = Application.Match("part_no", source_sheet.Rows(firstrow), 0)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
End If
ElseIf InStr(1, strFileName, "NEC DISPLAY") > 0 Then
Set wb = Workbooks.Open(folderpath & strFileName)
Set source_sheet = Workbooks(strFileName).Worksheets(1)
nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
firstrow = Application.Match("Customer Name", source_sheet.Columns("B"), 0)
lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
If lastrow > firstrow Then
Source_col1 = Application.Match("P.O. No.", source_sheet.Rows(firstrow), 0)
Source_col2 = Application.Match("Line #", source_sheet.Rows(firstrow), 0)
Source_col4 = Application.Match("Sched Dt", source_sheet.Rows(firstrow), 0)
Source_col5 = Application.Match("Quantity", source_sheet.Rows(firstrow), 0)
Source_col6 = Application.Match("NEC Order", source_sheet.Rows(firstrow), 0)
Source_col7 = Application.Match("Material No.", source_sheet.Rows(firstrow), 0)
Source_col8 = Application.Match("Carrier", source_sheet.Rows(firstrow), 0)
Source_col9 = Application.Match("Bill of Lading", source_sheet.Rows(firstrow), 0)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col2), source_sheet.Cells(lastrow, Source_col2)).Copy Destination:=.Cells(nextrow, Target_col2)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col8), source_sheet.Cells(lastrow, Source_col8)).Copy Destination:=.Cells(nextrow, Target_col8)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col9), source_sheet.Cells(lastrow, Source_col9)).Copy Destination:=.Cells(nextrow, Target_col9)
End If
ElseIf InStr(1, strFileName, "Daily Ship Report - TDUS") > 0 Then
Set wb = Workbooks.Open(folderpath & strFileName)
Set source_sheet = Workbooks(strFileName).Worksheets("Shipping Report")
nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
firstrow = Application.Match("PO#", source_sheet.Columns("B"), 0)
lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
If lastrow > firstrow Then
Source_col1 = Application.Match("PO#", source_sheet.Rows(firstrow), 0)
Source_col3 = Application.Match("SKU", source_sheet.Rows(firstrow), 0)
Source_col4 = Application.Match("SHIP DATE", source_sheet.Rows(firstrow), 0)
Source_col5 = Application.Match("Shipped Qty", source_sheet.Rows(firstrow), 0)
Source_col6 = Application.Match("Order No", source_sheet.Rows(firstrow), 0)
Source_col7 = Application.Match("Belkin Part No", source_sheet.Rows(firstrow), 0)
Source_col8 = Application.Match("Carrier", source_sheet.Rows(firstrow), 0)
Source_col9 = Application.Match("Tracking Number", source_sheet.Rows(firstrow), 0)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col3), source_sheet.Cells(lastrow, Source_col3)).Copy Destination:=.Cells(nextrow, Target_col3)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col8), source_sheet.Cells(lastrow, Source_col8)).Copy Destination:=.Cells(nextrow, Target_col8)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col9), source_sheet.Cells(lastrow, Source_col9)).Copy Destination:=.Cells(nextrow, Target_col9)
End If
ElseIf InStr(1, strFileName, "Daily Shipping Report 3741 TECH DATA CORP as of") > 0 Then
Set wb = Workbooks.Open(folderpath & strFileName)
Set source_sheet = Workbooks(strFileName).Worksheets("Shipping Report")
nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
firstrow = Application.Match("PO#", source_sheet.Columns("B"), 0)
lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
If lastrow > firstrow Then
Source_col1 = Application.Match("PO#", source_sheet.Rows(firstrow), 0)
Source_col3 = Application.Match("SKU", source_sheet.Rows(firstrow), 0)
Source_col4 = Application.Match("SHIP DATE", source_sheet.Rows(firstrow), 0)
Source_col5 = Application.Match("Shipped Qty", source_sheet.Rows(firstrow), 0)
Source_col6 = Application.Match("Order No", source_sheet.Rows(firstrow), 0)
Source_col7 = Application.Match("Belkin Part No", source_sheet.Rows(firstrow), 0)
Source_col8 = Application.Match("Carrier", source_sheet.Rows(firstrow), 0)
Source_col9 = Application.Match("Tracking Number", source_sheet.Rows(firstrow), 0)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col3), source_sheet.Cells(lastrow, Source_col3)).Copy Destination:=.Cells(nextrow, Target_col3)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col8), source_sheet.Cells(lastrow, Source_col8)).Copy Destination:=.Cells(nextrow, Target_col8)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col9), source_sheet.Cells(lastrow, Source_col9)).Copy Destination:=.Cells(nextrow, Target_col9)
End If
ElseIf InStr(1, strFileName, "Garmin Open Orders Report") > 0 Then
Set wb = Workbooks.Open(folderpath & strFileName)
Set source_sheet = Workbooks(strFileName).Worksheets("Sheet1")
source_sheet.Columns("A:J").UnMerge
nextrow = .Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
firstrow = Application.Match("Customer PO Number", source_sheet.Columns("B"), 0)
lastrow = source_sheet.Columns("B").Find("*", , xlValues, , xlRows, xlPrevious).Row
order_num_col = Application.Match("Order Number", source_sheet.Rows(firstrow), 0)
Cust_po_num_col = Application.Match("Customer PO Number", source_sheet.Rows(firstrow), 0)
For xrow = firstrow To lastrow
If source_sheet.Cells(xrow, order_num_col).Value = "" Then
source_sheet.Cells(xrow, order_num_col).Value = source_sheet.Cells(xrow - 1, order_num_col)
End If
Next
For xrow = firstrow To lastrow
If source_sheet.Cells(xrow, Cust_po_num_col).Value = "" Then
source_sheet.Cells(xrow, Cust_po_num_col).Value = source_sheet.Cells(xrow - 1, Cust_po_num_col)
End If
Next
If lastrow > firstrow Then
Source_col1 = Application.Match("Customer PO Number", source_sheet.Rows(firstrow), 0)
Source_col4 = Application.Match("Ship Date", source_sheet.Rows(firstrow), 0)
Source_col5 = Application.Match("Ordered Quantity", source_sheet.Rows(firstrow), 0)
Source_col6 = Application.Match("Order Number", source_sheet.Rows(firstrow), 0)
Source_col7 = Application.Match("Ordered Item", source_sheet.Rows(firstrow), 0)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col1), source_sheet.Cells(lastrow, Source_col1)).Copy Destination:=.Cells(nextrow, Target_col1)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col4), source_sheet.Cells(lastrow, Source_col4)).Copy Destination:=.Cells(nextrow, Target_col4)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col5), source_sheet.Cells(lastrow, Source_col5)).Copy Destination:=.Cells(nextrow, Target_col5)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col6), source_sheet.Cells(lastrow, Source_col6)).Copy Destination:=.Cells(nextrow, Target_col6)
source_sheet.Range(source_sheet.Cells(firstrow + 1, Source_col7), source_sheet.Cells(lastrow, Source_col7)).Copy Destination:=.Cells(nextrow, Target_col7)
End If
End If
End If
'code to ensure that the data is being inputted correct
batch_audit_Col = Application.Match("BATCH1", Dash.Rows("1:1"), 0)
batch_audit_Col_end = Application.Match("X", Dash.Columns(batch_audit_Col - 1), 0)
Dash.Range(Dash.Cells(3, batch_audit_Col), Dash.Cells(batch_audit_Col_end - 1, batch_audit_Col)).ClearContents
'locate the last row of the file in question
lastrow = source_sheet.UsedRange.Rows.Count
validation_counter = 0
For x = 1 To 50
'generate a random variable between the first and last row in the file
randgen_value = WorksheetFunction.RandBetween(firstrow + 1, lastrow)
'select random serial # from the list using the random variable as the column
rand_Value_T1 = source_sheet.Cells(randgen_value, Source_col10).Value
'select random part # from the list using the random variable as the column
rand_Value_T2 = source_sheet.Cells(randgen_value, Source_col7).Value
'from whichever variable is selected, pull back the customer PO
If Source_col10 <> "" Then
'sets of the customer PO match from the source file
Set rand_match = .Range(.Cells(firstrow, Source_col10), .Cells(lastrow, Source_col10))
Set rand_index = .Range(.Cells(firstrow, Source_col1), .Cells(lastrow, Source_col10))
'from whichever variable is selected, pull back the customer PO
source_PO = Application.Index(rand_index, Application.Match(rand_Value_T1, rand_match, 0), Source_col1)
'sets up index match parameters for the hopper file
Set rand_match = .Range(.Cells(firstrow, Target_col1), .Cells(randgen_lastrow, Target_col1))
Set rand_index = .Range(.Cells(firstrow, Target_col1), .Cells(randgen_lastrow, Target_col10))
'sets uses the customer PO previously retrieved to pull back the SN
hopper_sn = Application.Index(rand_index, Application.Match(source_PO, rand_match, 0), Target_col10)
'compares the original source SN with the hopper SN
'if a match, then the validation counter increases by one
'if it gets to 5 then
last_batch_row = Dash.Columns(batch_audit_Col).Find("*", , , , xlRows, xlPrevious).Row + 1
If rand_Value_T1 = hopper_sn Then
'if a mismatch doe snot occur, add a(0) in the correct batch column
Dash.Cells(last_batch_row, batch_audit_Col).Value = 0
validation_counter = validation_counter + 1
If validation_counter = 5 Then
Exit For
End If
ElseIf rand_Value <> hopper_sn Then
Dash.Cells(last_batch_row, batch_audit_Col).Value = 1
Exit For
End If
End If
'what to do if the validation cannot be made in 50 attempts
If x = 50 And validation_counter < 5 Then
'if an mismatch occurs, add an error marker(1) in the correct batch column
Dash.Cells(last_batch_row, batch_audit_Col).Value = 1
Exit For
End If
ElseIf Source_col7 <> "" Then
'sets of the customer PO match from the source file
Set rand_match = .Range(.Cells(firstrow, Source_col7), .Cells(lastrow, Source_col7))
Set rand_index = .Range(.Cells(firstrow, Source_col1), .Cells(lastrow, Source_col7))
'from whichever variable is selected, pull back the customer PO
source_PO = Application.Index(rand_index, Application.Match(rand_Value_T2, rand_match, 0), Source_col1)
'sets up index match parameters for the hopper file
Set rand_match = .Range(.Cells(firstrow, Target_col1), .Cells(randgen_lastrow, Target_col1))
Set rand_index = .Range(.Cells(firstrow, Target_col1), .Cells(randgen_lastrow, Target_col7))
'sets uses the customer PO previously retrieved to pull back the SN
hopper_part = Application.Index(rand_index, Application.Match(source_PO, rand_match, 0), Target_col7)
'compares the original source SN with the hopper SN
'if a match, then the validation counter increases by one
'if it gets to 5 then
last_ batch_row = Dash.Columns(batch_audit_Col).Find("*", , , , xlRows, xlPrevious).Row + 1
If rand_Value_T2 = hopper_part Then
'if a mismatch doe snot occur, add a(0) in the correct batch column
Dash.Cells(last_batch_row, batch_audit_Col).Value = 0
validation_counter = validation_counter + 1
If validation_counter = 5 Then
Exit For
End If
ElseIf rand_Value_T2 = hopper_part Then
'if an mismatch occurs, add an error marker(1) in the correct batch column
Dash.Cells(last_batch_row, batch_audit_Col).Value = 1
Exit For
End If
End If
'what to do if the validation cannot be made in 50 attempts
If x = 50 And validation_counter < 5 Then
Dash.Cells(last_batch_row, batch_audit_Col).Value = 1
Exit For
End If
End If
Next
'Add additional process to ensure that the data to the hopper is added to the bottom of the last used row
'and not simply to the last customer PO row. This way even if there is a missing PO or something,
'the data will be okay.
If IsFileOpen(folderpath & strFileName) Then
Workbooks(strFileName).Close False
fso.MoveFile folderpath & strFileName, destinationpath & strFileName
End If
strFileName = Dir
Loop
'puts a date stamp on the dashboard for the respective batch run
Dash.Range("D2").Value = Format(Date, "mm/dd/yyyy")
'saves and closes the hopper
Workbooks(hoppername).Save
Workbooks(hoppername).Close True
Calculate
'keeps track of the errors on the dashboard and presents the user with a count of errors.
error_count = WorksheetFunction.CountIf(Dash.Range("I3:I100"), 1)
If Dash.Range("F2").Value = "Batch Failed" Then
MsgBox "Batch Failed. Contact Analyst"
ElseIf error_count > 0 Then
MsgBox "Batch Complete with - " & error_count & " errors."
Else
MsgBox "Batch Complete without errors."
End If
ActiveWorkbook.Save
Exit Sub
'########################################################ERROR HANDLING##############################################################
'########################################################ERROR HANDLING##############################################################
'########################################################ERROR HANDLING##############################################################
'everything past here is error handling
nextsheet:
Batch_col = Application.Match("Reports that had Errors:", Dash.Rows("1:1"), 0)
If IsFileOpen(folderpath & strFileName) Then
Workbooks(strFileName).Close False
Last_Err_Rpt = Dash.Columns(Batch_col).Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
Dash.Cells(Last_Err_Rpt, Batch_col).Value = batch_num
Dash.Cells(Last_Err_Rpt, Batch_col + 1).Value = strFileName
strFileName = Dir
Else
Last_Err_Rpt = Dash.Columns(Batch_col).Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
Dash.Cells(Last_Err_Rpt, Batch_col).Value = batch_num
Dash.Cells(Last_Err_Rpt, Batch_col + 1).Value = strFileName
strFileName = Dir
End If
GoTo loopsetter
End With
End Sub