Get data from multiple CSV files

[Sukumar]

Hi

I am trying to pull data from multiple CSV Files.
Data from next CSV file should be appended to last Blank Row.
I am not able to identify what is error in following code:-

Private Sub Workbook_Open()

On Error GoTo errh

      Dim FileNames As Variant
      Dim mStr As String
      Dim FileNum, RowNum, ColNum As Long
     
      Cells.ClearContents

set rowdestination = activesheet.cells(rows.count, 1).end(xlup).offset(1)
     
      FileNames = Application.GetOpenFilename(MultiSelect:=True)
     
      If IsArray(FileNames) Then
           
          mStr = "You selected:" & vbNewLine
         
          For FileNum = LBound(FileNames) To UBound(FileNames)
         
              mStr = mStr & FileNum & " : " & FileNames(FileNum) & vbNewLine
             
              If FileNum = 1 Then
             
                RowNum = 1
             
              Else
             
                With ActiveSheet
                    RowNum = .Cells(.Rows.Count, "A").End(xlUp).Row
                    ColNum = .Cells(RowNum, .Columns.Count).End(xlToLeft).Column
                End With
             
              End If
             
              MsgBox RowNum & " : " & ColNum
             
              With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FileNames(FileNum), Destination:=RowDestination) 
                    '.CommandType = 0
                    .Name = "Range" & FileNum
                    .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(4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
                    1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
                    .TextFileTrailingMinusNumbers = True
                    .Refresh BackgroundQuery:=False
                End With

               
               
          Next FileNum
          MsgBox mStr
      Else
          Exit Sub
      End If
     
   
errh:
 
  MsgBox Err.Number & " : " & Err.Description
 
     
     
  End Sub

 

[Marc L]

Hi !

Just a logic error !

Move Set rowdestination codeline
just before With ActiveSheet.QueryTables …
​