+ Start a Discussion
ScotScot 

Automating Excel extracts with Office Edition

The robust method for getting sf.com information into an Excel spread-sheet automatically will be -- when it is available -- the use of the API through the COM toolkit.  For simpler usage, however, it would be nice to automate the Office Edition report refreshes in an Excel Macro.

While not documented, it can be done ...
To do this:

a) Establish a reference to sfdc.xla in your project:
     a1. open vba
     a2. select the VBAProject for your workbook
     a3. open the menu tools/references, find and check "SFDCExcelAddin"

b) Build your macro, calling appropriate entries. An example, which refreshes all the reports in a workbook:

  Sub MyRefresh()
     If Not (SalesForceDotCom.g_LoggedOn) Then SFDCExcelAddin.CommandBarRelated.login
     SFDCExcelAddin.CommandBarRelated.RefreshAll
     SFDCExcelAddin.CommandBarRelated.Logoff
  End Sub

You can explore the entries using "View/Object Browser"; select "SFDCExcelAddin" from the pull-down menu at the top left; click on classes and members, and look for information at the bottom.

Updated Aug 2005: to match the newer version of Office Edition. The objects changed; the old version used references to  "SalesForceDotCom", and did not have the "CommandBarRelated" qualification.

Message Edited by Scot on 08-04-2005 09:48 AM

htalericohtalerico

Are there any methods that will tell you when the refresh of the report is complete?  When I kick off the RefreshAll method it starts the refresh and then I have a bunch of other code in a macro to take the data and format it.  The problem is that I start grabbing data and formatting it before the refresh has finished. 

Does anyone know of a way to tell when the refresh from Salesforce is complete?

Also, has anyone put any documentation together to explain the methods available?

ScotScot

Good question (know when the refresh has completed).

I don't know of a solution. If they are done sequentially (one at a time), you might look for results from a final, dummy, one. But I suspect that more than one are done in parallel.

I haven't seen any documentation yet.  I'm not sure if we will ...

Though not yet available, documentation for the sforce Office Edition Toolkit is expected and has been promised.

What we're using here, however, is the underlying "private" API used to implement the Office Edition menus. I think that they were designed as internal API's, and not for public usage. In other words, the functions they provide have not really be released.

Scot

 

Force2b_MikeForce2b_Mike
Scot,

I've tried using your code to login, but I can't find "SalesForceDotCom" as an object under Tools->References in Excel. I'm using the 1.3.1.0 version of the Office Toolkit.

Basically, I'm looking to write code that simply prompts the user login to SFC (via the SFC Office Toolkit), issues a Refresh, and then saves the data to a CSV file:

Sub RefreshData
' Code to login?
Selection.QueryTable.Refresh BackgroundQuery:=False
' Logout
ActiveWorkbook.Save
ActiveWorkbook.SaveAs "S:\forecast\export.csv", xlCSV
ActiveWorkbook.Close False
End Sub

Is there a way to do this?

Thanks,

Mike
ScotScot

Mike,

I think you're wanting to use the salesforce (not connector, not office toolkit) login. This was the one I was describing in my original posting.  It appears, however, that it has changed since I last used it.

Try looking for SFDCExcelAddin in place of SalesForceDotCom. If you point the visual basic object browser at that, you'll find Login and RefreshAll in the CommandBarRelated folder.

It's unfortunate that there is no published method for performing the login/refresh functions through VBA rather than the menu (though I'd be happy to be proven wrong ....)

Scot

Force2b_MikeForce2b_Mike
Scot,

Thanks. Here is the final code that seems to work great!!

SFDCExcelAddin.CommandBarRelated.login
Selection.QueryTable.Refresh BackgroundQuery:=False
ActiveWorkbook.Save
ActiveWorkbook.SaveAs "S:\forecast\export.csv", xlCSV
ActiveWorkbook.Close False

Mike