Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

(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

2 Answers 2

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.