Using a Macro to copy data from all CSV files in folder

[MGA22]

Hello,

I was wondering if it would be possible to copy columns A-I from one worksheet(in the folder) and paste it in cell A1 then call on another 2 macros before looping and copying the data from the next CSV file in the folder. Essentially the new data copied over from the next sheet will overwrite the existing data that was copied over previously.

I'm still quite new to macros and the other macros I've created are quite basic for copying data over to a new table.

I would really appreciate any help.

Thank you.

 

[Zot]

Yes this is possible. During loop
1) Open a file
2) Import data
3) Call Macro1
4) Call Macro2
5) Clear worksheet
6) Got to step 1

Here is the link I found that can guide you clearly (I believe) on how to loop through files.

VBA code to loop through files in a folder (and sub folders)

Looping through every file in a folder is a useful technique to make changes to lots of file. This post provides 6 different methods to achieve this.

exceloffthegrid.com

 

[MGA22]

Thanks for your reply Zot.

I've gone through and made the code below.

Sub CopyDatafromFolder()
Dim fileName As Variant


 fileNmae = Dir("FolderDestination\ * .csv")

While fileName <> ""
 
 Columns("A:I").Select
    Selection.Copy
    
    Sheets("CSV Template").Range("A1").PasteSpecial


 'Reset filename
 fileName = Dir
 
 Call K_M_v2
 
 
Wend

End Sub

The issue I have at the moment is that I'm not too sure on how to reference my current active worksheet that I want to paste the data into so that it actually pastes.



Thank you,

 

[Zot]

Say you have macro in workbook you want to work on. During this stage you can define your worksheet already like

Dim ws As Worksheet
Set ws = Activeworkbook.Sheets("Sheet1")

Say you macro open another workbook from your data source file. Once the file opened by macro, the open file is now active usially. At this stage, you can define your source workbook and source worksheet. This way you have reference to both worksheet to be referred to in you code.

 

[Zot]

Here is one example I wrote before

Sub Test()

Dim Fname As Variant
Dim ws1 As Worksheet, ws2 As Worksheet
Dim wbA As Workbook, wbB As Workbook
Dim rngTotal As Range

Application.ScreenUpdating = False

' Define this Workbook as wbA
Set wbA = ActiveWorkbook
' Define working sheet in wbA. Change sheet name accordingly
Set ws1 = wbA.Sheets("Sheet1")

' Search destination Workbook
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb), *.xls; *.xlsx; *.xlsm; *.xlsb", Title:="Select a File")
If Fname = False Then Exit Sub                         'CANCEL is clicked

' Define opened Workbook as wbB while opening it.
Set wbB = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
' Define working sheet in wbB. Change sheet name accordingly
Set ws2 = wbB.Sheets("Sheet1")

End Sub

 

[MGA22]

Thanks again for your reply,

I understand now that i have to reference this worksheet that i want to copy the data to in my code (below as ws) when i want to paste the copied data and have altered it as per below. Issue is that it doesn't actually copy the data from the other workbooks and paste it in this worksheet("CSV Template"). I can see that it calls on the macro K_M_v2 but the data doesn't actually change.

Sub CopyDatafromFolder()
Dim fileName As Variant
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("CSV Template")

 fileName = Dir("H:\Retail\Independents Channel\Metcash\Head Office\Financial Analysis\Claims\2021 Metcash Claims\02. February\BBS\ *.csv")

While fileName <> ""
 
 Columns("A:I").Select
    Selection.Copy
    
    ws.Range("A1").PasteSpecial


 'Reset filename
 fileName = Dir
 
 Call K_M_v2
 
 
Wend

Thank you

 

[Zot]

Thanks again for your reply,

I understand now that i have to reference this worksheet that i want to copy the data to in my code (below as ws) when i want to paste the copied data and have altered it as per below. Issue is that it doesn't actually copy the data from the other workbooks and paste it in this worksheet("CSV Template"). I can see that it calls on the macro K_M_v2 but the data doesn't actually change.

Sub CopyDatafromFolder()
Dim fileName As Variant
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("CSV Template")

fileName = Dir("H:\Retail\Independents Channel\Metcash\Head Office\Financial Analysis\Claims\2021 Metcash Claims\02. February\BBS\ *.csv")

While fileName <> ""

Columns("A:I").Select
    Selection.Copy
   
    ws.Range("A1").PasteSpecial


'Reset filename
fileName = Dir

Call K_M_v2


Wend

Thank you

It is easier to create identifiable nickname for workbooks (if you are working with more than one workbook) and worksheets. Thsi way easy to refer to and you will not get lost

Glad you have that figure out yourself