xluser2007
12-08-2008, 07:40 PM
Hi All,
I have few html files which contain tables I need to import.
I have written the following code, which works, but I would like to ensure that the HTML tables are REFRESHED before the table is copied from them.
How would I go about doing this?
Option Explicit
Sub Import_HTML_Table(strSourceHTMLfile As String, rngDestRange As Range, strDestNmdrng As String)
With rngDestRange.Parent.QueryTables.Add(Connection:= _
"FINDER;file:" & strSourceHTMLfile _
, Destination:=rngDestRange)
.Name = strDestNmdrng
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.SaveData = True
.RefreshStyle = xlOverwriteCells ' This is important
.AdjustColumnWidth = False
.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
Sub test()
Call _
Import_HTML_Table(ThisWorkbook.Worksheets("Parameters").Range("HTMLOutputPath").Value _
& "\bucket_0K.html", _
ThisWorkbook.Worksheets("Common_Law_Intimations").Range("RIC_bucket_0K"), "bucket0K")
End Sub
Any help appreciated.
I have few html files which contain tables I need to import.
I have written the following code, which works, but I would like to ensure that the HTML tables are REFRESHED before the table is copied from them.
How would I go about doing this?
Option Explicit
Sub Import_HTML_Table(strSourceHTMLfile As String, rngDestRange As Range, strDestNmdrng As String)
With rngDestRange.Parent.QueryTables.Add(Connection:= _
"FINDER;file:" & strSourceHTMLfile _
, Destination:=rngDestRange)
.Name = strDestNmdrng
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.SaveData = True
.RefreshStyle = xlOverwriteCells ' This is important
.AdjustColumnWidth = False
.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
Sub test()
Call _
Import_HTML_Table(ThisWorkbook.Worksheets("Parameters").Range("HTMLOutputPath").Value _
& "\bucket_0K.html", _
ThisWorkbook.Worksheets("Common_Law_Intimations").Range("RIC_bucket_0K"), "bucket0K")
End Sub
Any help appreciated.