Problem with querytables from search return page

[jhize]

Hi all,

I'm new to forum, not new to VBA but right know I feel like it.

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.

 
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]

perhaps

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

should be

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

Stan

[jhize]

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]

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.