Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

(This is a revision of a similar question)

I often import a folder of .csv files into the active workbook (the workbook with the macro), so I want to automate this with VBA.

I pieced together the following script, but it inserts blank worksheet after every imported .csv files.

Sub ImportCSV()

With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select folder of .csv files."
    .Show
    strDir = .SelectedItems.Item(1)
End With

strFile = Dir(strDir & "\" & "*.csv")

Set wbSink = ThisWorkbook

Do While strFile <> ""

    Set wbSource = Workbooks.Open(strDir & "\" & strFile)
    wbSource.Sheets.Copy wbSink.Sheets.Add
    wbSource.Close False
    strFile = Dir()

Loop

End Sub

That is, after the first imported sheet it adds "Sheet2", then after the next imported sheet it adds "Sheet3", and so on. What is the fix?

share|improve this question
up vote 2 down vote accepted

Change the sheet copy line as follows:

Do While strFile <> ""
    Set wbSource = Workbooks.Open(strDir & "\" & strFile)
    wbSource.Sheets.Copy wbSink.Sheets(Sheets.Count)
    wbSource.Close False
    strFile = Dir()
Loop
share|improve this answer

This would work, but it's using copy/paste

Sub ImportCSV()

    Dim strFile As String
    Dim strPath As String
    Dim wkb As Workbook

    Application.ScreenUpdating = False

    strPath = "C:\"
    strFile = Dir(strPath & "*.csv")

        Do While strFile <> ""

            Set wkb = Workbooks.Open(strPath & strFile)

            ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count)

            wkb.Sheets(1).UsedRange.Copy

            ThisWorkbook.Sheets(Sheets.Count).Range("A1").Paste

            wkb.Close

            strFile = Dir
        Loop

    Application.ScreenUpdating = True

End Sub
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .