ARNG
11-05-2019, 02:21 PM
Hi,
When i wrote the code to copy multiple text files into one excel sheet, I am getting an error "Subscription out of range" error at the line in bold below
***Here is my code below***
Sub importfiles()
'
' importfiles Macro
For rep = 4 To 7
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("$A$" + row_number))
.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 = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileFixedColumnWidths = Array(10, 2)
.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
End Sub
Could anyone help me with this?
When i wrote the code to copy multiple text files into one excel sheet, I am getting an error "Subscription out of range" error at the line in bold below
***Here is my code below***
Sub importfiles()
'
' importfiles Macro
For rep = 4 To 7
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("$A$" + row_number))
.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 = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileFixedColumnWidths = Array(10, 2)
.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
End Sub
Could anyone help me with this?