Trying to bulk import csv to Excel using VBA, data turns out "slanted"

Question

This is my current VBA code:

Option Explicit

Private Function LoopThroughFolder(RootFolder As String, CsvFolder As String, Status As String)
    Dim folder, StrFile As String
    Dim wks As Worksheet
    folder = RootFolder & "\" & CsvFolder & "\" & Status
    StrFile = Dir(folder & "\*.csv")
    Do While Len(StrFile) > 0
        Set wks = Worksheets(CsvFolder & Status)
        ImportCsv folder & "\" & StrFile, wks
        StrFile = Dir
    Loop
    'Debug.Print RootFolder & "\" & CsvFolder & "\" & Status & " >>> OK!"
End Function

Private Function ImportCsv(CsvFile As String, wks As Worksheet)
    Dim row&, col As Integer
    'Debug.Print CsvFile
    row = wks.Cells(Rows.Count, 1).End(xlUp).row
    With wks.QueryTables _
         .Add(Connection:="TEXT;" & CsvFile, Destination:=wks.Cells(row + 1, 1))
        .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)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Function

Public Sub ImportFolderCsv()
    Dim RootFolder As String
    RootFolder = "C:\Users\chinkai\Desktop\dims investigate"
    Dim CsvFolders(1 To 2) As String
    CsvFolders(1) = "csvVeh"
    CsvFolders(2) = "csvCust"
    Dim Statuses(1 To 2) As String
    Statuses(1) = "FAIL"
    Statuses(2) = "PASS"

    Dim i, j As Integer
    Dim folder As String
    Dim ws As Worksheet
    For i = 1 To 1
        For j = 1 To 2
            Sheets.Add.Name = CsvFolders(i) & Statuses(j)
            LoopThroughFolder RootFolder, CsvFolders(i), Statuses(j)
        Next j
    Next i
End Sub

When I open my worksheets to view, the data appears in the form of an inverted triangle. Data from the first CSV goes into the top right corner, data from the second CSV goes below but to the left, so on and so forth, until the last CSV where data appears in the bottom left corner.

What my data looks like:

New to Excel VBA, so most of the code here are copy-pasta. I tried to tweak what I can but now I am not sure where I have gone wrong. Advice/feedback appreciated, thank you!

Edit: made some changes as suggested. Updated my code above and also provided a screen capture of this weird display...

Answer 1

I have played a bit with your code, but I could not replicate the "reversed triangle thing". However, just to make you started somewhere:

Replace the ActiveSheet with a reference of the worksheet, that you should pass as a parameter to the ImportCsv function:

Private Function ImportCsv(CsvFile As String, wks As Worksheet)
    Dim row&, col As Long
    Debug.Print CsvFile
    row = wks.Cells(Rows.Count, 1).End(xlUp).row    
    With wks.QueryTables _
         .Add(Connection:="TEXT;" & CsvFile, Destination:=wks.Cells(row + 1, 1))

And you take the wks like this from the Status string:

Private Function LoopThroughFolder(RootFolder As String, CsvFolder As String, Status As String)
    Dim folder, StrFile As String
    Dim wks As Worksheet
    folder = RootFolder & "\" & CsvFolder & "\" & Status
    StrFile = Dir(folder & "\*.csv")
    Do While Len(StrFile) > 0
        Set wks = Worksheets("csvVeh" & Status)

Two more important points:

• write Option Explicit on the top of your module and try to declare all variables. Then go to Debug>Compile on the VBEditor ribbon and declare what is not declared.

• as @Peh mentioned in the comments if you declare like this in C++ Dim a, b as Integer, then a and b are Integers. In VBA only b is declared as an Integer, a is a Variant. You should declare Dim a As Integer, b as Integer

• Why Use Integer Instead of Long?