Import multiple csv to separate worksheets (initial code within)

NRGZ

New Member
Joined
Jul 23, 2021
Messages
47
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
**I would like to give ALL credit to the Original author and the coder who updated it to work with Excel 2016.**

As i'm still early into my journey with VB and I would really appreciate some help with this.
The code below imports the CSVs fine, creates and names the worksheets fine but it leaves the excel 'import' workbooks open ( it was only 3 this time but imagine if there was 20 or more) so how can i get it to NOT open the workbooks or automatically close them afterwards or is there a better way to do this altogether?

Option Explicit
Sub ImportCSVs()
'Author: Jerry Beaucaire
'Date: 8/16/2010
'Summary: Import all CSV files from a folder into separate sheets
' named for the CSV filenames

'Update: 2/8/2013 Macro replaces existing sheets if they already exist in master workbook
'Update: base script as seen in: Jerry Beaucaire's - Excel Assistant - CSVs to Sheets
'Update: adjusted code to work in Excel 2016

Dim fPath As String
Dim fCSV As String
Dim wbName As String
Dim wbCSV As Workbook
Dim wbMST As Workbook


wbName = "this is a string"
Set wbMST = ThisWorkbook

fPath = "C:\csvs\" 'path to CSV files, include the final \
Application.ScreenUpdating = False 'speed up macro
Application.DisplayAlerts = False 'no error messages, take default answers
fCSV = Dir(fPath & "*.csv") 'start the CSV file listing

On Error Resume Next
Do While Len(fCSV) > 0
Set wbCSV = Workbooks.Open(fPath & fCSV) 'open a CSV file
If wbName = "this is a string" Then 'this is to check if we are just starting out and target workbook only has default Sheet 1
wbCSV.Sheets.Copy After:=wbMST.Sheets(1) 'for first pass, can leave as is. if loading a large number of csv files and excel crashes midway, update this to the last csv that was loaded to the target workbook
Else
wbCSV.Sheets.Copy After:=wbMST.Sheets(wbName) 'if not first pass, then insert csv after last one
End If

fCSV = Dir 'ready next CSV
wbName = ActiveSheet.Name 'save name of csv loaded in this pass, to be used in the next pass
Loop

Application.ScreenUpdating = True
Set wbCSV = Nothing
End Sub

Many Thanks In Advance.
o/
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,424
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Any reason you can't just close the import CSV file at the end of each loop?

VBA Code:
  wbCSV.Close False
  fCSV = Dir 'ready next CSV
  wbName = ActiveSheet.Name 'save name of csv loaded in this pass, to be used in the next pass
Loop
 
Solution

NRGZ

New Member
Joined
Jul 23, 2021
Messages
47
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
Any reason you can't just close the import CSV file at the end of each loop?

VBA Code:
  wbCSV.Close False
  fCSV = Dir 'ready next CSV
  wbName = ActiveSheet.Name 'save name of csv loaded in this pass, to be used in the next pass
Loop
YES! I changed the argument to TRUE (which i thought you might be hinting at) and it closed (well infact it didnt even look like it opened anything!) each sheet.
As i say i'm very early into my journey with VB and the above code isn't mine but I can 'read' and 'understand' most of the workings even though i dont understand every syntax and more so some of the 'arguments' that each syntax uses YET but I'm enjoying the adventure into VB.

Thanks for you help,
(please feel free to look at my other requests (CRIES!) for help)
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,424
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The only difference between wbCSV.Close False and wbCSV.Close True is that 'False' means that any changes made to wbCSV are not saved when the workbook is closed, while 'True' means that they are. Another form of these statements would be

VBA Code:
    wbCSV.Close SaveChanges:=False
    wbCSV.Close SaveChanges:=True
 

NRGZ

New Member
Joined
Jul 23, 2021
Messages
47
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

Thanks, so the variable wbCSV would be stored within the workbook when saved so when the workbook was opened again you could you 'recall/reuse' the value?
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,424
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Variable wbCSV (Dim wbCSV As Workbook ) is the variable that references the workbook. But that only lasts until the macro completes. Once closed, you would need to open the workbook and reassign the variable as you did before

VBA Code:
Set wbCSV = Workbooks.Open(fPath & fCSV) 'open a CSV file
 

NRGZ

New Member
Joined
Jul 23, 2021
Messages
47
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
Understood.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,563
Messages
5,838,125
Members
430,529
Latest member
PaperBoi5870

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