Forum Discussion

wynns2008's avatar
wynns2008
Copper Contributor
Jun 21, 2019

Batch Import of CSVs into workbook as individual worksheets

Hey Everyone,

 

I am trying to import a series of csv files into a workbook with worksheets for each csv file. Right now I have figured out how to import all of them into a single worksheet, but I cannot make individuals.

 

Hope you can help.

excel
Formulas and Functions
office 365

4 Replies

  • Berndvbatanker's avatar
    Berndvbatanker
    Iron Contributor
    Jun 25, 2019

    wynns2008 

    Hi, try this.

     

    Sub ReadCSV()
    Dim strFolder As String
    Dim strFile As String
    Dim wksTab As Worksheet
    Dim wkbSource As Workbook

    strFolder = ThisWorkbook.Path & "\"
    strFile = Dir(strorder & "*.csv")

    Do While strFile <> ""
    Debug.Print strFile
    Workbooks.OpenText Filename:=strFolder & strFile, Semicolon:=True, local:=True
    Set wkbSource = ActiveWorkbook
    ThisWorkbook.Worksheets.Add
    wkbSource.Worksheets(1).UsedRange.Copy Destination:=ThisWorkbook.ActiveSheet.Range("A1")
    wkbSource.Close savechanges:=False
    strFile = Dir
    Loop

    End Sub

     

    or see attachments. Copy all files into a folder an start file start.xlsm

     

    Best regards from germany

    Bernd

    https://vba-tanker.com/ - https://vba-tanker.com/

  • trishia11271's avatar
    trishia11271
    Copper Contributor
    Jun 25, 2019
    What method do you wish to use? And are all CSV files in one folder? And is it all files in that folder?
    If it is one folder of all the files in tht folder the VBA is pretty easy to loop through all the files, importing the data to a new sheet and naming that sheet the same name as the file name.

Resources