Apex Code Development You need to sign in to do that
Don't have an account?
rgrant 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
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
(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
How does the SFDCExcelAddin.Login work without Username and password as parameters?
Richard
Also I don't see SFDCExcelAddin just SalesForceDotCom then in the object explorer only SFDCSpreadsheet??\
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
(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
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
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
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
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
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.
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
even if you don't have mssql, you can download the ms sql express beta for free ( from MS)
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.
Well put.....I agree totally!!!
Ron- any word?
Richard
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.
Actually, working on using the Web Services API via Java to grab the appropriate SFDC tables into my DB.
Richard
Just in case!
https://na1.salesforce.com/setup/offline/offlinesetup2.jsp
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?
This is great thanks!
Hey Richard,
I'm also looking to automate refreshing of data from SFDC.
Are you finally able to automate the whole process?
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
http://cdn.cdata.com/help/RFB/xls/pg_excelmacroex.htm
https://www.cdata.com/kb/tech/salesforce-excel-cdataquery.rst