jhize
11-29-2006, 03:12 PM
Hi all,
I'm new to forum, not new to VBA but right know I feel like it. :banghead:
As the code below shows I've created an IEObject that opens a corporate directory on my company's intranet. Users can enter an employees name and hit search. The search result page then displays with the employees info. This works already. My problem is getting the search results copied with formatting to my spreadsheet. The querytables.add does this but excludes the search results (it just copies the formatting). Any help would be greatly appreciated.:help
Dim IeApp As InternetExplorer
Dim sURL As String
Dim sURL2 As String
Dim IeDoc As Object
Dim iedoc2 As Object
Dim i As Long
Dim nm As String
Dim searchperson As Variant
searchperson = Sheets("sheet2").Range("c10").Value
Set IeApp = New InternetExplorer
IeApp.Visible = False
sURL = "search link here"
sURL2 = "search return url here"
IeApp.navigate sURL
Do
Loop Until IeApp.readyState = READYSTATE_COMPLETE
Set IeDoc = IeApp.document
IeDoc.all.f_simple.Value = (Sheets("sheet2").Range("c10").Value)
IeDoc.all.submit.Click
MsgBox "begin redirect"
' Now auto navigating to sURL2
Application.Wait (Now + TimeValue("0:00:02"))
Do
Loop Until IeApp.readyState = READYSTATE_COMPLETE
'search page is loaded at this point
' Search Results ----------------------------///
MsgBox "readystate complete"
With Sheets("test").QueryTables.Add(Connection:= _
"URL;" & sURL, _
Destination:=Sheets("test").Range("A1"))
'.QueryType = xlWebQuery
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
MsgBox "webquery complete"
I'm new to forum, not new to VBA but right know I feel like it. :banghead:
As the code below shows I've created an IEObject that opens a corporate directory on my company's intranet. Users can enter an employees name and hit search. The search result page then displays with the employees info. This works already. My problem is getting the search results copied with formatting to my spreadsheet. The querytables.add does this but excludes the search results (it just copies the formatting). Any help would be greatly appreciated.:help
Dim IeApp As InternetExplorer
Dim sURL As String
Dim sURL2 As String
Dim IeDoc As Object
Dim iedoc2 As Object
Dim i As Long
Dim nm As String
Dim searchperson As Variant
searchperson = Sheets("sheet2").Range("c10").Value
Set IeApp = New InternetExplorer
IeApp.Visible = False
sURL = "search link here"
sURL2 = "search return url here"
IeApp.navigate sURL
Do
Loop Until IeApp.readyState = READYSTATE_COMPLETE
Set IeDoc = IeApp.document
IeDoc.all.f_simple.Value = (Sheets("sheet2").Range("c10").Value)
IeDoc.all.submit.Click
MsgBox "begin redirect"
' Now auto navigating to sURL2
Application.Wait (Now + TimeValue("0:00:02"))
Do
Loop Until IeApp.readyState = READYSTATE_COMPLETE
'search page is loaded at this point
' Search Results ----------------------------///
MsgBox "readystate complete"
With Sheets("test").QueryTables.Add(Connection:= _
"URL;" & sURL, _
Destination:=Sheets("test").Range("A1"))
'.QueryType = xlWebQuery
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
MsgBox "webquery complete"