Home

Batch Import of CSVs into workbook as individual worksheets

%3CLINGO-SUB%20id%3D%22lingo-sub-713808%22%20slang%3D%22en-US%22%3EBatch%20Import%20of%20CSVs%20into%20workbook%20as%20individual%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-713808%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20Everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20import%20a%20series%20of%20csv%20files%20into%20a%20workbook%20with%20worksheets%20for%20each%20csv%20file.%20Right%20now%20I%20have%20figured%20out%20how%20to%20import%20all%20of%20them%20into%20a%20single%20worksheet%2C%20but%20I%20cannot%20make%20individuals.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20you%20can%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-713808%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-720006%22%20slang%3D%22en-US%22%3ERe%3A%20Batch%20Import%20of%20CSVs%20into%20workbook%20as%20individual%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-720006%22%20slang%3D%22en-US%22%3EWhat%20method%20do%20you%20wish%20to%20use%3F%20And%20are%20all%20CSV%20files%20in%20one%20folder%3F%20And%20is%20it%20all%20files%20in%20that%20folder%3F%3CBR%20%2F%3EIf%20it%20is%20one%20folder%20of%20all%20the%20files%20in%20tht%20folder%20the%20VBA%20is%20pretty%20easy%20to%20loop%20through%20all%20the%20files%2C%20importing%20the%20data%20to%20a%20new%20sheet%20and%20naming%20that%20sheet%20the%20same%20name%20as%20the%20file%20name.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-720023%22%20slang%3D%22en-US%22%3ERe%3A%20Batch%20Import%20of%20CSVs%20into%20workbook%20as%20individual%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-720023%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F366440%22%20target%3D%22_blank%22%3E%40trishia11271%3C%2FA%3E%26nbsp%3B%20All%20the%20files%20are%20in%20the%20same%20folder%2C%20but%20I%20am%20uncertain%20of%20the%20VBA%20process.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-720061%22%20slang%3D%22en-US%22%3ERe%3A%20Batch%20Import%20of%20CSVs%20into%20workbook%20as%20individual%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-720061%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364504%22%20target%3D%22_blank%22%3E%40wynns2008%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20try%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20ReadCSV()%3CBR%20%2F%3EDim%20strFolder%20As%20String%3CBR%20%2F%3EDim%20strFile%20As%20String%3CBR%20%2F%3EDim%20wksTab%20As%20Worksheet%3CBR%20%2F%3EDim%20wkbSource%20As%20Workbook%3CBR%20%2F%3E%3CBR%20%2F%3EstrFolder%20%3D%20ThisWorkbook.Path%20%26amp%3B%20%22%5C%22%3CBR%20%2F%3EstrFile%20%3D%20Dir(strorder%20%26amp%3B%20%22*.csv%22)%3CBR%20%2F%3E%3CBR%20%2F%3EDo%20While%20strFile%20%26lt%3B%26gt%3B%20%22%22%3CBR%20%2F%3EDebug.Print%20strFile%3CBR%20%2F%3EWorkbooks.OpenText%20Filename%3A%3DstrFolder%20%26amp%3B%20strFile%2C%20Semicolon%3A%3DTrue%2C%20local%3A%3DTrue%3CBR%20%2F%3ESet%20wkbSource%20%3D%20ActiveWorkbook%3CBR%20%2F%3EThisWorkbook.Worksheets.Add%3CBR%20%2F%3EwkbSource.Worksheets(1).UsedRange.Copy%20Destination%3A%3DThisWorkbook.ActiveSheet.Range(%22A1%22)%3CBR%20%2F%3EwkbSource.Close%20savechanges%3A%3DFalse%3CBR%20%2F%3EstrFile%20%3D%20Dir%3CBR%20%2F%3ELoop%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eor%20see%20attachments.%20Copy%20all%20files%20into%20a%20folder%20an%20start%20file%20start.xlsm%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%20from%20germany%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fvba-tanker.com%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EA%20vba%20database%20full%20of%20macros%3C%2FA%3E%26nbsp%3B-%20%3CA%20href%3D%22https%3A%2F%2Fvba-tanker.com%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Evba-tanker.com%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-720079%22%20slang%3D%22en-US%22%3ERe%3A%20Batch%20Import%20of%20CSVs%20into%20workbook%20as%20individual%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-720079%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347933%22%20target%3D%22_blank%22%3E%40Berndvbatanker%3C%2FA%3E%26nbsp%3BWorks%20great%2C%20thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E
wynns2008
New Contributor

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.

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

@trishia11271  All the files are in the same folder, but I am uncertain of the VBA process.

@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

A vba database full of macros - vba-tanker.com

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies