Read from mutiple csv files to multiple sheets on a single workbook

[vinodravula]

Hi guys,

Hope some one can help me with this. Thanks for your help.

I am trying to read multiple csv files into different sheets on a single excel workbook. Its throwing a Run-time error '1004' at the below code:

With Sheets(output_sheet).QueryTables.Add(Connection:="Text;" + file_name, Destination:=Sheets(output_sheet).Range("$F$" + rownumber))

Can't work out what seems to be the issue. See below code:

Sub Import_DefectData()
For rep = 3 To 6

Dim file_name As String
Dim row_number As String
Dim output_sheet As String

  file_name = Sheets("Admin").Range("B" & rep).Value
  output_sheet = Sheets("Admin").Range("C" & rep).Value
  row_number = Sheets("Admin").Range("D" & rep).Value


   With Sheets(output_sheet).QueryTables.Add(Connection:="Text;" + file_name, Destination:=Sheets(output_sheet).Range("$F$" + rownumber))

  .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
  .TextFileTrailingMinusNumbers = True
  .Refresh BackgroundQuery:=False
  End With
  
  Dim wb_connection As WorkbookConnection
  For Each wb_connection In ActiveWorkbook.Connections
  If InStr(file_name, wb_connection.Name) > 0 Then
  wb_connection.Delete
  End If
  Next wb_connection

Next rep

 

[vletm]

vinodravula
Have You verified that
file_name, output_sheet & row_number
gets CORRECT/VALID values?