Import last row of each CSV file within a folder

la333

New Member
Joined
May 14, 2018
Messages
27
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!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
@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.
 

Attachments

  • vbe.png
    vbe.png
    3.7 KB · Views: 5
  • vbe2.png
    vbe2.png
    17 KB · Views: 5
Upvote 0
In those cases, there is no penultimate, so don't copy?
 
Upvote 0
Try this:

VBA Code:
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
 
Upvote 0
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...

VBA Code:
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

VBA Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top