Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open multiple HTML files in succession
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" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open multiple HTML files in succession
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" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open multiple HTML files in succession
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining multiple Excel files into one html? | Excel Programming | |||
Combining multiple Excel files into one html? | Excel Programming | |||
Combining multiple Excel files into one html? | Excel Programming | |||
How to open large HTML FILES IN EXCEL In quick Time | Excel Programming | |||
How to open large HTML FILES IN EXCEL In quick Time | Excel Programming |