Create an excel workbook with multiple worksheets by importing multiple csv file with command delimited record

I need to read  a number of comma delimited csv file from a specific folder and put each file into a worksheet with the name of the worksheet being the name of the csv file.  How do I do this using Excel VBA?
tommym121Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Can you provide an example of the csv
0
tommym121Author Commented:
Roy,  this is the code I try to do.  This is the line I am not sure what is the problem.  I basically ask for the first file to import and then I go throught the directory to import each file into a seperate worksheet.  Hope you can spot the problem of the code.
Thanks.
' *********************************************
'  It complains the invalid arguement,  I did do a macro recording to get the format, I change ActiveSheet to xlSheet. since it complains about the Application Object
'**********************************************
    With xlSheet.QueryTables.Add(Connection:= vFilename, _  
        Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = StrFile ' label the sheet with the import filename without extension
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With



Sub ImportCSVFile()
'Imports a text file using Excel's own
'(and fast) import function. The example
'assumes that semicolon is used as delimiter.
Dim vFileName
Dim StrFile As String
Dim inputDirectoryToScanForFile As String

   Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim xlSheetLast As Excel.Worksheet


    'Create an instance of Excel and start building a spreadsheet
    'Early Binding
    Set xlApp = Excel.Application
    xlApp.Visible = False
    Set xlBook = xlApp.Workbooks.Add
    
    On Error GoTo ErrorHandle
    
    'Here we use a filter to show nothing but text files with
    'the extension "*.txt". If you delete the file filter
    '("Text Files (*.txt),*.txt")
    'all files are shown. There are several other
    'possibilities - read the VBA help.
    vFileName = Application.GetOpenFilename("Text Files (*.csv),*.csv")
    
    'If the user pressed "Cancel" or didn't select a text file,
    'we exit the procedure.
    If vFileName = False Or Right(vFileName, 3) <> "csv" Then
       GoTo BeforeExit
    End If
    
    'Switch off screen updating for speed.
    Application.ScreenUpdating = False
        
    
    'Left(nPath, InStrRev(nPath, "\") - 1)
    inputDirectoryToScanForFile = Left(vFileName, InStrRev(vFileName, "\") - 1)
    StrFile = Dir(inputDirectoryToScanForFile & "\*" & "csv")
' Loop through the directroy to import the sheet
    Do While Len(StrFile) > 0
        Debug.Print StrFile
        Set xlSheet = xlBook.Sheets.Add(After:=xlBook.Sheets(xlBook.Sheets.Count))
        ''''xlSheet.Name = Left(StrFile, InStr(StrFile, ".") - 1)
        'We now import the selected text file, and data is
        'inserted in a new spreadsheet. 
    Application.CutCopyMode = False
' *********************************************
'  It complains the invalid arguement,  I did do a macro recording to get the format, I change ActiveSheet to xlSheet. since it complain about the Application Object
'**********************************************
    With xlSheet.QueryTables.Add(Connection:= vFilename, _  
        Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = StrFile ' label the sheet with the import filename without extension
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
        'Just to show how we auto adjust the width of column A.
        xlSheet.Columns("A:A").EntireColumn.AutoFit
        
        StrFile = Dir
    Loop

Open in new window

0
Fabrice LambertFabrice LambertCommented:
Your problem is simple and it is called Implicit reference.:

The Destination argument is set to Range("$A$1"), wich is implicitly ActiveWorkbook.ActiveSheet.Range("$A$1").
Handling one "active" object is already a nightmare, handling two is pure suicide.

So, replace the implicit reference by an explicit one (notice the additional dot):
Destination:=.Range("$A$1")

Open in new window


Side note:
Option Explicit and declaring your variables with a correct type won't hurt.
Your code handling the files selection should be in a separate function, by respect for SRP.
The dir function is unreliable, use the Scripting.FileSystem Library instead.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Ejgil HedegaardCommented:
The line
.CommandType = 0
is added by the macro recorder, but returns an error when run.
Delete the line, it is not needed to import the query.
0
tommym121Author Commented:
I modified the following line of the above code.  I got an 'Application-defined or object-defined error'.  Instead of defining Range() implicitly.  I explicitly define it as 'xlSheet.Range("$A$1")'.   I also comment out CommandType

    With xlSheet.QueryTables.Add(Connection:= _
        vFileName, _
        Destination:=xlSheet.Range("$A$1"))
        '.CommandType = 0
0
Ejgil HedegaardCommented:
You must specify the connection type, so it will be
With xlSheet.QueryTables.Add(Connection:="TEXT;" & vFileName, _
        Destination:=xlSheet.Range("$A$1"))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tommym121Author Commented:
Thanks to you all.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.