function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
ckellieckellie 

Simplifying the Excel Connector

I have several extensive and intricate excel workbooks in a variety of departments involved in developing quotes. I am wanting to connect these excel workbooks to Salesforce for my end user to download the desired information from Salesforce with clicking a button, then upload new information, amending/creating records. Various dataloaders require certain admin skills for successful use. I am wanting to simplify the process to pushing 1 button on the excel workbook. Are there any existing applications?

dontichdontich

I am trying to develop another VBA application to do this as well.  However, I am unable to access the VBA code associated with the connector.  Would you or anyone else have an idea where to get this?
fryguyfryguy

The connector plug in is password protected, but you can enable the project in VB and then call certain subroutines in your macro.

 

To enable, open your VB project, then click Tools on the menu, then References, then check-box the sforce connector and save the change.

 

In your macro, put sfQueryAll on its own line; that should run all the queries on the active sheet.

 

Other subroutines include:

 

sfUserName(user id)
given an sf user ID , returns the user's first name + last name. Very useful for owner, last modified by, etc.

 

 

sfUserId(fullname)
given a (first name + last name), returns the User ID
FixID(id)
given either a 15 or 18 character ID, returns an 18 character one

 

 

 QuarterNum(date-value)
returns the number of the quarter (e.g. 4 for Nov 15th)

 

 

 NextQuarter(date-value)
returns the first day of the next quarter (e.g. 1/1/2005 for Nov 15th 04)

 

 

 fixidcol()
if assigned to a macro, will fix all the ids (see FixID, above) for an entire column

 

 

soql_table(tablename, querystring)
Tricky: returns an ID if the query returns one hit. The querystring must be in the sytax of the SOQL Where clause.

Example:

soql_table("Contact","FirstName = 'Scot' and LastName = 'Stoney' ")

 

 

 sflookup(tablename, name)
returns the ID for the table based on a search value; it matches most fields

 

 

 sfemail(email, Optional findfirst)
returns the Contact ID based on a search for the email address.
If a second argument is specified, it will return the first match even if more than 1 are found

 

 

 sfcontact(firstname, Optional lastname)
a short-cut to sflookup. It is the equivalent of:
sflookup("Contact", firstname + " " + lastname) with some argument checking

 

 

 sfaccount(accountname)

a short-cut to sflookup. It is the equivalent of:

sflookup("Account", accountname) with the removal of some common suffixes such as "Corp".