Copying data from multiple CSV files into one

[zenjitsu]

Hey there, after feeling like a rock star by implementing a VBA script for my company, I've been thrown a new variable I don't know how to account for.

What I'm doing: I'm copying information from a .CSV that is imported into a folder that I have another .XLM file located at that has my script on it. User opens the XLM file, clicks the button to start the script, then that script is copying the data from the .CSV file, pasting it into itself, then exporting that newly formatted spreadsheet to a .txt file in a different folder, then deleting the original .CSV file that was imported so the next .CSV file can be imported without overwriting needing to be done

What has changed: I thought there would always and forever only be one CSV file being exported out of our other program into this folder, which would always be the current year - current month, IE 2021-11 if done today. I setup the script to read this as "yyyy-m". Now I've found out that if older information is being exported at the same time, that this program will export multiple files, each with the Year and the Month that the information inside contained. So now instead of only having 2021-11.csv in november, if older data is exported at the same time, I can have 2021-11.csv, 2021-10.csv, 2021-8.csv, and 2021-5.csv. (If January - September this number is formatted without a 0, IE 1 for January instead of 01)

Here's the script as I have it, how can I make it where the information is copied from ALL of the CSV files into the proper places into the XLM file and ready for export to the TXT file?

Sub paste_values()
    
     'Fname = Filename, Fpath - Filepath, Sname = Sheetname
Dim FName As String
Dim FPath As String
Dim Sname As String
Dim wb1 As ThisWorkbook

FPath = "C:\FOLDER\"
FName = Format(Date, "yyyy-m") & ".csv"
Sname = Format(Date, "yyyy-m")

   
    'open source workbook
    Workbooks.Open Filename:=FPath & FName
    
    'copying data
    Workbooks(FName).Worksheets(Sname).Range("A2:I200").Copy
    
    'define destination and paste values
    Workbooks.Open "C:\FOLDER\Script.xlsm"
    ThisWorkbook.Sheets("Sheet1").Range("B2").PasteSpecial Paste:=xlPasteValues
        
    'suppress overwrite prompt
    Application.DisplayAlerts = False

    'Export data to TXT file
    ActiveSheet.SaveAs "C:\NEWFILEEXPORT\Export.txt", FileFormat:=xlTextPrinter
    
    'Enable prompts again
    Application.DisplayAlerts = True
    
    'Display Export complete message
    x = MsgBox("Export Completed Successfully", 0, "Export")
        
    'Close open workbooks
    Workbooks(FName).Close
    
    'Delete all CSV files to clear folder out for next Docuware Export file
    Kill "C:\Folder\*.csv*"
    
    'Close current workbook
    ActiveWorkbook.Close
    
    End Sub

I am still very new to all things VBA and scripting in general (why so many comments in the code, my learning) really appreciate any assistance with this.

 

[Joe4]

You need to loop through all your CSV files in the folder.
There are some old threads here on this site that show you how to do that, i.e.

VBA to loop through all csv files in folder and delete specific columns

I have a folder that contains 100 csv files. I need to delete the same columns from each of these files. I have tried several variations of the code below without success. I keep receiving a syntax error. I have tried everything I can find and keep striking out. Any help would be greatly...

www.mrexcel.com

Loop through folder of csv files, import, and save as

Hi, i'm trying to loop through a folder of csv files, import them each into a separate workbook, and save as xls files into a different folder using the contents of a particular cell as the name of the xls. i've found code for loops, but i don't have the expertise to customize for cvs files...

www.mrexcel.com

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

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

www.mrexcel.com