Open multiple HTML files in succession

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!

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.

--
Cheers
Nigel



"Gil" <[email protected]> wrote in message
news:[email protected]...
> 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!

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.

"Nigel" wrote:

> 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.
>
> --
> Cheers
> Nigel
>
>
>
> "Gil" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
>
>
>

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