karldou
07-29-2008, 04:26 AM
Hi,
I'm new to VBA and Excel so please be gentle :(
I have been given the task to try and speed up one of our processes.
What i need to do is to create a macro, that will read multiple HTML files, copy the contents of one table from each file, and then paste the contents into one column on a spreadsheet. Then it will filter out the one cell with the data I need, and then paste this cell into another sheet.
The data would need to be read from the table in the first HTML file, then pasted into a column, say A. Then it would need to read the second HTML file, copy the data from the table and paste into column A again, but one row lower. It would need to loop this process until all the HTML files in the directory have been read.
Once this has been completed, it would then need to copy the contents of multiple seperate cells into a new worksheet... say copy cells B2, B6, B10, B14 etc (4 row gap).
I have played a bit with Macros and VBA, and i have read almost all relevant posts on this forum, which have been really informative, but i'm now starting to get rather confused!
Here are a few snippets of the code i have been playing with:
Importing the table from the first HTML file
Sub Import()
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;file:///C:/Documents%20and%20Settings/Administrator/Desktop/Forms/test-new/test/1.htm" _
, Destination:=Range("A1"))
.Name = "1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
Copying one of the imported cells and pasting into a seperate sheet
' Copies Leave Date from E-Form and pastes into "input_file.xls"
Range("B9").Select
Selection.Copy
Workbooks.Open Filename:= _
"C:\Documents and Settings\Administrator\Desktop\Forms\test-new\test\input_file.xls"
Range("A2").Select
ActiveSheet.Paste
I know I will need to include an Offset to allow, the data from different HTML files, to be pasted into a row.
I would really appreciate it if someone can give me a few pointers on how to get the import loop working, plus the copy and past offset.
I have included the test HTML file in the attached 1.zip file.
Thanks
Karl
I'm new to VBA and Excel so please be gentle :(
I have been given the task to try and speed up one of our processes.
What i need to do is to create a macro, that will read multiple HTML files, copy the contents of one table from each file, and then paste the contents into one column on a spreadsheet. Then it will filter out the one cell with the data I need, and then paste this cell into another sheet.
The data would need to be read from the table in the first HTML file, then pasted into a column, say A. Then it would need to read the second HTML file, copy the data from the table and paste into column A again, but one row lower. It would need to loop this process until all the HTML files in the directory have been read.
Once this has been completed, it would then need to copy the contents of multiple seperate cells into a new worksheet... say copy cells B2, B6, B10, B14 etc (4 row gap).
I have played a bit with Macros and VBA, and i have read almost all relevant posts on this forum, which have been really informative, but i'm now starting to get rather confused!
Here are a few snippets of the code i have been playing with:
Importing the table from the first HTML file
Sub Import()
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;file:///C:/Documents%20and%20Settings/Administrator/Desktop/Forms/test-new/test/1.htm" _
, Destination:=Range("A1"))
.Name = "1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
Copying one of the imported cells and pasting into a seperate sheet
' Copies Leave Date from E-Form and pastes into "input_file.xls"
Range("B9").Select
Selection.Copy
Workbooks.Open Filename:= _
"C:\Documents and Settings\Administrator\Desktop\Forms\test-new\test\input_file.xls"
Range("A2").Select
ActiveSheet.Paste
I know I will need to include an Offset to allow, the data from different HTML files, to be pasted into a row.
I would really appreciate it if someone can give me a few pointers on how to get the import loop working, plus the copy and past offset.
I have included the test HTML file in the attached 1.zip file.
Thanks
Karl