cParseCom examples - Desktop Liberation

Update Nov2017

UPDATE: parse.com as now been closed and moved to parseplatform.org. I will update the content of this page an move the back-end to this platform

In parse.com – nosql database for VBA, I described how to use cParseCom to be able to use parse.com directly from VBA. You can find the implementation details here parse.com api class for VBA

Here’s a few examples – we’ll walk through the code shown below

Page Content hide

1 The Code

2 Initial authentication

3 Populate from a sheet – walkthrough

4 Getting credentials from the registry

5 Deleting objects

6 Getting Data

7 Clearing up

8 Get an object by its unique ID – walkthrough

9 Do a query- walkthrough

10 Counting objects – walkthrough

11 Updating objects – walkthrough

12 A small application – walkthrough

13 Get involved

The Code

Option Explicit
'v1.2
Private Sub firstTimeParseCom()
    Dim parseCom As cParseCom
    Set parseCom = New cParseCom
    With parseCom.init("ColorTable", , , "your app id", _
                "your restapikey")
        .tearDown
    End With
End Sub

Private Sub populates()
    ' copy two sheets to parse.com
    populateFromSheet "VBAParseCustomers"
    populateFromSheet "VBAParseData"
    
End Sub
Private Sub populateFromSheet(sheetName As String)
    Dim parseCom As cParseCom, job As cJobject, dset As cDataSet
    
    ' this will clear out an existing parse class, and create a new one from a worksheet
    ' we'll use batch mode throughout
    
    Set parseCom = getParsed(sheetName).batch
    
    ' clear out existing any existing data
    parseCom.deleteObjects

    'get the data from the sheet and populate database
    Set dset = New cDataSet
    With dset.populateData(wholeSheet(sheetName), , , , , , True).jObject(, True, True)
        For Each job In .children
            With parseCom.createObject(job)
                ' clear this error handling up
                Debug.Assert .isOk
            End With
        Next job
        ' clear up
        .tearDown
    End With
    
    ' commit any outstanding  and clean up
    With parseCom.flush.batch(False)
        Debug.Assert .isOk
        ' show how many are there now
        Debug.Print .count & " in class" & sheetName
        .tearDown
    End With

End Sub

Private Sub testGetItemByUniqueId()

    ' get an item by unique object ID
    With getParsed("VBAParseCustomers")
        .getObjectById ("SmnyjZKs9m")
        ' test if it worked, and do something with the results
        If .isOk Then
            Debug.Print .jObject.stringify(True)
        Else
            Debug.Print "failed to get object:" & .browser.url & ":" & .browser.status & ":" & .browser.Text
        End If
        .tearDown
    End With

End Sub
Private Sub testparseUpdate()

    ' get some items by query and change the scheme name to something else
    With getParsed("VBAParseData").batch
        With .updateObjects(JSONParse("{'customerid':39}"), JSONParse("{'customerid':1}"))
            ' test if it worked, and do something with the results
            If .isOk Then
                Debug.Print "all is good", .jObject.stringify
            Else
                Debug.Print "failed to update:" & .browser.url & ":" & .browser.status & ":" & .browser.Text
            End If
        End With
        .flush.tearDown
    End With

End Sub

Private Sub testparsequery()
    
    ' get a number of items that match a query by example
    With getParsed("VBAParseData")
        With .getObjectsByQuery(JSONParse("{'customerid':1}"), JSONParse("{'limit':2}"))
            'test if it worked, and do something with the results
            If .isOk Then
                Debug.Print "all is ok", .jObject.stringify(True)
            Else
                Debug.Print "failed to do query:" & .browser.url & ":" & .browser.status & ":" & .browser.Text
            End If
        End With
        .tearDown
    End With
    
End Sub
Private Sub testparseCount()
    
    ' get a number of items that match a query by example
    Debug.Print getParsed("VBAParseData").count(JSONParse("{'customerid':1}"))
    
End Sub

Private Sub parseMatch()
    Dim pCustomer As New cParseCom, pData As cParseCom, job As cJobject, joc As cJobject, queryJob As cJobject
    ' look up data in another table based on data in another
    
    ' data abot the pantone colors of the year
    Set pCustomer = getParsed("VBAParseCustomers")
    Set pData = getParsed("VBAParseData")
    
    ' set up a query by example, restricting to a particular customer
    Set queryJob = New cJobject
    queryJob.init(Nothing).add "country", "United States"
        
    ' go through all matching customers
    With pCustomer.getObjectsByQuery(queryJob)
        If .isOk Then
            With .jObject.child("results")
                For Each job In .children
                    With pData.getObjectsByQuery(job.child("customerid"))
                        If .isOk Then
                            With .jObject.child("results")
                                For Each joc In .children
                                    Debug.Print job.toString("country"), job.toString("name"), job.child("customerid").value, joc.child("invoiceid").value
                                Next joc
                            End With
                        End If
                    End With
                Next
            End With
        End If
    End With

    
    ' clean up
    queryJob.tearDown
    pCustomer.tearDown
    pData.tearDown
    
End Sub
Public Function getParsed(parseClass As String) As cParseCom
    Dim p As cParseCom
    Set p = New cParseCom
    Set getParsed = p.init(parseClass)
    
End Function

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

Option Explicit

'v1.2

Private Sub firstTimeParseCom()

Dim parseCom As cParseCom

Set parseCom = New cParseCom

With parseCom.init("ColorTable", , , "your app id", _

"your restapikey")

.tearDown

End With

End Sub

Private Sub populates()

' copy two sheets to parse.com

populateFromSheet "VBAParseCustomers"

populateFromSheet "VBAParseData"

End Sub

Private Sub populateFromSheet(sheetName As String)

Dim parseCom As cParseCom, job As cJobject, dset As cDataSet

' this will clear out an existing parse class, and create a new one from a worksheet

' we'll use batch mode throughout

Set parseCom = getParsed(sheetName).batch

' clear out existing any existing data

parseCom.deleteObjects

'get the data from the sheet and populate database

Set dset = New cDataSet

With dset.populateData(wholeSheet(sheetName), , , , , , True).jObject(, True, True)

For Each job In .children

With parseCom.createObject(job)

' clear this error handling up

Debug.Assert .isOk

End With

Next job

' clear up

.tearDown

End With

' commit any outstanding and clean up

With parseCom.flush.batch(False)

Debug.Assert .isOk

' show how many are there now

Debug.Print .count & " in class" & sheetName

.tearDown

End With

End Sub

Private Sub testGetItemByUniqueId()

' get an item by unique object ID

With getParsed("VBAParseCustomers")

.getObjectById ("SmnyjZKs9m")

' test if it worked, and do something with the results

If .isOk Then

Debug.Print .jObject.stringify(True)

Else

Debug.Print "failed to get object:" & .browser.url & ":" & .browser.status & ":" & .browser.Text

End If

.tearDown

End With

End Sub

Private Sub testparseUpdate()

' get some items by query and change the scheme name to something else

With getParsed("VBAParseData").batch

With .updateObjects(JSONParse("{'customerid':39}"), JSONParse("{'customerid':1}"))

' test if it worked, and do something with the results

If .isOk Then

Debug.Print "all is good", .jObject.stringify

Else

Debug.Print "failed to update:" & .browser.url & ":" & .browser.status & ":" & .browser.Text

End If

End With

.flush.tearDown

End With

End Sub

Private Sub testparsequery()

' get a number of items that match a query by example

With getParsed("VBAParseData")

With .getObjectsByQuery(JSONParse("{'customerid':1}"), JSONParse("{'limit':2}"))

'test if it worked, and do something with the results

If .isOk Then

Debug.Print "all is ok", .jObject.stringify(True)

Else

Debug.Print "failed to do query:" & .browser.url & ":" & .browser.status & ":" & .browser.Text

End If

End With

.tearDown

End With

End Sub

Private Sub testparseCount()

' get a number of items that match a query by example

Debug.Print getParsed("VBAParseData").count(JSONParse("{'customerid':1}"))

End Sub

Private Sub parseMatch()

Dim pCustomer As New cParseCom, pData As cParseCom, job As cJobject, joc As cJobject, queryJob As cJobject

' look up data in another table based on data in another

' data abot the pantone colors of the year

Set pCustomer = getParsed("VBAParseCustomers")

Set pData = getParsed("VBAParseData")

' set up a query by example, restricting to a particular customer

Set queryJob = New cJobject

queryJob.init(Nothing).add "country", "United States"

' go through all matching customers

With pCustomer.getObjectsByQuery(queryJob)

If .isOk Then

With .jObject.child("results")

For Each job In .children

With pData.getObjectsByQuery(job.child("customerid"))

If .isOk Then

With .jObject.child("results")

For Each joc In .children

Debug.Print job.toString("country"), job.toString("name"), job.child("customerid").value, joc.child("invoiceid").value

Next joc

End With

End If

End With

End If

End With

' clean up

queryJob.tearDown

pCustomer.tearDown

pData.tearDown

End Sub

Public Function getParsed(parseClass As String) As cParseCom

Dim p As cParseCom

Set p = New cParseCom

Set getParsed = p.init(parseClass)

End Function

Initial authentication

Before you can start, you need to get your own credentials from parse.com. It’s free and easy. As described in parse.com – nosql database for VBA, once you have those you should register yourself on this PC. You won’t need to run this again after the first time.

Private Sub firstTimeParseCom()
    Dim parseCom As cParseCom
    Set parseCom = New cParseCom
    With parseCom.init("ColorTable", , , "your app id", _
                "your restapikey")
        .tearDown
    End With
End Sub

Private Sub firstTimeParseCom()

Dim parseCom As cParseCom

Set parseCom = New cParseCom

With parseCom.init("ColorTable", , , "your app id", _

"your restapikey")

.tearDown

End With

End Sub

Afterwards we use getParsed(‘classname’), which looks like this.

Public Function getParsed(parseClass As String) As cParseCom
    Dim p As cParseCom
    Set p = New cParseCom
    Set getParsed = p.init(parseClass)
    
End Function

Public Function getParsed(parseClass As String) As cParseCom

Dim p As cParseCom

Set p = New cParseCom

Set getParsed = p.init(parseClass)

End Function

Populate from a sheet – walkthrough

Private Sub populateFromSheet(sheetName As String)
    Dim parseCom As cParseCom, job As cJobject, dset As cDataSet
    
    ' this will clear out an existing parse class, and create a new one from a worksheet
    ' we'll use batch mode throughout
    
    Set parseCom = getParsed(sheetName).batch
    
    ' clear out existing any existing data
    parseCom.deleteObjects

    'get the data from the sheet and populate database
    Set dset = New cDataSet
    With dset.populateData(wholeSheet(sheetName), , , , , , True).jObject(, True, True)
        For Each job In .children
            With parseCom.createObject(job)
                ' clear this error handling up
                Debug.Assert .isOk
            End With
        Next job
        ' clear up
        .tearDown
    End With
    
    ' commit any outstanding  and clean up
    With parseCom.flush.batch(False)
        Debug.Assert .isOk
        ' show how many are there now
        Debug.Print .count & " in class" & sheetName
        .tearDown
    End With

End Sub

Private Sub populateFromSheet(sheetName As String)

Dim parseCom As cParseCom, job As cJobject, dset As cDataSet

' this will clear out an existing parse class, and create a new one from a worksheet

' we'll use batch mode throughout

Set parseCom = getParsed(sheetName).batch

' clear out existing any existing data

parseCom.deleteObjects

'get the data from the sheet and populate database

Set dset = New cDataSet

With dset.populateData(wholeSheet(sheetName), , , , , , True).jObject(, True, True)

For Each job In .children

With parseCom.createObject(job)

' clear this error handling up

Debug.Assert .isOk

End With

Next job

' clear up

.tearDown

End With

' commit any outstanding and clean up

With parseCom.flush.batch(False)

Debug.Assert .isOk

' show how many are there now

Debug.Print .count & " in class" & sheetName

.tearDown

End With

End Sub

Getting credentials from the registry

Before we can do anything, we have to pick up the encrypted credentials we stored as a one off exercise. The should be one instance of cParseCom for every parse.com class – in this case I’m creating or updating a class with the same name as the sheet I’m importing .Adding .batch() will set this instance to batch Mode. That means that rest Queries will be automatically batched up as much as possible to minimize fetches.

Set parseCom = getParsed(sheetName).batch

1	Set parseCom = getParsed(sheetName).batch

Deleting objects

Since we are populating sheets, we want to empty out the current contents. deleteObjects with no arguments will do that

parseCom.deleteObjects

1	parseCom.deleteObjects

Getting Data

We can use cDataSet (see How to use cDataSet) to minimize playing around with spreadsheet contents. It also has a useful property (.JObject) which returns a useful JSON friendly cJobject (see How to use cJobject), each child of which represents a worksheet row, already in the correct format for createObject() to create a row in the parse.com class database.

'get the data from the sheet and populate database
    Set dset = New cDataSet
    With dset.populateData(wholeSheet(sheetName), , , , , , True).jObject(, True, True)
        For Each job In .children
            With parseCom.createObject(job)
                ' clear this error handling up
                Debug.Assert .isOk
            End With
        Next job
        ' clear up
        .tearDown
    End With

'get the data from the sheet and populate database

Set dset = New cDataSet

With dset.populateData(wholeSheet(sheetName), , , , , , True).jObject(, True, True)

For Each job In .children

With parseCom.createObject(job)

' clear this error handling up

Debug.Assert .isOk

End With

Next job

' clear up

.tearDown

End With

Clearing up

As with most classes on this site, cParseCom features a .tearDown function which ensures that any memory it is using gets cleaned up. Since we using batch Mode, we have to flush, before exiting to clear any outstanding requests. Finally .count() will show how many rows are in the newly created table

' commit any outstanding  and clean up
    With parseCom.flush.batch(False)
        Debug.Assert .isOk
        ' show how many are there now
        Debug.Print .count & " in class" & sheetName
        .tearDown
    End With

' commit any outstanding and clean up

With parseCom.flush.batch(False)

Debug.Assert .isOk

' show how many are there now

Debug.Print .count & " in class" & sheetName

.tearDown

End With

Get an object by its unique ID – walkthrough

Private Sub testGetItemByUniqueId()

    ' get an item by unique object ID
    With getParsed("VBAParseCustomers")
        .getObjectById ("3RGpNBMncB")
        ' test if it worked, and do something with the results
        If .isOk Then
            Debug.Print .jObject.stringify
        Else
            Debug.Print "failed to get object:" & .browser.url & ":" & .browser.status & ":" & .browser.Text
        End If
        .tearDown
    End With

End Sub

Private Sub testGetItemByUniqueId()

' get an item by unique object ID

With getParsed("VBAParseCustomers")

.getObjectById ("3RGpNBMncB")

' test if it worked, and do something with the results

If .isOk Then

Debug.Print .jObject.stringify

Else

Debug.Print "failed to get object:" & .browser.url & ":" & .browser.status & ":" & .browser.Text

End If

.tearDown

End With

End Sub

Each object (row) has a unique ID. Here’s how to get it (you’ll need to look at your parse.com dashboard to get a valid objectID for the data you’ve just loaded).

getParsed("VBAParseCustomers").getObjectById ("3RGpNBMncB")

1	getParsed("VBAParseCustomers").getObjectById ("3RGpNBMncB")

which, when you do this;

Debug.Print .jObject.stringify(True)

1	Debug.Print .jObject.stringify(True)

looks like this

 {
      "address":"584-5478 Et Road",
      "city":"Hastings",
      "company":"Eu Accumsan Sed Inc.",
      "coordinates":"38.2264, 75.04849",
      "country":"Comoros",
      "customerid":100,
      "email":"tincidunt.nibh@Curabitur.net",
      "name":"Contreras",
      "region":"NI",
      "zip":"12396",
      "createdAt":"2013-11-26T14:36:40.517Z",
      "updatedAt":"2013-11-26T14:36:40.517Z",
      "objectId":"SmnyjZKs9m"
   }

{

"address":"584-5478 Et Road",

"city":"Hastings",

"company":"Eu Accumsan Sed Inc.",

"coordinates":"38.2264, 75.04849",

"country":"Comoros",

"customerid":100,

"email":"tincidunt.nibh@Curabitur.net",

"name":"Contreras",

"region":"NI",

"zip":"12396",

"createdAt":"2013-11-26T14:36:40.517Z",

"updatedAt":"2013-11-26T14:36:40.517Z",

"objectId":"SmnyjZKs9m"

}

Do a query- walkthrough

Queries in parse are by example. You can see the details here. You can set up any query by making a cJobject with the filters required. Here Im using JSONParse to create a jobject which will select all objects with customerid = 1.

.getObjectsByQuery(JSONParse("{'customerid':1}"))

1	.getObjectsByQuery(JSONParse("{'customerid':1}"))

Here’s the whole thing,

Private Sub testparsequery()
    
    ' get a number of items that match a query by example
    With getParsed("VBAParseData")
        With .getObjectsByQuery(JSONParse("{'customerid':1}"))
            'test if it worked, and do something with the results
            If .isOk Then
                Debug.Print "all is ok", .jObject.stringify(True)
            Else
                Debug.Print "failed to do query:" & .browser.url & ":" & .browser.status & ":" & .browser.Text
            End If
        End With
        .tearDown
    End With
    
End Sub

Private Sub testparsequery()

' get a number of items that match a query by example

With getParsed("VBAParseData")

With .getObjectsByQuery(JSONParse("{'customerid':1}"))

'test if it worked, and do something with the results

If .isOk Then

Debug.Print "all is ok", .jObject.stringify(True)

Else

Debug.Print "failed to do query:" & .browser.url & ":" & .browser.status & ":" & .browser.Text

End If

End With

.tearDown

End With

End Sub

Which returns something that starts like this.

{
      "results":[         {
            "customerid":1,
            "quantity":9,
            "value":4230,
            "invoiceid":145,
            "date":{
               "__type":"Date",
               "iso":"2013-10-08T00:00:00.000Z"
            },
            "createdAt":"2013-11-26T14:36:43.286Z",
            "updatedAt":"2013-11-26T14:36:43.286Z",
            "objectId":"TXOAXukaLS"
         },
         {
            "customerid":1,
            "quantity":3,
            "value":1560,
            "invoiceid":151,
            "date":{
               "__type":"Date",
               "iso":"2013-05-21T00:00:00.000Z"
            },
            "createdAt":"2013-11-26T14:36:43.430Z",
            "updatedAt":"2013-11-26T14:36:43.430Z",
            "objectId":"8IF3UNIy69"
         },

{

"results":[ {

"customerid":1,

"quantity":9,

"value":4230,

"invoiceid":145,

"date":{

"__type":"Date",

"iso":"2013-10-08T00:00:00.000Z"

"createdAt":"2013-11-26T14:36:43.286Z",

"updatedAt":"2013-11-26T14:36:43.286Z",

"objectId":"TXOAXukaLS"

{

"customerid":1,

"quantity":3,

"value":1560,

"invoiceid":151,

"date":{

"__type":"Date",

"iso":"2013-05-21T00:00:00.000Z"

"createdAt":"2013-11-26T14:36:43.430Z",

"updatedAt":"2013-11-26T14:36:43.430Z",

"objectId":"8IF3UNIy69"

Counting objects – walkthrough

The .count() function will take the same arguments as getObjectsByQuery() and return the number of objects that match

Private Sub testparseCount()
    
    ' get a number of items that match a query by example
    Debug.Print getParsed("VBAParseData").count(JSONParse("{'customerid':1}"))
    
End Sub

Private Sub testparseCount()

' get a number of items that match a query by example

Debug.Print getParsed("VBAParseData").count(JSONParse("{'customerid':1}"))

End Sub

Updating objects – walkthrough

The .updateObjects() function will take the same arguments as getObjectsByQuery() and return the objects that match, and then update any fields in those objects mentioned in the second argument; so this updates the customerId for all objects belonging to customerId 39 form 39 to 1.

.updateObjects(JSONParse("{'customerid':39}"), JSONParse("{'customerid':1}"))

1	.updateObjects(JSONParse("{'customerid':39}"), JSONParse("{'customerid':1}"))

Here’s the whole thing. Note that I specify .batch() here. Updates, Creates and Deletes can, and should be batched for better performance. And when you batch, don’t forget to flush to commit the updates.

Private Sub testparseUpdate()

    ' get some items by query and change the scheme name to something else
    With getParsed("VBAParseData").batch
        With .updateObjects(JSONParse("{'customerid':39}"), JSONParse("{'customerid':1}"))
            ' test if it worked, and do something with the results
            If .isOk Then
                Debug.Print "all is good", .jObject.stringify
            Else
                Debug.Print "failed to update:" & .browser.url & ":" & .browser.status & ":" & .browser.Text
            End If
        End With
        .flush.tearDown
    End With

End Sub

Private Sub testparseUpdate()

' get some items by query and change the scheme name to something else

With getParsed("VBAParseData").batch

With .updateObjects(JSONParse("{'customerid':39}"), JSONParse("{'customerid':1}"))

' test if it worked, and do something with the results

If .isOk Then

Debug.Print "all is good", .jObject.stringify

Else

Debug.Print "failed to update:" & .browser.url & ":" & .browser.status & ":" & .browser.Text

End If

End With

.flush.tearDown

End With

End Sub

A small application – walkthrough

In this example, we’ll find any customers in United States, look up their orders, and print out some data about them.

We need 2 instances of cParseCom

    Set pCustomer = getParsed("VBAParseCustomers")
    Set pData = getParsed("VBAParseData")

1 2	Set pCustomer = getParsed("VBAParseCustomers") Set pData = getParsed("VBAParseData")

Set up a query for US

 Set queryJob = New cJobject
 queryJob.init(Nothing).add "country", "United States"

1 2	Set queryJob = New cJobject queryJob.init(Nothing).add "country", "United States"

Find Them

With pCustomer.getObjectsByQuery(queryJob)

1	With pCustomer.getObjectsByQuery(queryJob)

Look through each one

With .jObject.child("results")
       For Each job In .children

1 2	With .jObject.child("results") For Each job In .children

Do a query on their orders

 With pData.getObjectsByQuery(job.child("customerid"))

1	With pData.getObjectsByQuery(job.child("customerid"))

Print out each of their orders

With .jObject.child("results")
        For Each joc In .children
            Debug.Print job.toString("country"), job.toString("name"), job.child("customerid").value, joc.child("invoiceid").value
        Next joc
    End With

With .jObject.child("results")

For Each joc In .children

Debug.Print job.toString("country"), job.toString("name"), job.child("customerid").value, joc.child("invoiceid").value

Next joc

End With

Here’s the whole thing

Private Sub parseMatch()
    Dim pCustomer As New cParseCom, pData As cParseCom, job As cJobject, joc As cJobject, queryJob As cJobject
    ' look up data in another table based on data in another
    
    ' data abot the pantone colors of the year
    Set pCustomer = getParsed("VBAParseCustomers")
    Set pData = getParsed("VBAParseData")
    
    ' set up a query by example, restricting to a particular customer
    Set queryJob = New cJobject
    queryJob.init(Nothing).add "country", "United States"
        
    ' go through all matching customers
    With pCustomer.getObjectsByQuery(queryJob)
        If .isOk Then
            With .jObject.child("results")
                For Each job In .children
                    With pData.getObjectsByQuery(job.child("customerid"))
                        If .isOk Then
                            With .jObject.child("results")
                                For Each joc In .children
                                    Debug.Print job.toString("country"), job.toString("name"), job.child("customerid").value, joc.child("invoiceid").value
                                Next joc
                            End With
                        End If
                    End With
                Next
            End With
        End If
    End With

    
    ' clean up
    queryJob.tearDown
    pCustomer.tearDown
    pData.tearDown
    
End Sub

Private Sub parseMatch()

Dim pCustomer As New cParseCom, pData As cParseCom, job As cJobject, joc As cJobject, queryJob As cJobject

' look up data in another table based on data in another

' data abot the pantone colors of the year

Set pCustomer = getParsed("VBAParseCustomers")

Set pData = getParsed("VBAParseData")

' set up a query by example, restricting to a particular customer

Set queryJob = New cJobject

queryJob.init(Nothing).add "country", "United States"

' go through all matching customers

With pCustomer.getObjectsByQuery(queryJob)

If .isOk Then

With .jObject.child("results")

For Each job In .children

With pData.getObjectsByQuery(job.child("customerid"))

If .isOk Then

With .jObject.child("results")

For Each joc In .children

Debug.Print job.toString("country"), job.toString("name"), job.child("customerid").value, joc.child("invoiceid").value

Next joc

End With

End If

End With

End If

End With

' clean up

queryJob.tearDown

pCustomer.tearDown

pData.tearDown

End Sub

And the results

United States Hammond 85 161

United States Nash 97 282

United States Nash 97 391

Get involved

See parse.com – nosql database for VBA for more on this topic

For help and more information join our forum, follow the blog or follow me on Twitter