+ Start a Discussion
rgrantrgrant 

VBA Macro access to Excel plug-in

Can you access the salesforce.com plug from VBA Macros? If so, what are the subroutine/function names in the project and what are their parameters...

I would like (from a macro):

1) Login to sfdc
2) Refresh ALL reports (or even use Refresh Selected Reports and select the reports by name)

Richard
deepblueseadeepbluesea
Richard,

(1) open the VBA code window in Excel
(2) add a reference to SFDCExcelAddin
(3) browse the object explorer
(4) scan the classes, especially "CommandBarRelated"; here you will find the methods "Login", "IsLogedIn", "RefreshAll" and many others.

To use these methods in VB macros, you must wrap them in VBA code, such as:

Public Function VBLogin() As Boolean

SFDCExcelAddin.Login
VBLogin= SFDCExcelAddin.IsLoggedIn() 'check if login was successfull

End Function

Hope this helps.
Erik
rgrantrgrant
Thanks Erik for the quick response...

How does the SFDCExcelAddin.Login work without Username and password as parameters?

Richard
rgrantrgrant
Erik-

Also I don't see SFDCExcelAddin just SalesForceDotCom then in the object explorer only SFDCSpreadsheet??\

Richard
deepblueseadeepbluesea
Richard,

SFDCExcelAddin.Login simply opens the login dialog and prompts you for your credentials. The actual sfdc login is called behind the scenes, in (private) routines not published to the Interface. If you want to avoid reentering your credentials with every function call, then modify the code as follows:

Function SFDC_Login() as Boolean
If not sfdcExcelAddin.IsLoggedIn then sfdcExcelAddin.Login
SFDC_Login = sfdcExcelAddin.IsLoggedIn
End Function

Logging out is just as straightforward:

Function SFDC_Logout() as Boolean
If sfdcExcelAddin.IsLoggedIn then sfdcExcelAddin.Logout
SFDC_Logout = Not sfdcExcelAddin.IsLoggedIn
End Function

Refreshing all reports involves the first function:

Function SFDC_RefreshAll() as Boolean

If SFDC_Login() Then
SFDCExcelAddin.RefreshAll
Else
MsgBox "Login failed", vbExclamation
End If

End Function

Cheers,
Erik
deepblueseadeepbluesea
Richard,

(1) have you installed Office Edition?
(2) did you add the reference to SFDCExcelAddin in the VBA code Window?

Adding a reference: I have a german Excel, but I think in english the menu item should be "Tools"\"Add Reference" or something comparable; in my Excel 2000, it's the first menu item in the 8th menu. You are then prompted to pick references from a list, and with the Office Edition properly installed, SFDCExcelAddin should be in that list. If so, then click on the checkbox and then click on "OK". Once you've done this, you should be able to pick the library "SFDCExcelAddin" in the object browser and brwose it's contents.

Pls let me know if you can manage.

Cheers,
Erik
rgrantrgrant
You are being very helpful..

1) I may have an older version installed...

2) I really want to enter username and password via code and avoid the dialogue screen...any thoughts?

Richard
deepblueseadeepbluesea
Richard,

as I said, the tool doesn't let you call the login routine directly (well, at least I haven't found it). I haven't tried logging in with routines provided by other tools (such as the sforce connector) and then calling the SFDCExcelAddin routine, but I don't really think this would work. Maybe you should ask Ron Hess, he's extremely knowledgable in these things...

Or ask the developers to provide in the next release a login routine that accepts credentials...

Sorry for not being of any real help in this,
Erik
Ron HessRon Hess
The code example to do a login using the COM Office toolkit is inside the Excel (Sforce ) Connector, install this and then browse the source. feel free to copy it , look for the module Login, there is a fuction validate()
which calls into CSession class module to make the login() call into the toolkit.
you can browse the toolkit using the object browser, the Reference dialog lists the libraries used, the one you want to
browse is SForceOfficeToolkit3 v3.0
download instructions are here: http://sforce.sourceforge.net/excel/index.htm
rgrantrgrant
Firstly thanks to both of you for all the help....

Ron-

Let me just tell you quickly what I want to do:
1) I am going to create a spreadsheet that has imported reports from sf.com
2) On subsequent opening of the spreadsheet I want to execute from Excel VBA a refresh of all the reports without any user involement
3) This means I have to programatically login to sf.com bypassing the login screen (so I will have to pass username and password to a subroutine.)
4) Then programmatically do a Refresh All

I think I am hearing you say that I have to install the LATEST Excel plugin AND the sforce connector. Login using sforce connector code as you describe in your response and then what. Use Erik's technique for doing a Refresh All?

If this is true I have one problem....I submitted two cases a while back #00478411 and #0045811...both of these involved acknowledged bugs in the Excel plug-in. This is why I am still using version 1.3.1.10. Do you know if these have been fixed....If yes, then I can upgrade to the latest plugin and connector. The new connector does NOT work with the old plug in. If no, do you know when they will be fixed. BTW, is there a way to check the status on my own.

Isn't there another option? If you either give me the password to unlock the SalesForceDotCom (SFDC.xla) project or tell me what subroutines (with parameters) are being called from the plugin forms...I can then call these routine from my code? This seems like the easiest solution, since what I want to do is very simple.

Sorry for the long message and many questions....But resolution on this issue will be extremely helpful to me and my clients.

Richard
Ron HessRon Hess
i think i'm starting to understand, i've never done this

the two add-in's are not related in any way except that they use the same COM add-in.
I don't have any passwd to unlock the SFDC.xla, however i can ask the team if they are willing to make this open-source, the precident is there given that the loader is open-source.

there is no combination of SFDC and sforce_connect that can do exactly what you are describing.
if you built all the reports using sforce_connect query(), then you could, by stuffing username/passwd inside the code ( very bad practice) get them to refresh, however this will not generate the nice reports that you want (reports often have joined tables, connector operates on only one table)

now the good news,

You can do much more of what you want with the open-source dbamp project, this will allow you to pull data down into a local database and then build simple excel sheets which refresh on open because they use that local database as a "data srource".

dbAmp is going to be more robust, more flexable and more programable than any Excel and VBA based solution, if I understand the requirements of your problem.
rgrantrgrant
Totally agree with the "bad practice" comment...however, keep in mind I just want to automate the grabbing of some reports in excel...and if I do it through a VBA project and password protect that I am still "protecting" the login info.

I do understand the issue re: api vs. excel. Grabbing many tables then loading up a database, doing all the joins just to replicate a report is a lot of work for little gain. So being able access the excel plugin project for login and refresh all would be GREAT.

BTW- where is the dbAmp project I will look into that solution...could you send me the url.

Looking forward to a "positive" answer to opening up the SFDC.xla.

Richard
Ron HessRon Hess
http://sourceforge.net/projects/dbamp/

even if you don't have mssql, you can download the ms sql express beta for free ( from MS)
ScotScot
Note:
As pointed out here, there is a major problem when you try to automate the refresh of reports using the Office Edition. (not the Office Edition Toolkit).

The problem is that the office edition code, which is protected, does not expose parameters on the login call. If these were exposed, then it would be a piece of cake to perform this functionality. As a result, you'd be able to build an Excel workbook with a refresh button...

My request would be to expose them.
rgrantrgrant
Scot-

Well put.....I agree totally!!!

Ron- any word?

Richard
Ron HessRon Hess
answer is not at this time.

in general salesforce.com is not opposed to releasing sample and useful tools to open source, the loader is the example of this.

however, my understanding is that access to the reports via the API is a feature request (which several people have requested), therefore i am confident it will be addressed at some point in the future; Because this feature request is out there, there is no desire to release code now which may not be compat with the future vision for this feature.

So for now, i don't expect that the existing code (for SFDC.xla) base will be released.
sorry.

If you haven't filed a feature request for a report API, please do, the more requests for a feature, the more attention this can get.

so, i guess your best options are
use DbAmp from forceamp
modify sforce_connect.xla to generate the report you are looking for

both would work.
rgrantrgrant
Thanks Ron...not really what I wanted to hear but I understand.

Actually, working on using the Web Services API via Java to grab the appropriate SFDC tables into my DB.

Richard
jambujambu
Hi, method to logout is sfdcExcelAddin.Logoff
Just in case!
foghornfoghorn
If you interested, the next version of the salesforce offline edition handles db replication for you. As an extra bonus, you can run any sql query you’d like against it.

https://na1.salesforce.com/setup/offline/offlinesetup2.jsp
Mike Schinkel.ax196Mike Schinkel.ax196
Foghorn:

I just downloaded and installed the offline edition. It's a pretty nifty piece of software, generating a dynamic website locally w/o a local HTTP server; wow.

I noticed it installed a service called SForceDB.exe. Where does it store it's local data?
Ron HessRon Hess
the good news is you can use it (via COM)and you don't need to know where the data is...
catharsis50catharsis50

This is great thanks!

PankajTamrakarPankajTamrakar

Hey Richard,

 

I'm also looking to automate refreshing of data from SFDC.

 

Are you finally able to automate the whole process?

RohanBPRohanBP

Hi,

 

I would also like to pass a username and password to the SFDC add-in and log in programmatically.  Does anyone know if there is a solution for this yet?  Or does anyone have the VBA code to do this?

 

Thanks,

 

Rohan

Alex CaineAlex Caine
I'm unable to find SFDCexceladdin in the object explorer but I've installed everything I need to... I do see s_force in the object explorer. Can anyone help? Very frustrating
 
Altin Elezi 8Altin Elezi 8
Cdata Software has created The Salesforce Excel Add-In that is a powerful tool that allows you to connect with live Salesforce.com account data, directly from Microsoft Excel. Use Excel to read, write, and update Leads, Contacts, Opportunities, Accounts, etc. Perfect for mass imports / exports / updates, data cleansing & de-duplication, Excel based data analysis, and more!

http://cdn.cdata.com/help/RFB/xls/pg_excelmacroex.htm
https://www.cdata.com/kb/tech/salesforce-excel-cdataquery.rst