PDA

View Full Version : Problem with querytables from search return page



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"

stanl
11-30-2006, 02:57 PM
perhaps



With Sheets("test").QueryTables.Add(Connection:= _
"URL;" & sURL, _

should be


With Sheets("test").QueryTables.Add(Connection:= _
"URL;" & sURL2, _

Stan

jhize
12-01-2006, 07:50 AM
Yeah. Thanks I caught that. Problem is that it opens a new instance of IE and I need the already loaded Search returns page.

jhize
12-01-2006, 08:10 AM
Apologies to all. This is a cross post. I have to start a new thread. It will be called:

SELECT CASE web data to spreadsheet

It will incorporate all advise thus far received BUT more importantly it has a full description of what my needs are. Again apologies.