Apex Code Development You need to sign in to do that
Don't have an account?
elipman Having trouble calling sforce_connect.xla internal functions from VBA
I'm tinkering with VBA for the first time, and I'm trying to set up a spreadsheet on which I can click a button on the first sheet (tab) of my excel document that calls the "run each query on current sheet" function built into the sforce connector's toolbar.
A co-worker that knows way more about this than me suggested this line of code for running all queries on all sheets.
SO... I tried this code sample below that a community member named "PAL" had posted here:
http://community.salesforce.com/sforce/board/message?board.id=Excel_Connector&message.id=242#M242
This chunk of code failed with a compile error "sub or function not defined"
Specifically, it fails when it calls sfqueryall(true)
Playing around, I found that if I added this same code as a module within sforce_connect.xla, it worked perfectly, but when I add it as a module in my excel document, sfqueryall(true) fails.
From my limited programming knowledge, that tells me that even though sforce.connect.xla is an active "add-in," I still need to "include" the code somehow before I call one of the connector's internal functions.
Do I have the connector installed incorrectly? am I calling its internal functions incorrectly? or is there some simple include statement I need to issue before calling an internal function?
Alternatively to "running all queries on current sheet", it would seem quite elegant to just update the table data for the 5 different rows that I need to update sequentially.
With what I currently have working, I have to query each row manually.
select row22 - and choose "query table data" from the sforce connector toolbar.
select row25 - again query table data from the toolbar
and again for 3 or 4 more tables since I'm downloading the opportunity, contact, account, opportunitylintims, pricebook entry, and pricebook2
You can probably see why I want to automate this with a button on the first tab. I'm sure if I had some experience with VBA, this would be a no brainer. I'll learn quickly if I can get a little assistance here.
Thanks in advance!
-Ethan
A co-worker that knows way more about this than me suggested this line of code for running all queries on all sheets.
Sub ButtonName_Click()Turns out I don't want to run all queries on ALL sheets because that triggered some funky things happening on other sheets of my document that I didn't intend to modify.
Application.run "'sforce_connect.xla'!sfQueryAllSheets()"
End Sub
SO... I tried this code sample below that a community member named "PAL" had posted here:
http://community.salesforce.com/sforce/board/message?board.id=Excel_Connector&message.id=242#M242
Private Sub CmdButUpdSforce_Click()I changed "opps" to the name of my 2nd sheet "FROM SALESFORCE" that contains the queries I want to update.
On Error GoTo Err_CmdButUpdSforce_Click
Sheets("opps").Activate
Call sfqueryall(True)
Exit_CmdButUpdSforce_Click:
Exit Sub
Err_CmdButUpdSforce_Click:
MsgBox Err.Description
Resume Exit_CmdButUpdSforce_Click
End Sub
This chunk of code failed with a compile error "sub or function not defined"
Specifically, it fails when it calls sfqueryall(true)
Playing around, I found that if I added this same code as a module within sforce_connect.xla, it worked perfectly, but when I add it as a module in my excel document, sfqueryall(true) fails.
From my limited programming knowledge, that tells me that even though sforce.connect.xla is an active "add-in," I still need to "include" the code somehow before I call one of the connector's internal functions.
Do I have the connector installed incorrectly? am I calling its internal functions incorrectly? or is there some simple include statement I need to issue before calling an internal function?
Alternatively to "running all queries on current sheet", it would seem quite elegant to just update the table data for the 5 different rows that I need to update sequentially.
With what I currently have working, I have to query each row manually.
select row22 - and choose "query table data" from the sforce connector toolbar.
select row25 - again query table data from the toolbar
and again for 3 or 4 more tables since I'm downloading the opportunity, contact, account, opportunitylintims, pricebook entry, and pricebook2
You can probably see why I want to automate this with a button on the first tab. I'm sure if I had some experience with VBA, this would be a no brainer. I'll learn quickly if I can get a little assistance here.
Thanks in advance!
-Ethan
Message Edited by elipman on 09-22-2007 05:47 PM
using the application.run method was one was of calling those internal functions, but the ability to include the sforce_connect.xla add-in finally stopped evading me.
http://groups.google.com/group/microsoft.public.office.developer.vba/browse_thread/thread/fdcf11a88689a486/3fac8288eb12c9e5%233fac8288eb12c9e5
just had to hit tools - reference
and tick the box beside the sforce connector add-in
and finally.. I'm cruisin right along