1

I have to download a .csv file, convert it to .xlsx and copy data from certain cells to corresponding cells in a different workbook. The downloaded and converted workbook is wbAdpx while the workbook in which updates need to be amde is titled wbNewT. Each of these workbooks has just one worksheet, titled "ADPX" and "ACE" respectively.

Here is what I have so far and it works ok to download the csv file and convert it to .xlsx.

    Sub MatchData()
    Dim MyFSO As FileSystemObject
    Dim MyFileLoc, ParStr, ParStr1, ParStr2 As String
    Dim MyFd As Office.FileDialog
    Dim MyExtn As Variant
       
    ' select the downloaded In-Progress status file.
    Set MyFSO = New FileSystemObject
    Set MyFd = Application.FileDialog(msoFileDialogFilePicker)

   With MyFd
      .InitialFileName = ThisWorkbook.Path
      .AllowMultiSelect = False
      .Title = "Please select the file."
      .Filters.Clear
      .Filters.Add "All Files", "*.*"   
      If .Show = True Then
        MyFileLoc = .SelectedItems(1)
        MyExtn = MyFSO.GetExtensionName(MyFileLoc)
       Else
        MsgBox "Clicked cancel! No file selected"
       End If
   End With
    'Convert selected csv file to xlsx format
    If MyExtn = "csv" Then
        ParStr = Left(MyFileLoc, Len(MyFileLoc) - 4) & ".xlsx"
        ActiveWorkbook.SaveAs Filename:=ParStr, CreateBackup:=False
        ParStr1 = Mid(ParStr, 33, 9)
        ParStr2 = Mid(ParStr1, 1, 4)
    Else
        MsgBox "Invalid file format!"
    End If

    Set wbNewT = ThisWorkbook
    Set wbAdpx = Workbooks(2)

I am not sure how to compete this code because, while wbADPx always has first and last names cells B2 & B3 in row# 2 while matching row with the same first and last name in wbNewT changes every two weeks.

I am unable to figure out how to determine to which row in wbNewT should I copy data for first and last names in each row of wbAdpx.

Help please?

Thanks

Ven.

Ven
  • 21
  • 1
  • I'm assuming you meant B2 & C2 for first and last names? Otherwise, they're not on the same row as you mentioned later. As for matching the rows, unless you got a key-column, I don't think application.Match would help you. So you could put wbNewT's values in an array, add the first & last name as a key to the dictionary with the position in the array as the value in a dictionary. Then with the value array of your wbADPx check with first and last name what the position is with the dictionary and adjust in the wbNewTArray where necessary. Then paste entire array back in wbNewT. – Notus_Panda Jul 03 '23 at 09:09
  • @Notus_Panda, thank you for the suggestion, Sir. To clarify, yes, B2 and C2 contain the first and last names. I did not follow how to implement "add the first & last name as a key to the dictionary with the position in the array as the value in a dictionary" into the code. Can you pl. explain? Thanks. – Ven Jul 03 '23 at 13:32
  • See [this answer](https://stackoverflow.com/a/915333/19353309) about dictionaries, the key would be something like `firstName & " | " & lastName` and the value the i (iteration) of the loop of the array of wbNewT – Notus_Panda Jul 03 '23 at 16:44

0 Answers0