This is the code I had previously but it is not working
Sub Update(control As IRibbonControl)
On Error GoTo here:
Application.ScreenUpdating = False
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")
Dim wb2 As Workbook
Dim ws2 As Worksheet
Dim fileDialog As Office.fileDialog
Dim filePaths As Variant
' Create a file dialog object
Set fileDialog = Application.fileDialog(msoFileDialogFilePicker)
' Allow multiple file selection
fileDialog.AllowMultiSelect = True
' Add filter for Excel files
fileDialog.Filters.Add "Excel Files", "*.xls; *.xlsx; *.xlsm", 1
' Show the file picker dialog
Dim lrow1, lrow2, i, j As Long
Dim hasMatch As Boolean
hasMatch = False
Dim lcol As Long
Dim tmp As Variant
If fileDialog.Show = -1 Then
For Each filepath In fileDialog.SelectedItems
' Here you can perform actions on each selected file
'Debug.Print "Opening file: " & filepath
Set wb2 = Workbooks.Open(filepath)
Set ws2 = wb2.Sheets(1)
lrow2 = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row
lrow1 = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Dim nameCol, mapCol, notesCol, dateCol, qtyCol, addressCol As Long
nameCol = Application.Match("Name", ws2.Rows(1), 0)
mapCol = Application.Match("Map", ws2.Rows(1), 0)
notesCol = Application.Match("Notes", ws2.Rows(1), 0)
dateCol = Application.Match("Date", ws2.Rows(1), 0)
qtyCol = Application.Match("Driver Note", ws2.Rows(1), 0)
addressCol = Application.Match("Address", ws2.Rows(1), 0)
If IsError(nameCol) Then
tmp = appendNameCol("Name", CStr(filepath), ws2)
If tmp = -1 Then
MsgBox "User cancelled."
wb2.Close
Exit Sub
Else: nameCol = tmp
End If
End If
If IsError(mapCol) Then
tmp = appendNameCol("Map", CStr(filepath), ws2)
If tmp = -1 Then
MsgBox "User cancelled."
wb2.Close
Exit Sub
Else: mapCol = tmp
End If
End If
If IsError(notesCol) Then
tmp = appendNameCol("Notes", CStr(filepath), ws2)
If tmp = -1 Then
MsgBox "User cancelled."
wb2.Close
Exit Sub
Else: notesCol = tmp
End If
End If
If IsError(dateCol) Then
tmp = appendNameCol("Date", CStr(filepath), ws2)
If tmp = -1 Then
MsgBox "User cancelled."
wb2.Close
Exit Sub
Else: dateCol = tmp
End If
End If
If IsError(qtyCol) Then
tmp = appendNameCol("Driver Note", CStr(filepath), ws2)
If tmp = -1 Then
MsgBox "User cancelled."
wb2.Close
Exit Sub
Else: qtyCol = tmp
End If
End If
If IsError(addressCol) Then
tmp = appendNameCol("Address", CStr(filepath), ws2)
If tmp = -1 Then
MsgBox "User cancelled."
wb2.Close
Exit Sub
Else: addressCol = tmp
End If
End If
For i = 2 To lrow2
If ws2.Cells(i, nameCol) <> "" Then
For j = 2 To lrow1
If ws2.Cells(i, nameCol) & "-" & ws2.Cells(i, mapCol) = ws.Cells(j, "A") & "-" & ws.Cells(j, "C") Then
hasMatch = True
lcol = ws.Cells(j, ws.Columns.Count).End(xlToLeft).Column
ws.Cells(j, 4) = ws2.Cells(i, notesCol)
ws.Cells(j, 4).WrapText = False
ws.Cells(j, lcol + 1) = ws2.Cells(i, dateCol)
ws.Cells(j, lcol + 1).NumberFormat = "m/d/yyyy"
ws.Cells(j, lcol + 1).HorizontalAlignment = xlCenter
ws.Cells(1, lcol + 1) = "DELIV DATE"
If ws2.Cells(i, qtyCol) <> "" Then
ws.Cells(j, lcol + 2) = CInt(ws2.Cells(i, qtyCol))
Else: ws.Cells(j, lcol + 2) = "-"
End If
ws.Cells(j, lcol + 2).HorizontalAlignment = xlCenter
ws.Cells(j, lcol + 2).WrapText = False
ws.Cells(j, lcol + 2).NumberFormat = "General"
ws.Cells(1, lcol + 2) = "QUANTITY"
Exit For
End If
Next j
If hasMatch = False Then
lrow1 = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
ws.Cells(lrow1 + 1, "A") = ws2.Cells(i, nameCol)
ws.Cells(lrow1 + 1, "B") = ws2.Cells(i, addressCol)
ws.Cells(lrow1 + 1, "C") = ws2.Cells(i, mapCol)
ws.Cells(lrow1 + 1, "D") = ws2.Cells(i, notesCol)
ws.Cells(lrow1 + 1, "E") = ws2.Cells(i, dateCol)
ws.Cells(lrow1 + 1, "E").NumberFormat = "m/d/yyyy"
ws.Cells(lrow1 + 1, "E").HorizontalAlignment = xlCenter
If ws2.Cells(i, qtyCol) <> "" Then
ws.Cells(lrow1 + 1, "F") = CInt(ws2.Cells(i, qtyCol))
ws.Cells(lrow1 + 1, "F").NumberFormat = "General"
Else: ws.Cells(lrow1 + 1, "F") = "-"
End If
ws.Cells(lrow1 + 1, "F").HorizontalAlignment = xlCenter
ws.Cells(lrow1 + 1, "F").WrapText = False
End If
End If
hasMatch = False
Next i
ws.Rows(1).Font.Bold = True
wb2.Close
' For demonstration purpose, let's just print the file paths
Next filepath
Else
Debug.Print "No files selected."
End If
' Release the file dialog object
Set fileDialog = Nothing
MsgBox "Done Processing data."
Exit Sub
here:
MsgBox "Incorrectly done. Please make sure to close input files before using this macro."
End Sub
Function appendNameCol(str As String, pat As String, ws As Worksheet)
Dim updatedStr As String
Dim colName As Variant
Dim isOK As Boolean
isOK = False
updatedStr = InputBox(str & " column name cannot be found on " & pat & vbNewLine & " Please enter correct column name.", "Append column name")
Do Until isOK = True Or updatedStr = ""
colName = Application.Match(updatedStr, ws.Rows(1), 0)
If IsError(colName) = False Then
isOK = True
Else:
updatedStr = InputBox(str & " column name still cannot be found on " & pat & vbNewLine & " Please enter correct column name.", "Append column name.")
End If
Loop
If updatedStr = "" Or updatedStr = vbNullString Then
appendNameCol = -1
Else:
appendNameCol = colName
End If
End Function
