Open multiple HTML files in succession

[Guest]

I'm trying to strip data from several hundred HTML files with names
File1.htm, File2.htm, File3.htm, etc. I've written the macro to strip the
data (opening the file as a text file, or importing the data with a query,
works both ways) but get stuck trying to write the code to open each file in
turn, close it once I've copied the data into a worksheet, then open the next
file (and ultimately stop once every file in the directory has been handled).
All help appreciated - I'm trying to make the jump from 4.0 macros (where
this would be pretty easy) to VBA so please be gentle!

 

[Nigel]

Hi
Try using the macro recorder. This will give you a template code to begin
with and in some instances maybe enough.

Turn on macro recorder with tools->macro->record new macro, stop recording
then use Alt-F11 to view the code module just created.

 

[Guest]

Yep I did that and it works great for one file with a known filename. To wit:

With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;file:///C:/myfolder/file1.htm", _Destination:=Range("A1"))
.Name = "file1.htm"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

From there the rest of the macro copies data into a worksheet and leaves the
worksheet prepared for the next row of data.

I found some code snippets out there that I think I can adapt to solve this
problem but still welcome all suggestions.

 

[Guest]

This did it. Thanks to another site for a code snippet that didn't work but
provided the framework for this.

Dim intIndex As Integer
Dim fsSearch As FileSearch
Set fsSearch = Application.FileSearch
fsSearch.LookIn = "c:\mydirectory"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
fsSearch.Filename = "*.htm"
fsSearch.Execute

For Each i In fsSearch.FoundFiles
Workbooks.OpenText Filename:=(i), _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
'another sub formats the data
Application.Run "Extract.XLS!StripsAndPastesData"
Rows("1:1").Select
Selection.Copy
ActiveWorkbook.Close (False)
Windows("Extract.XLS").Activate
Sheets("Sheet1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("start").Select
ActiveWorkbook.Names.Add Name:="start",
RefersToR1C1:="=Sheet1!R[+1]C1"
Next i

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub