+ Start a Discussion
adamgadamg 

Updated sforce Excel Connector now available

Ron's brilliant (and now famous) bi-directional Excel add-in for updating and managing sforce data has been ported to the Office toolkit, and is now available for download at http://sforce.sf.net. Among other things, this means it now supports custom objects and Product2.

Thanks Ron!

Message Edited by adamg on 07-27-2004 09:38 AM

dgrissendgrissen

Thanks Ron for the fantastic plug-in.  I have a (hopefully) quick question on it.  Is there a way to use the insert function to create a new account or a new opportunity within Salesforce?  I had tried a test scenario of creating a new account.  After getting account table structure, I added account name and filled in a few fields and then put "new" in cell A1.  The query message kept returning an error, however.  Does the Account ID field have to be filled in correctly in order to create (insert) a new record.  Does the Account ID (or Opportunity ID or whatever entity ID) get created automatically?  If not, how do you figure out what to make the Entity ID?

 

Thanks,

Darrell

ScotScot

Darrell,

What you describe should work; there is probably something missing in your description. Perhaps a note of what the error message is?

What should work:
   1) "account" in A1
   2) sforce Connector/Describe Table
   3) "new" in the Account ID column (should be column A)
   4) fill in any other columns
   5) highlight the "new" values in the Account ID table
   6) sforce Connector/Insert Selected Rows

It will highlight the "new" values in yellow as it processes them, then remove the highlight and replace the "new" with the newly assigned account ID's from the insert operation.

I sometimes receive data-related errors (most recently, it required that true/false values be non-blank). I cured this by removing the columns for the true/false values.

DevAngelDevAngel

Hi Darrell,

The Id field is not udateable, creatable or nullable.  When inserting a record, you leave the id field blank and a new id will be generated.

This code works in VB 6.0

    Dim binding As New SForceOfficeToolkitLib.SForceSession
    Call binding.Login("username", "password", False)
   
    Dim sobjs(0) As SForceOfficeToolkitLib.SObject
   
    Set sobjs(0) = binding.CreateObject("Contact")
    sobjs(0).Item("LastName") = "foghorn"
    sobjs(0).Item("FirstName") = "contact"
    On Error Resume Next
    binding.Create sobjs, False
    If Err.Number = 0 Then
        MsgBox "Created"
    Else
        MsgBox "failed" & " - " & binding.ErrorMessage
    End If

ScotScot

Just to head off any confusion ..

Dave's note is perfectly accurate for the Toolkit: for an insert the ID is left blank.

The Excel plug-in, however, uses a value of "new" in the ID cell to trigger passing the blank value to the Toolkit. If you use a blank in the cell, you'll get an error from the VB code of the Excel plug-in.

Ron HessRon Hess

Yes, creating new accounts works , "new" in the account id field should work, limit the columns to the ones you want (as Scot suggests)

what was the error msg ?

Garry LindsayGarry Lindsay

This is a VBscript function that fails trying to insert a new Lead record :-

Public Function RunCreate()

  Dim bRet
  Dim sobj(0)

  bRet = TRUE

  Set sobj(0) = p_sfdc.CreateObject("Lead")


  sobj(0).Item("FirstName") = "Garry" <<<<<<<< Failure point, see error below


  sobj(0).Item("LastName") = "Lindsay"
  'sobj.Create
  p_sfdc.Create sobj, False
  'RunQuery
    
     On Error Resume Next
    
  If Err.Number = 0 Then
   Response.Write "Created"
  Else
   Response.Write "failed" & " - " & p_sfdc.ErrorMessage
  End If

    
     RunCreate = bRet
    
 End Function

 

Here is the error :-

Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'sobj(...).item'

/appsynthesis/appmanager/includes/classes/sForce/clssForceHelper.asp, line 321

 

Any help??

Cheers

Garry

 

Garry LindsayGarry Lindsay

Is it possible to use this Office COM object from WIndows Script Host via VBScript???

It appears there are some basic data types that VB script does not support that are need to call the COM API's?

Cheers

Garry

DevAngelDevAngel

Hi Garry,

You "should" be able to do this.  This object can be used in javascript, so it should be available for vbscript.

 

Garry LindsayGarry Lindsay

Object browser tells me that "FirstName" needs to be of type System.Object

public abstract new SForceOfficeToolkitLib.Field Item ( System.Object key )

Member of SForceOfficeToolkitLib.ISObject

But it appears this error :-

Set sobj(0) = p_sfdc.CreateObject("Lead")

sobj(0).Item("FirstName") = "Garry" <<<<<<<<<< Error

Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'sobj(...).item'

I obviously get this error on this code -

Set sobj(0) = p_sfdc.CreateObject(p_strCurrentObjectName)

sobj(0)("FirstName") = "Garry" <<<<<<<<<< Error

 Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method

The VBscript doc tells me this :-

You specified a property or method that does not exist for this automation object. Not all objects support all properties and methods.

To correct this error

  • Check the properties and methods to ensure there were no typing errors.
  • See the object's documentation for more information.

I am scratching my head!!!!

Cheers

Garry

DevAngelDevAngel

Hi Garry,

I got this to work:

Set sobj = p_sfdc.CreateObject("Lead")

sobj.Item("FirstName").Value = "Garry"

foghornfoghorn

Late bound languages, like vbscript and javascript, have trouble with identity properties (DISPID_VALUE). This is the magic property index that will map a particular object property as a default. In the toolkit, on SObject, this maps to the Item collection. On the field object it maps to the value property.

So code like this which is fine in early bound VB, making use of this little trick :

Dim s as SObject

Set s = api.CreateObject("contact")

s("lastname") = "test"

will not work in scripts, which access everything via IDispatch interfaces and do not honor DISPID_VALUE.

So, you have to be really specific about how you access everything from scipt languages:

 

Dim sobject
Dim session

Set session = server.createobject("SForceOfficeToolkit.SForceSession.1")

session.Login "foo@bar.com","123456",False


set sobject = session.CreateObject("contact")

sobject.Item("firstname").value = "Me"
sobject.Item("lastname").value = "Not you"

sobject.Create

sobject.Refresh

Notice the .value, which is all your code needs.

Unfortuanlly I found a bug were I'm not handling vbscript arrays correctly, which results in an exception for all the array methods.

I'll fix it for the 2.0 release this month, for now just use the singleton methods like the example or use javascript.

 

Garry LindsayGarry Lindsay

Awesome, it worked, but I have found one other wierd issue when pulling data out :-

For Each sobj In qrs
 For i = LBound(fieldNames) To UBound(fieldNames)
  strFieldName = TRIM(fieldNames(i))   
  Set fld = sobj.Item(i) <<<<<<<<<<<<<<<<<<<<<< Works
  Set fld = sobj.Item(strFieldName) <<<<<<<<<<<<< Fails
 Next
Next

Here is the error

SForceOfficeToolkit.SObject.1 error '80004001'

Unsupported field index type

and if I run it again, I get this :-

SForceOfficeToolkit.SObject.1 error '80004005'

Invalid field index::14564

It seems I get a random field index error, or the unsupported index type error, but using the index itself Set fld = sobj.Item(i) or actually having the line Set fld = sobj.Item("FirstName") works, but Set fld = sobj.Item(strFieldName) fails? strFieldName can be set to "FirstName" and it still fails?

Am I making another bad scripting coding mistake?

Cheers

Garry

 

Garry LindsayGarry Lindsay

Do you support listening all the fields in an sForce object i.e. all the Lead field in the Lead object e.g. like using the .NET describeSObject function call. Is there any way in VBscript to replicate this call :-

// Invoke describeSObject and save results in DescribeSObjectResult
   DescribeSObjectResult dsr = binding.describeSObject("Lead");

 

Cheers

Garry

DevAngelDevAngel

Hi Garry,

Try casting to a string like this:

Set fld = sobj.Item(cstr(strFieldName))

 

Cheers

Garry LindsayGarry Lindsay

I am having a problem using the Create function

Here is my code

  Set sobj = p_sfdc.CreateObject("Lead")
  
  FOR EACH strField IN dictNewFieldValues
   strValue = dictNewFieldValues(NameValuePair)
   Response.Write("Field = " & strField & " | Value = " & strValue & "<br>")
   sobj.Item(strField).Value = dictNewFieldValues(strValue) >>>>>>> This is line 658
  NEXT
  
  sobj.Create
  sobj.Refresh

here is my result

Creating applicantID : 489852 record to sForce...
Field = Company | Value = Drexel eLearning

SForceOfficeToolkit.SObject.1 error '80004005'

Invalid field index::27892

/appsynthesis/backendmanager/includes/classes/sForce/clssForceHelper.asp, line 658

Yet, when I blank out the FOR EACH loop and replace it with this code

 sobj.Item("FirstName").Value = "Garry"

 sobj.Item("LastName").Value = "Lindsay"

I get this result, it fails on the create statement

Creating applicantID : 489852 record to sForce...

SForceOfficeToolkit.SForceSession.1 error '80004005'

Required fields are missing: [Company]

/appsynthesis/backendmanager/includes/classes/sForce/clssForceHelper.asp, line 665 

Do all fields need to be filled in for a recored to be inserted?

Help!

Cheers

Garry

ScotScot

Hi Gary,

I don't have an an opinion on the first problem, but on the requirement for all fields ....

Not all fields need be specified, but for create, you do need to include those flagged as required for your site. By default, Company is such a required field.

Scot

Garry LindsayGarry Lindsay

It appears in ASP/VB script that :-

Set sobj = p_sfdc.CreateObject("Lead")

sobj.Item("FirstName").Value = "Garry"

works, but

Set sobj = p_sfdc.CreateObject("Lead")

Dim strField

strField = "FirstName"

sobj.Item(CSTR(strField)).Value = "Garry"

fails

Any ideas?

 

Cheers

Garry

ScotScot

You've got me.

Most of my work so far has been in VBA in Excel. I'm working on some code to transfer to VBScript, however, so I may find myself in your situation soon.  I'll be interested in what you discover.

Scot

RoosterRooster

Salesforce: Update()

Invalid range, missing data type or other unknown error

Type mismatch

==================

Getting this error trying to do a simple one account record/one cell (text data) update.

Have re-installed the .xla (now have the additional operators showing in the query wizard) but am a newbie here and can't see what I've missed in following the setup instructions.  No problems logging in or setting up the query to retrieve the data I'm looking for.  Any tips much appreciated.

Ron HessRon Hess

is the field a standard or custom?

version of Excel?

do all updates fail ? or only one.

could be several things, the connector just catches vb exceptions, so you could breakpoint in the code and see what values it was passing around just before the exception.

perhaps passing a number into a text field or reverse?

sorry, but there are too many possible causes to pin this down without more info / example

send me the sheet you are using if you can

RoosterRooster

Occurred for both standard (street, Account Site, Account description) and custom.  What I'm really trying to do is move a string of 8 digits into account site.  Used "Text" setting on cell to try to force interpretation of 8 digits as a string, not an integer.  But even typing a test string into the standard "Account Description" field yeilded the same error.

Using Excel 2002 sp3

Have not been able to get any update to go thru on account.  Tried with updating only a single row, no luck.  Any other ideas?

TIA

bouscalbouscal
I've received this message when attempting to update several fields where one of those fields was required and I attempted to put a null value into it.  Selecting only the cells with info, in your case only the Site field, may allow the update to run.  I often only use 3 or 4 fields on the spreadsheet when I run updates as well, this keeps things moving relatively quick.
Ron HessRon Hess

The profile you are using does need to have permissions to wirte the field, i'm sure you've checked that.

if you can boil it down to a single row and column in a worksheet I will look at it, send the sheet to me.

my email is in my profile.

RoosterRooster

Hi Ron,

I wasn't able to view your email in your profile.  (it is there for Sforce to send messages to but may not be open for public viewing.) 

Here's what's in the spreadsheet, down to one record, trying to update the billing zip.

Even if I try to change the zip code from 80503 to 80503-7167, highlighting one cell of a spreadsheet with one row of data, I'm still getting the same error.

AccountSub Codeequals82 smqCustomer Numbergreater than22000000Customer Numberless than23000000Account SitelikeG2N
Account IDAccount NameBilling CityBilling CountryBilling Zip/Postal CodeBilling State/Province
00130000002D1cQAASSolantLongmontUSA80503CO

------------------------------------------------------------

Your notes indicate tested

The Sforce Connector has been tested and is running on Excel XP (10) and Excel 2003 (11), other versions of Excel may or may not function properly.   It looks like I've only got sp3 -- ??

RoosterRooster
Upgrading to Excel 2003 from Excel 2002 solved the problem.