There's data just waiting to be scraped....

April 18, 2011 11:45 AM

Excel-macro filter / this-must-be-possible filter: copy and paste a cell (say, A2) into Google Maps, hit submit, then copy and paste the first phone number into an empty cell (say, A10). How to make it work?

It's gotta be possible, somehow... I have 400+ records to search for a phone and address using Google Maps. Given a name that's guaranteed to be there, what sort of automated system could scrape and dump? I'm familiar with HTML, but would appreciate a walking through of anything more complex.

posted by chrisinseoul to Computers & Internet (7 answers total)

odinsdream:: Excel macros typically can't break out into other applications and retrieve data.

Well, technically they can (I've got an Excel workbook that opens an instance of IE and scrapes/downloads all my recent activity from the bank daily, which is very similar to this request), but in all honesty, doing so is usually more trouble than it's worth.

I would agree with odinsdream that this is probably something you want to export to CSV and then process using some other tool designed for the job, rather than trying to do it entirely within Excel.
posted by Doofus Magoo at 12:41 PM on April 18, 2011

Another alternative (if you don't want to enter a world of scripting) would be Amazon Mechanical Turk - depending on how pricily you value your time, it might be simplest to just advertise it there - I doubt it'd come to over twenty dollars.
posted by piato at 12:44 PM on April 18, 2011

Best answer: I've been using the JitBit Macro Recorder for stuff like this
posted by Hairy Lobster at 12:54 PM on April 18, 2011

When all you have is a hammer:

Sub ScrapeFromGoogleMaps()

Dim tmpsheet, source As Worksheet
Set source = ActiveSheet


For Each s In source.Range("A1:A3")
URL = "URL;http://maps.google.com/maps?q=" & s.Value
Set tmpsheet = ActiveWorkbook.Worksheets.Add

With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("$A$1"))
.Name = "TempImport"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False

End With

For Each c In Range("TempImport")
If c.Value = "A" Then
i = 1
While c.Offset(i, 0).Value <> "B" And Right(c.Offset(i, 0).Value, 7) <> "reviews"
If c.Offset(i, 0) <> "Photos" And Right(c.Offset(i, 0).Value, 7) <> "info »" Then Address = Address & c.Offset(i, 0).Value & "~~"
i = i + 1
Wend
End If
Next c


Application.DisplayAlerts = False
tmpsheet.Delete
Application.DisplayAlerts = True
s.Offset(0, 1).Value = Address
Address = ""
Next s
End Sub


Change the bit about "A1:A3" to the range of your input info, and ensure there's nothing important next to it, and this should give you a start at least.

(This presumes the code isn't mangled. I can email you a workbook if you like).
posted by pompomtom at 4:42 PM on April 18, 2011

Response by poster: To clarify, it doesn't have to be done completely in Excel... The goal is an automated setup; mTurk would work as well.

@pompomtom - the code looks fascinating. My geek cred is fading however - where would this go? Excel's macro window?
posted by chrisinseoul at 10:34 PM on April 18, 2011

Yep.

Assuming you're on Windows: copy the code, go to excel, hit alt-F11, double-click "This Workbook" (in the pane on the left), then paste it. Adjust the source range to match your input info, and hit play.

(That said, this will run really slowly, and some other scripting option may well be preferable if excel isn't a requirement.)
posted by pompomtom at 11:17 PM on April 18, 2011

Response by poster: Thanks all - the Jitbit Macro Recorder did exactly what I needed it to do. I'll note it can record both keyboard and mouse input, but requires a bit of tweaking - and precision - to work over and over again.
posted by chrisinseoul at 7:42 AM on April 20, 2011