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
dontichdontich 

Macro to run Excel Queues automatically

I am currently updating data into salesforce oppertunities using the excel connector professional addition.  I have gotten everything to run perfectly however it is a pretty time comsuming process to do by hand every single time any data needs to be updata ( would be done weekly). 

I am wondering if I can write a macro to automatically call the salesforce excel connector plugin.  I am unable to access the macro's source code and when I try to record the macro then run the addon, all that is displayed in the macro is to select the cell to start in.  Would I have to have access to the salesforce connector to do this??  Also is their a place I am not looking for, in order for me to issue VBA commands to the add-in.
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".

 

 
dontichdontich

Thanks!!,  Also how would i run the command to update particular cells ( it can just be the current selection)
dontichdontich

I figured this out myself.  You do it with the update_Range command
AndrewHess77AndrewHess77

I realize this is 9 months old, but do you happen to know what command would run the query on ALL sheets? I saw sfQueryAll, but I think that's per sheet. I'd like to update the whole document.