Add each csv filename to end of each row in excel

Lola223

New Member
Joined
Jan 18, 2022
Messages
26
Office Version
  1. 2010
Hi,

I am using the following VBA to loop through several csv files and copy the data into a worksheet on the active workbook the code is run from.

I would like to add the filename to the end of each row so that I know which file the copied data row has come from.

Public strPath As Range

Sub CopyRange()

Application.DisplayAlerts = False

Application.ScreenUpdating = False

Dim wkbDest As Workbook

Dim wkbSource As Workbook

Set wkbDest = ThisWorkbook

Dim LastRow As Long

Set strPath = Worksheets("Combine Dataset").Range("B1")

strExtension = Dir("*.csv*")

Do While strExtension <> ""

Set wkbSource = Workbooks.Open(strPath & strExtension)

With wkbSource

LastRow = .ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

.ActiveSheet.Range("A5:K" & LastRow).Copy wkbDest.Sheets("Banking Combined").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

.Close savechanges:=False

End With

strExtension = Dir

Loop

Application.ScreenUpdating = True

End Sub
 
perhaps should Worksheets("Combine Dataset")... be Worksheets("Combined Dataset")...
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
I have uploaded a screenshot of my spreadsheet. The B1 cell reference contains a file path. You can also see the named worksheets.
 

Attachments

  • Screenshot_20220420-160653_Gallery.jpg
    Screenshot_20220420-160653_Gallery.jpg
    161.2 KB · Views: 10
Upvote 0
I have tried the syntax on my pc and it works. It shouldn't make a difference, but try getting rid of the workbook clause and use range vs cells:

Worksheets("HybridMI Combined").Range("M" & LastRow).Value = Worksheets("Combine Dataset").Range("B1").value & ".csv"
 
Upvote 0
I don't think this really doing what I need. Stepping through the code actually results in the last row in column M being populated with the file path followed by .csv by what I actually want to happen is the following:

The first csv file is opened and all the contents copied over to my template file on tab HybridMI Combined which covers columns A to L. If there are say 300 rows in the first csv file, I'd like the first 300 rows in column M to be populated with the .csv filename these rows were copied from then same again for the second csv file until all files are covered.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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