CODEWITHSUNDEEP
vbscript
Shankar Guru
Shankar Guru

Reputation: 1161

Multiple CSVs to single excel VBS

I am trying to read multiple CSVs into single spreadsheet. I got below code from google.

There are 10 CSVs present in "C:\Users\achayapa\Desktop\test". I need to have each of these CSVs in a single excel. could someone please help?

I am new to vb script. 

Sub MacroLoop()
Dim strFile As String
Dim ws As Worksheet
strFile = Dir("C:\Users\achayapa\Desktop\test\*.csv")
Do While strFile <> vbNullString
    ws = Sheets.Add
    With ws.QueryTables.Add(Connection:= _
        "TEXT;" & "C:\Users\achayapa\Desktop\test\" & strFile, Destination:=Range("$A$1"))
        .Name = strFile
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh(BackgroundQuery:=False)
    End With
    strFile = Dir
Loop
End Sub

Upvotes: 0

Views: 514

Answers (2)

Shankar Guru
Shankar Guru

Reputation: 1161

I just thought of sharing the answer to above question.

Create a VBA script as below:

Sub Macro1() 
Dim strPath As String 
Dim strFile As String

strPath = "C:\test\" 
strFile = Dir(strPath & "*.csv") 
Do While strFile <> "" 
    With ActiveWorkbook.Worksheets.Add 
        With .QueryTables.Add(Connection:="TEXT;" & strPath & strFile, _ 
            Destination:=.Range("A1")) 
            .Parent.Name = Replace(strFile, ".csv", "") 
            .TextFileParseType = xlDelimited 
            .TextFileTextQualifier = xlTextQualifierDoubleQuote 
            .TextFileConsecutiveDelimiter = False 
            .TextFileTabDelimiter = False 
            .TextFileSemicolonDelimiter = False 
            .TextFileCommaDelimiter = True 
            .TextFileSpaceDelimiter = False 
            .TextFileColumnDataTypes = Array(1) 
            .TextFileTrailingMinusNumbers = True 
            .Refresh BackgroundQuery:=False 
        End With 
    End With 
    strFile = Dir 
Loop 

End Sub

In above code include for path - '\' For example - C:\test\

After this follow include above VBA in Excel, follow steps as in below link:

http://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/

Upvotes: 1

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38755

Short answer: Yes it is possible.

Step 0 of long answer:

There are 2 syntactical errors in you SWub MacroLoop():

  1. ws = Sheets.Add must be Set ws = Sheets.Add because you want to assign an object to ws.
  2. .Refresh(BackgroundQuery:=False) must be .Refresh BackgroundQuery:=False because you must not use param list () when calling (a function/method as) a Sub (see here).

You may get problems with .TextFilePlatform and .TextFileTrailingMinusNumbers - at least I did when testing on my rather dated Excel. If so, disable those lines (' comment) and try again.

For the next step I would need a detailed account of your testing experience. What result do you expect and how did the actual outcome differ from that?

Upvotes: 0

Related Questions