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

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
  1. venuesParameters. The specific parameter sheet describing the required application.
  2. geoCodingParameters. The common parameter sheet describing how to geoCode and other common parameter blocks
  3. venueMaster. The list of known venues. This contains data about the venue, included the geoCoded addresses.
  4. artistMaster. This is a lookup sheet that contains information about the artists that will be performing at particular venues
  5. 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
  6. 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.
  1. Standard parameter setup , dSetsSetup
  2. Pick up additional parameters, specific to the transaction data, that is only needed for this exercise getTransactionParameters.
  3. Find any extra look up worksheets referenced (in this case the artistmaster), extraJoins.
  4. Copy all the transactions from venuesTransactions to venuesMapping, bigCommit.
  5. Add all the fields mentioned in the clone block are added
  6. A new dSet is created, now containing all the columns to be cloned, plus the data from the original transactions
  7. 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
  8. Commit all the cloned data, .bigcommit

joiningExample complete code.

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