Import last row of csv to excel

[uki]

Hi!
I really need your help.

I got one file named - "TargetScan Data.csv" (source file). File contains 10 (static) columns and dynamic rows. Each day one row is added. File is on D:\OneDrive\TargetScan and What I want: I want to be able to import last row to my "File.xlsm' file (destination file). I want to click specific cell - and after clicking a button it should import row of 9 cells. In other words - I want to copy last row from csv file to xlsm file with one click.

Can You guys help?

Thank You very much! uki

 

[DanteAmor]

Hi and welcome to MrExcel

Try this:

Sub Import_last_row()
  Dim sFile As String, wb As Workbook, lr As Long, cell As Range
  Application.ScreenUpdating = False
  sFile = "D:\OneDrive\TargetScan\TargetScan Data.csv"
  Set cell = ActiveCell
  Set wb = Workbooks.Open(sFile, , True)
  lr = ActiveSheet.Range("A:I").Find("*", , xlValues, , xlByRows, xlPrevious).Row
  cell.Resize(1, 9).Value = Range("A" & lr).Resize(1, 9).Value
  wb.Close False
End Sub

 

[uki]

Thank You very very much! It works, but:
csv file contains values separated by semicolon (example last row):

"05.05.2020";"5 maj 2020";"40";"380-16x (400,3)";"91,81";"-0,75";"-4,69";"6,57";"25,50";"CPM-1"

When I import last row from closed csv file i got this:

When I import last row from opened (in the background) csv file it works just fine:

Any clue how to fix this?

 

[DanteAmor]

"05.05.2020";"5 maj 2020";"40";"380-16x (400,3)";"91,81";"-0,75";"-4,69";"6,57";"25,50";"CPM-1"

Is the data in your file in quotes?

Try this

Sub Import_last_row()
  Dim sFile As String, wb As Workbook, lr As Long, cell As Range
  Application.ScreenUpdating = False
  sFile = "D:\OneDrive\TargetScan\TargetScan Data.csv"
  sFile = "C:\trabajo\TargetScan Data.csv"
  Set cell = ActiveCell
  Set wb = Workbooks.Open(Filename:=sFile, ReadOnly:=True, local:=True)
  lr = ActiveSheet.Range("A:I").Find("*", , xlValues, , xlByRows, xlPrevious).Row
  cell.Resize(1, 9).Value = Range("A" & lr).Resize(1, 9).Value
  wb.Close False
End Sub

 

[uki]

Thank You very much for Your help! ? Works like a charm now! Much appreciated!?

PS Yes, data in the file is in quotes.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[la333]

@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.

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!

Edit: They are all in the same directory.

 

[Joe4]

@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.

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!

Edit: They are all in the same directory.

Your question is a bit different than the original question.
As such, you should post it to a new thread instead of tacking on to an old one.
That way it will appear as a new unanswered question in the "Unanswered threads" list that many people use to look for and answer questions.

 

[la333]

Your question is a bit different than the original question.
As such, you should post it to a new thread instead of tacking on to an old one.
That way it will appear as a new unanswered question in the "Unanswered threads" list that many people use to look for and answer questions.

I did go ahead and do that just now. Thanks!

Link: Import last row of each CSV file within a folder