VBA - Opening multiple doc/html files into their own excel worksheets

[brittany1]

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 any help you can provide !

Regards
Brit

 

[SkipVought]

hi,

Each time you run that code it adds another querytable to your sheet. NOT A GOOD THING!

ADD your querytable MANUALLY ONE TIME and then REFRESH programmatically to get new data.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

your code assumes that EVERY FILE in that folder has 3 columns that are pipe delimited.

You code also assumes that all sheets are named Sheetn, where n is a number. Furthermore, it assumes that the number of files in your folder is less than or equal to the number of sheet so named. If the number of files in the folder exceeds the number of sheets so named, then you will have a [highlight #FCE94F]Subscript out of range[/highlight] error.

You can prevent that by testing the idx counter with the sheet count, assuming that the sheet count corresponds to the sheet names. If the idx counter exceeds the sheet count, then use the Worksheets.Add method before referencing that sheet.

    While (Len(fname) > 0)
        idx = idx + 1
    
    '[b]delete all previously entered QueryTables on each sheet
        For Each qt In Sheets("Sheet" & idx).QueryTables
            qt.Delete
        Next
    '[/b]
        With Sheets("Sheet" & idx).QueryTables.Add(Connection:="TEXT;" _
          & fpath & fname, Destination:=Range("A1"))

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]