Copying multiple text files into one excel but different tabs

[ARNG]

Hello everyone,

I am relatively new to VBA. I am trying to copy multiple text files(around 4) into one excel but different tabs and then edit the excel further.

I am able to find good youtube videos on how to copy multiple text files into one excel but not into different tabs.

Can somebody help me to start with it.

Thanks in advance!!

[SamT]

Show us your code for copying text files, I don't feel like reinventing your wheel (https://en.wikipedia.org/wiki/Reinventing_the_wheel), I would rather just make what you have work

[ARNG]

Thanks for replying Sam.

I haven't written any code yet but I think it will be good idea to write something first and you can update accordingly to meet my requirements.

Let me write something as per my knowledge and I will get back to you.

[ARNG]

Hi,

When i wrote the code :

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

I am getting error "Subscription out of range" error at the line in bold above.

Could help me with this?