Google has now stopped its Earth API and Maps API is a paid for API nowadays. Some capabilities have also been either removed or changed. Therefore, I had to remove all examples of VizMap applications I had created. I didn’t remove the entire topic as I thought some of the code may still be useful with some modifications.
VizMap: the vba to join master to transactional data
This relates to Data Driven Mapping applications
For this example I had created a Concerts/Venues example that is now deprecated. The complete workbook (googlemapping.xlsm) can be downloaded (above) and the example Parameter WorkSheet is called VenuesParameters.
How to join master and transaction data
In our example we have the following worksheets
- venuesParameters. The specific parameter sheet describing the required application.
- geoCodingParameters. The common parameter sheet describing how to geoCode and other common parameter blocks
- venueMaster. The list of known venues. This contains data about the venue, included the geoCoded addresses.
- artistMaster. This is a lookup sheet that contains information about the artists that will be performing at particular venues
- venueTransactions. This contains the list that associates venues with artists – thats to say a list of dates that artist x is performing and venue y
- venueMapping. This is the result of joining 3,4 and 5 and is the only data that is input to the VizMap application.
Each of the names of these sheets, and how to join them together, is defined in the this parameter block in the venuesParameters worksheet
and the fields that are to be cloned from these are defined in the Clone parameter block
the vba walkthrough
You will find the procedure to accomplish this in the joiningExample module. As usual the capabilities of the Data Manipulation Classes are used to make this a trivial coding exercise.
- Standard parameter setup , dSetsSetup
- Pick up additional parameters, specific to the transaction data, that is only needed for this exercise getTransactionParameters.
- Find any extra look up worksheets referenced (in this case the artistmaster), extraJoins.
- Copy all the transactions from venuesTransactions to venuesMapping, bigCommit.
- Add all the fields mentioned in the clone block are added
- A new dSet is created, now containing all the columns to be cloned, plus the data from the original transactions
- cDataSet has the capability of identifying a column as a key field when abstracting a block of data. It is this capability that allows the lookup of parameters through the syntax .cell(paramrow,paramcol). When the venueMaster and artistMaster datasets were populated, the join keys Venue ID and Artist ID were set as their respective keys. This means that when you access a .cell(id,col) the id will refer to the Venue ID in the case of the venueMaster and the artist ID in the case of the artistMaster. This means that matching the venueTransactions to these look up files and copying over the cloned column does not even need any lookup code. The matching row is returned like this Set drk = dSets.DataSet(sMaster, True).Row(sId) and the matching cell in that row is copied by dr.Cell(sField).Value = drk.Cell(sField).Value
- Commit all the cloned data, .bigcommit
joiningExample complete code.
Option Explicit 'for more about this ' https://ramblings.mcpher.com/classes/datamanip/ 'to contact me ' https://gitter.im/desktopliberation/community 'reuse of code ' https://ramblings.mcpher.com/reusing-code-from-this-site/ Dim dSets As cDataSets ' this one looks copies over transactions, looks up the matching fields in the customer master ' and checks the paramater sheet to find what the important column headings are ' application 1 - orders/transactions Public Sub transactionJoinOrders() transactionJoinExample cVizAppOrders End Sub ' application 2 - venues/artists Public Sub transactionJoinVenues() transactionJoinExample cVizAppVenues End Sub ' application 3 - palaces Public Sub transactionJoinPalaces() transactionJoinExample cVizAppPalaces End Sub ' application 4 - orgs Public Sub transactionJoinOrg() transactionJoinExample cVizAppOrg End Sub Public Sub transactionJoinExample(paramName As String) Dim hc As cCell, r As Range, dr As cDataRow, cc As cCell, sId As String, sField As String Dim rc As cCell, jExtend As String, drc As cDataRow, drk As cDataRow, sKey As String Dim sMaster As String, a As Variant, sOut As String jExtend = cJoin & "plus" Set dSets = dSetsSetup(paramName) If dSets Is Nothing Then Exit Sub If getTransactionParameters(paramName, dSets) Then If Not extraJoins(dSets) Then Exit Sub With dSets.DataSet(cTransactions) 'clone that If .bigCommit(wholeSheet(dSets.DataSet(cName).Cell(cJoin, cSheet).toString), True) > 0 Then ' add some new columns if not already existing With dSets.init(wholeSheet(dSets.DataSet(cName).Cell(cJoin, cSheet).toString), , jExtend, , , True) ' adding all new columns that need to be cloned Set r = lastCell(.HeadingRow.Where) For Each dr In dSets.DataSet(cCopyFields).Rows ' does it exist in the current set? sKey = dr.Cell(cCopyFields).toString a = Split(sKey, "=") If (UBound(a) > LBound(a)) Then sKey = a(0) If .HeadingRow.Exists(sKey) Is Nothing Then Set r = r.Offset(, 1) r.Value = sKey End If Next dr End With Else MsgBox ("there was no data in " & cTransactions) End If End With ' read the completed data set structure with the extra headings back in With dSets.init(dSets.DataSet(jExtend).Where, , , , , True) 'clone evyerthing For Each dr In .Rows For Each drc In dSets.DataSet(cCopyFields).Rows ' the name of the column we want to touch sField = drc.Cell(cCopyFields).toString sOut = sField a = Split(sField, "=") ' there was a syntax like masterid=transid If (UBound(a) > LBound(a)) Then sField = a(1) sOut = a(0) End If ' the master file to find it in sMaster = drc.Cell(cCloneFrom).toString ' the key to match on sKey = dSets.DataSet(cName).Cell(sMaster, cJoin).toString a = Split(sKey, "=") ' there was a syntax like masterid=transid If (UBound(a) > LBound(a)) Then sKey = a(1) ' the target value of the key sId = dr.Cell(sKey).toString ' clear it out dr.Cell(sOut).Value = Empty ' get the matching row Set drk = dSets.DataSet(sMaster, True).Row(sId) ' maybe there is no match If (drk Is Nothing) Then MsgBox ("could not find value " & sId & " on Key " & sKey & _ " in dataset " & sMaster) Else Set cc = drk.Cell(sField) If cc Is Nothing Then MsgBox ("column " & sField & " does not exist in dataset " & cMaster) Else dr.Cell(sOut).Value = drk.Cell(sField).Value End If End If Next drc Next dr .bigCommit End With End If Set dSets = Nothing End Sub Private Function extraJoins(dSets As cDataSets) As Boolean ' we have to open additional dset for each copyfield not already known Dim dr As cDataRow, S As String, k As String, n As String, a As Variant extraJoins = False With dSets ' goes through the list of joind and creates a dataset from each corresponding sheet For Each dr In .DataSet(cCopyFields).Rows ' this is the field name n = dr.Cell(cCloneFrom).toString If .DataSet(n) Is Nothing Then ' means it doesnt yet exist - this would be the sheet name and key field its from With .DataSet(cName) S = .Cell(dr.Cell(cCloneFrom).toString, cSheet).toString k = .Cell(dr.Cell(cCloneFrom).toString, cJoin).toString End With a = Split(k, "=") If .init(wholeSheet(S), , n, True, , True, CStr(a(0))) Is Nothing Then Exit Function End If Next dr End With ' that went well extraJoins = True End Function
Questions, feedback and VizMap applications you have generated can be submitted for loading to this site via our forum. Now let’s look at Vba to generate VizMap Application