Copying data from multiple CSV files into one

zenjitsu

New Member
Joined
Nov 11, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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?

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

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,742
Office Version
  1. 365
Platform
  1. Windows
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,491
Messages
5,764,679
Members
425,229
Latest member
Rashid mahmood

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
Top