Opening multiple doc/html files into excel worksheets within a single workbook

[sumncguy]

I have zero experience with VBA. Im trying to make my life easier by looking for examples on the web.
I have an application that dumps a list of reports for servers. Each server report is contained in its own file either n doc or html format.


for instance
server1.html
server2.html
server3.html


I found the code below. In order to test I created test files as follows
file1.txt
file2.txt
file3.txt
file4.txt
file5.txt

Sub LoadPipeDelimitedFiles()
    Dim idx As Integer
    Dim fpath As String
    Dim fname As String


    idx = 0
    fpath = "c:\Users\brittany\Desktop\"
    fname = Dir(fpath & "*.txt")
    While (Len(fname) > 0)
        idx = idx + 1
        Sheets("Sheet" & idx).Select
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
          & fpath & fname, Destination:=Range("A1"))
            .Name = "a" & idx
            .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 = False
            .TextFileSpaceDelimiter = False
            .TextFileOtherDelimiter = "|"
            .TextFileColumnDataTypes = Array(1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
            fname = Dir
        End With
    Wend
End Sub

I receive the following error and it seems that iteration is interrupted.


Microsoft visual basic
Run-time error '9':
Subscript out of range.
When I hit debug


Sheets("Sheet" & idx).Select line is highlighted.


Only tabs 1-3 are populated with the associated file data. In addition, the script doesnt work at all for html or doc files.


Thanks alot in advance for your help !


Regards

[westconn1]

Only tabs 1-3 are populated with the associated file data.

how many sheets in your workbook?
maybe you need to add more sheets when idx is above 3