VBA Support

You can use VBA to work with the underlying SFDC database access functions of XL-Connector. You can’t record macros with XL-Connector buttons (it wouldn’t do you any good if you could…) so you have to work with VBA code in order to do automation with the SFDC data.

Before you start, you have to get the XL-Connector COM addin properly instantiated in your worksheet’s memory space. And that means putting this code at the top of your VBA module or in an “auto_open” sub for the spreadsheet. Here’s the code you need:

Don’t forget to make the automationObject public in the main declarations part of your module.

Before you begin writing code…

When you’re working with SFDC data through XL-Connector, everything is presented to you as arrays that are addressed only by tuple coordinates.

When you’re working with queries, remember it’s only SOQL syntax, not full SQL. So, forget about “SELECT *”. Use XL-Connector’s query-builder window to test any query string that you have, since the error messages you’ll get back from VBA will be misleading, or useless, or both.

You’d think that the catch-all “Variant” array data type would be fine swallowing a string array…but it isn’t. So pay attention when a method’s return array is shown as being strings.

When you get a dateTime string from a query, it will be in this format: YYYY-MM-DD HH:MM:SS, but when you want to query based on a dateTime, you need to use this one: YYYY-MM-DDTHH:MM:SSZ.

If your query has an empty result set, you will not get an array back from SFDC: you’ll get a null object that will cause error messages in your code if you try to do anything with it.

Pull report from SFDC by Id – returns a path to the saved CSV file (note, it does not put it into the open Excel worksheet…you pull it in with your own VBA…see example code lower down)

string RunReport(string id, out string errorText)

Refresh the data in the active worksheet

string Refresh(False)

Refresh the data all worksheets

string Refresh(True)

Run the Flow in a given worksheet

bool RunFlow(Worksheet ws, out error)

This function will run all active Flow steps in a given worksheet. If there’s an error, the function will return False and the error text will be in the ‘error’ output variable.

Run all Flows in the active workbook

bool RunAllFlows(out error)

This function will run all Flows in your document. If there’s an error, the function will return False and the error text will be in the ‘error’ output variable.Retrieve metadata files from Salesforce.com

This command will download all metadata files (the XML goodies you can see in Eclipse) of the specified object type and put them into the specified folder. Be careful, if you pull the same metadata files twice, the second time will overwrite the files pulled during the first time. Please note that if you are specifying standard object types, you will need to have “*” as the first item in the array (which gets you all the custom object definitions… don’t ask me why the SFDC guys did it that way…). The array that is returned by this call will contain the names of every metadata file pulled during the call.

The additionalTypes parameter is optional, if it’s omitted – Array(“*”) will be used.

Here’s a snippet of how to use this call:

Dim sObjects()
Dim sTypes()
sTypes = Array("CustomObject")
sObjects = Array("*", "Task", "Event", "Campaign", "CampaignMember", "Lead", "Account", "Contact", _
"Opportunity", "OpportunityLineItem", "Case", "Entitlements", "Contract", _
"Solutions", "Product", "Pricebook", "Ideas", "Question", "Reply", "Asset", "User")
' this is the list of standard objects, after the first * which indicates "get all custom objects"
Dim MDfiles() as String
MDfiles = automationObject.GetMetadata(sTypes, "C:\Temp", error, sObjects)
' MDfiles() is the array of file names that will be created, in this case, in the C:\Temp\Object directory
Dim fso As New FileSystemObject
Dim fname As TextStream
' do NOT use VBA's internal "Open File for Input" method, as the Input Line statement will draw in the
' entire file as a single line of text. Yes, really.
' Instead use the Microsoft Scripting Library methods that do more sensible things...you may need to add
' it to your References for the project.
Set fname = fso.OpenTextFile(MDfiles(0)) ' this is just an example of opening the first file in this list
Do While Not fname.AtEndOfStream ' this is fancy-talk for EOF
debug.print (fname.ReadLine) ' pull in the first line of text, as terminated by an LF (ASC-10) character
Loop
fname.Close ' do your housekeeping to avoid messes!