A little over my head - Gathering Data and Merging into Single Worksheet

[deekm85]

Hello,

I'm working on a project that involves gathering data from a website called CrimeMapping. I've managed to put together something that automatically captures the data from the websites output table and copies the data into a different worksheet for each url. I've done this by manually modifying the parameters in the url and having them in each subsequent row in the first column.

Sub adds()
For x = 1 To 5
Worksheets("Crime").Select
Worksheets("Crime").Activate
mystr = "URL;http://www.crimemapping.com/DetailedReport.aspx?db=11/23/2013+00:00:00&de=11/24/2013+23:59:00&ccs=AR,AS,BU,DP,DR,DU,FR,HO,VT,RO,SX,TH,VA,VB,WE&xmin=-9339063.757914007&ymin=5176718.50954379&xmax=-9194444.9003984&ymax=5268901.565655747"

mystr = Cells(x, 1)
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = x & "C"
With ActiveSheet.QueryTables.Add(Connection:=mystr, Destination:=Range("$A$2"))
'CommandType = 0
        .Name = _
        "00&ccs=AR,AS,BU,DP,DR,DU,FR,HO,VT,RO,SX,TH,VA,VB,WE&xmin=-9339063.757914007&ymin=5176718.50954379&xmax=-9194444.9003984&ymax=5268901.565655747"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
End With
Next x
End Sub

I then take the output and run a script on the Location column (D) to replace any space characters with + and created a formula that builds the next website I need information from (GoogleAPIS Geocode). I also filter out Detroit Regional Police so I can see everything else and delete the rows manually, then remove the filter so I only have Detroit Regional Police data.

In the first column of another worksheet I have the second url I want to copy information from. For example maps.googleapis.com/maps/api/geocode/xml?address=Address,+City,+State&sensor=false/true
The address, city and state are all auto-populated from the previous step (except that I have to run a script to replace spaces).

Public Sub XMLIMport()

For x = 1 To 5

Worksheets("XmlMaps").Select
Worksheets("XmlMaps").Activate

myURL = Cells(x, 1)
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = x & "G"

ActiveWorkbook.XMLIMport URL:=myURL, _
ImportMap:=Nothing, Overwrite:=True, _
Destination:=Range("$A$1")


Next x

End Sub

My problem now is that each day I request crime data from the original website I get around 350 crimes/day in the Detroit region. Each day has its own Worksheet 1C, 2C, 3C, etc. Each of the average 350 crimes a day also has it's own Worksheet 1G, 2G, 3G, etc.

What I need ultimately is the long/lat coords in row 2 column G&H from the Google XML table aligned with its respective crime reported at the specified address. I intend to take this data and create a GIS mapping development of crime in a region for a term report. I think this is a really interesting topic however I lack the necessary skills to compile the data required..

Any help would be greatly appreciated.

Thanks,

D

 

[deekm85]

Bump,

A little feedback on if I've provided clear enough information regarding what I'm trying to do? Is this approach appropriate for my desired outcome?

Thanks,

D

 

[deekm85]

Selfless /Bump Again,

Need to assess the feasibility of this project any feedback would be greatly appreciated.

Thank you.

D