Import last row of each CSV file within a folder

[la333]

The closest post I've found that does similar to what I'm needing: Import last row of csv to excel answered by @DanteAmor

@DanteAmor I'm looking to do something similar but I have about 650 CSV files that I need to do the following for...
For each CSV file, I need to import the file name as Column A and then import the last row of 5 columns.

They are all in the same directory.

For further e.g. -

I have files named

AA1234.CSV
AA1235.CSV
AA1236.CSV
AA1237.CSV
AA1238.CSV
etc

and then each of the files appear as such -

bc456 1/6/2022 10:07 AM 192.168.1.5 Y
la333 1/7/2022 9:07 AM 192.168.1.5 Y
mc257 1/8/2022 7:07 AM 192.168.1.5 N
la333 1/9/2022 8:07 AM 192.168.1.5 Y
bc456 1/10/2022 9:07 AM 192.168.1.5 Y

I would like for my new file to add the name of the excel file to the first column and then pull the last row from each of the files.

AA1234 bc456 1/10/2022 9:07 AM 192.168.1.5 Y

Thanks for all of your help in advance!

 

[la333]

Thank you!

 

[la333]

@DanteAmor

We ran into an error... When a new device is added to the network and there's only been 1 login (1 row added to CSV), the script breaks. I've attached the error and the highlighted line causing the break.

 

[DanteAmor]

In those cases, there is no penultimate, so don't copy?

 

[la333]

In those cases, there is no penultimate, so don't copy?

Yes, it should just skip that file if there's not more than 1 row in the document.

 

[DanteAmor]

Try this:

Sub Import_last_row()
  Dim wb As Workbook
  Dim sh As Worksheet
  Dim sPath As String, sFile As Variant
  Dim lr As Long, i As Long
  
  Application.ScreenUpdating = False
  
  Set sh = Sheets("Sheet1")
  sPath = "C:\trabajo\entrada\"
  sFile = Dir(sPath & "*.csv")
  i = 2
  Do While sFile <> ""
    Set wb = Workbooks.Open(Filename:=sPath & sFile, ReadOnly:=True, local:=True)
    lr = wb.Sheets(1).Range("A:E").Find("*", , xlValues, , xlByRows, xlPrevious).Row - 1
    If lr > 1 Then
      sh.Range("A" & i).Value = Replace(sFile, ".csv", "")
      sh.Range("B" & i).Resize(1, 5).Value = wb.Sheets(1).Range("A" & lr).Resize(1, 5).Value
      i = i + 1
    End If
    wb.Close False
    sFile = Dir()
  Loop
  Columns("C:C").NumberFormat = "[$-F400]h:mm:ss AM/PM"
End Sub

 

[la333]

I got an error 91 with that but with this modification, I got the error to go away... Proper way of doing it instead?

So you're not looking blindly, this is the part I changed but the full code below as well...

lr = wb.Sheets(1).Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    If lr > 1 Then
      lr = wb.Sheets(1).Range("A:E").Find("*", , xlValues, , xlByRows, xlPrevious).Row - 1

Sub Import2ndLast()
  Dim wb As Workbook
  Dim sh As Worksheet
  Dim sPath As String, sFile As Variant
  Dim lr As Long, i As Long
  
  Application.ScreenUpdating = False
  
  Set sh = Sheets("Sheet1")
  sPath = "C:\trabajo\entrada\"
  sFile = Dir(sPath & "*.csv")
  i = 2
  Do While sFile <> ""
    Set wb = Workbooks.Open(Filename:=sPath & sFile, ReadOnly:=True, local:=True)
    lr = wb.Sheets(1).Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    If lr > 1 Then
      lr = wb.Sheets(1).Range("A:E").Find("*", , xlValues, , xlByRows, xlPrevious).Row - 1
      sh.Range("A" & i).Value = Replace(sFile, ".csv", "")
      sh.Range("B" & i).Resize(1, 5).Value = wb.Sheets(1).Range("A" & lr).Resize(1, 5).Value
      i = i + 1
    End If
    wb.Close False
    sFile = Dir()
  Loop
  Columns("D:D").NumberFormat = "[$-F400]h:mm:ss AM/PM"
End Sub

 

[DanteAmor]

I did not understand.
Do you have a problem or have you already solved it?

NOTE: If the csv file is empty then you will have problems.

 

[la333]

I did not understand.
Do you have a problem or have you already solved it?

NOTE: If the csv file is empty then you will have problems.

Appears to be working as needed now. Thank you!