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

tommym121
tommym121 used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Commented:
Can you provide an example of the csv

Author

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

Fabrice LambertConsulting
Distinguished Expert 2017
Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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.

Author

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
You must specify the connection type, so it will be
With xlSheet.QueryTables.Add(Connection:="TEXT;" & vFileName, _
        Destination:=xlSheet.Range("$A$1"))

Author

Commented:
Thanks to you all.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial