Can I "scrape the screen" in VBA 1

[mscallisto]

If I go to a site that processes a query, like

http://www.switchboard.com

(search by phone tab) and type a phone number like 617 637 1234, I can see the number imbedded in the link call string below:

http://www.switchboard.com/bin/cgir...8787D6AE704313303203O031D2C3D36AC70439B303203

&at=617&e=637&n=1234&type=BOTH&image1.x=20&image1.y=11

The results however are not passed back but simply populate the screen.

Is there a way in VBA to scrape the screen and capture the results to an excel cell?

 

[mscallisto]

Also Is this something frowned upon by the site owner?

I want to look-up 100 or so phone numbers/mth

 

[SkipVought]

Hi,

Sure! After running the query under various test conditions, obeserve what cells contain the results you are looking for.


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[mscallisto]

YEAH BUT....how do i say Cells(r,c)= something from the screen?

 

[SkipVought]

You might have to loop thru the cells, starting at B12, which is where the query put the NAME on my sheet.

dim r as long, c as integer, sName as string
r = 12
c = 2
sName = Cells(r, c).Value

you'ld have to figure out what it looks like if the address is more than one row.

does the row after city/state/zip ALWAYS contain Email,Maps and What's Nearby???



Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[mscallisto]

which is where the query put the NAME on my sheet"

I'm missing a crucial piece Skip.

If C1 has a link and I run the following code, are you saying I should be seeing results on my spreadsheet that I can parse or do whatever ??

Sub Macro1()
    Range("C1").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End Sub

 

[SkipVought]

Yup!

I'd assign the 3 chunks to 3 separate cells (lets say Sheet2!A1, B1, C1

then in a macro...

Sub GetOne()
    Dim sConn As String
    
    sConn = "URL;[URL unfurl="true"]http://www.switchboard.com/bin/cgirlookup.dll?SR=&LNK=32%3A48&MEM=1"[/URL]
    sConn = sConn & "&QV=AA5810C5927B4F348F43E94AD97803A0l028351BDB348444306313203O01825DB5FACA704337313203O039F2C3D0FC9704315313203"
    sConn = sConn & "&at=" & Sheet2.[A1]
    sConn = sConn & "&e=" & Sheet2.[B1]
    sConn = sConn & "&n=" & Sheet2.[C1]
    sConn = sConn & "&type=BOTH&image1.x=29&image1.y=5"


    With Sheet1.QueryTables(1)
        .Connection = sConn
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[mscallisto]

I can smell it Skip...I know your gettin close, but
With Sheet1.QueryTables(1) gives a subscript error

 

[SkipVought]

Is your querytable on Code Sheet Name Sheet1? Check in the Project Explorer.


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[mscallisto]

Skip

For me this reminds me of the "First Rule of Holes"

"When you find yourself in one....quit digging"!!!

1st I didn't know I needed to do a query,and project explorer shows no query table anywhere.

Me thinks I'm lost and can't follow where your taking me (logically) and I don't want to waste your valuable time.

 

[SkipVought]

Project Explorer has Workbook, Sheets, Modules, Charts, Forms, Class Modules etc.

The Query Tabe is IN A SHEET. It is the Sheet Code Name. However, if you'd rather use the Sheet Tab Name...

    With Sheets("Your Sheet Tab Name With QT in it").QueryTables(1)

Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[SkipVought]

King's X

Hold on! I ASSUMED something that is PROBABLY not so.

I ASSUMED that you had already set up a web query in Excel. I guess that you have not.

But it's pretty simple.

Copy your connect string that you posted first.

On Sheet1 - clear everything -- Data/Get External Data/New Web Query - paste your url in - select entire page.

The returns the text from that site.

NOW proceed with the code I posted for you.

SORRY!


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[mscallisto]

Thanks Skip I will give that a try (I'll stay in the hole a bit longer)!!

 

[mscallisto]

THERE IT IS !!!!!!!!

Thanks Skip (very much) I knew you cou do it !!

sam

 

[SkipVought]

Great!


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}