+ Start a Discussion
Ron HessRon Hess 

sforce Connector: new version 5.40

http://sforce.sourceforge.net/excel/downloads.htm

I got excited at the recent user group after talking with some of the folks using the connector to get day to day chores done quickly, so i went home and worked out a new feature.

One comment that came up was that inserting records one by one required you to watch over the process as one bad record would stop the bulk load.

After clarifying with Benji and Adam how the create() call works in the office toolkit i was able to refactor my create() code to easily pass a batch of rows to create(), then pick up the return values (id or fail msg) directly from the array which was constructed and passed in to create().

The user interface available within excel for delivering these messages required a bit of creativity, I used the "comment" object on the cells which contain the row which failed to insert. The msg is taken directly from the SObject returned by the toolkit.

The result is that one or several failed create calls will not stop the batch from proceeding. If there are failed create()'s then i message the user at the end of the operation, leaving "new" in the rows which failed, writing the object ID's where we got a successful record inserted.

fixed one other bug with nul group names, also fixed some references to custom objects which were passed into ref_id() and overwritten by a nul string.

The user groups are cool, check them out if you are able.
Force2b_MikeForce2b_Mike
Ron,

I could not live without your SForce Connector - Excellent work!!!

When I first started using this a few months back, I copied your classes into VB and have built a few engines that can upload and/or update SFC data from a local database. Someone else was looking for SFC login code for VB. Since my programs are based off of your original code, I wanted to check with you before I shared the code I've written.

Thanks,

Mike
Ron HessRon Hess

I'm glad it's working well for you.

yes, the VB source is in the public domain, you can share with others, thanks for asking.

It would be really great if you are in a position to share your derivative work with the comunity, I was recently looking for a way to load / unload a local db, which db do you work with?

can you share your code with us?

thanks

Force2b_MikeForce2b_Mike

Ron,

I'm happy to share my code. It's nothing spectacular, but it works - at least for what I'm doing. Currently, we use it to pump summary actual revenue history and timesheet data into custom objects in SFC on a daily basis. The process compares the data in the local table to data in the SFC object and then makes changes as needed - add/delete/update - to match the data. With the data in SFC, I'm able to have up to date dashboards and reports available to our users that previously would have required significant manual labor to create and email manually. I suppose that someone could update it to do the reverse - sychronize a local database with information from SFC.

The local database in this case is MS Access, mainly just because it's easy to work with for creating test queries, etc. It should be able to be switched to any ADO compatible database by changing the Connect String in the .INI file.

I've also written a couple of other utilities off of this same base code:

  • Sends eMail Reminders for Tasks. This was fairly simple to do. Just add a field called "Send eMail Reminder" to the Task screen with 5 possible values - blank, 5 Days Before, 4 Days Before, etc. This utility runs 4 times a day looking for tasks where a reminder email is needed and then sends it out. I have it running 4 times a day because we're a global company and I wanted to be sure that the reminder appears in our users eMail first thing in the morning no matter where they are around the world.
  • Opportunity Status Reminders. This sends an email to every user that owns opportunities or owns accounts with open opportunities (even if owned by someone else). The email lists all the opportunities, etc. with links to each. Doesn't work with Products yet, but I hope to add that in soon.

Anyway, below is a link to the genericized code for the DataUpdate logic.

Mike

http://www.kepner-tregoe.com/salesforce/SFC_VB6.zip

neyhotneyhot
is there a way I can login to saleforce from excel ? I want to be able to pull data automatically using excel.
DevAngelDevAngel

Hi neyhot,

Please check out the Excel Connector for salesforce.com.  This is a spreadsheet addin that will allow you to access salesforce.com data in the context of an Excel Workbook.

You can obtain this from here:

http://sforce.sourceforge.net/excel/downloads.htm

neyhotneyhot
Yes. im using the sforce connector in excel. How can I use the sforce connector so that the excel sheet automatically logs me into salesforce and pulls data.
Ron HessRon Hess

you will have to write a bit of VB code to pass your name and passwd into the login routine, then construct a query on one of your worksheets and call something like this:

sub AutoOpen () {

login(you,youpw)

Range("A1").select

sfQuery()

}

 

this can be done an a special macro called AutoExec or AutoOpen i believe

this code is not tested, you will have to work out the details like where the autoexec sub goes ( worksheet module i think) but the Select and Query bits work just like if you were clicking on a query range and then hitting the menu item in the connector.

still a bit of work for you to put the code togegher, but it's a nice feature request for the connector, i'll consider how to generalize something that can be run on open for a future release

 

tantan
Do I understand correctly that you can access cutom object tabels via the api? Can this be done with the Excel connector?

Thanks !
ScotScot
Yes (can access custom tables), and yes again (can do it with the Connector). Go for it!
martintrjchvmartintrjchv
Hi! I've just downloaded your code and reading the code.
Is there a documentation (Word, CHM or anyform ) ?

Regards,

Martín Trejo
Ron HessRon Hess

current doc is available via the help menu button, this takes you to :

http://sforce.sourceforge.net/excel/index.htm

please let me know if you find problems / issues or suggestions w.r.t. the doc or code.

martintrjchvmartintrjchv
Thank you for the answer!

Regards,

Martín Trejo Chávez
Ron HessRon Hess
Just posted the next version 5.48, some minor fixes, new wizard dialog, etc.

http://sforce.sourceforge.net/excel/downloads.htm