+ Start a Discussion
ScotScot 

Using a WIL to start and pass values to an Excel Worksheet

I need to build a WIL which opens an Excel spread-sheet and passes it values from the opportunity record. The excel sheet could be on salesforce or, possibly, already on the user's PC.  At a minimum, I need to start a macro within the sheet and feed it the Opportunity ID.

I suspect that the solution will require significant expertise in both Excel and WIL's; I have a fair amount of both, but have been unable to come up with a solution yet.

Has anyone done this?  Or something analagous?

If not - can you provide any suggestions?

DevAngelDevAngel

Hi Scot,

As a matter of fact this has been done.  What you do is create a workbook that has the requisite macros in it.  In these macros you will need to parse the FullName property of the work like so:

    Dim name As String
    name = ThisWorkbook.FullName
    If InStr(name, "&p1") <> 0 And InStr(name, "&p2") <> 0 And InStr(name, "&p3") <> 0 Then
        ParseLocation name
    End If

Sub ParseLocation(ByVal lName As String)

    Dim parts
    parts = Split(lName, "?")
    Dim queryPart As String
    queryPart = parts(1)
    Dim params
    params = Split(queryPart, "&")
    Dim i As Integer
    For i = LBound(params) To UBound(params)
        Dim kvPair
        kvPair = Split(params(i), "=")
        Select Case LCase(kvPair(0))
            Case "p1"
                session_ID = kvPair(1)
            Case "p2"
                server_URL = kvPair(1)
            Case "p3"
                user_ID = kvPair(1)
        End Select
    Next
   
   
End Sub

The p1, p2 and p3 are query parameters that are set in the sforce control HTML body by using the following javascript snippet:

<script>
<!--
document.location = '{!Scontrol_URL}&p1={!API_Session_ID}&p2={!API_Partner_Server_URL_30}&p3={!User_ID}';
-->
</script>

The snippet above represents the entire contents of the sforce Control html body.  The spreadsheet should be uploaded as the sforce Control binary file.

Setting the document.location like this will allow you to retrieve this info from the FullName property the workbook from your macro.

The attached file is a workbook that I have used in an scontrol.

 

ScotScot

Dave,
     Fantastic!    Awfully clever ... and so much easier to copy than to devise.

Thanks very much.

Scot

jcherianjcherian
Hi Scot/Dave,

Thanks Guys.

When I try to run this example by clicking on the �My Opportunities� button within the excel spreadsheet, I get the following error message: �Compile Error: Can�t find project or library�. When I hit Ok, the References- VBA Project Window pops-up with the sforceClent Proxy Class- Generated by the Pocket Soap WSDL widget Missing check box checked.

The Visual Basic Editor is pointing to the following function call at the same time: Global binding As sforceClient.binding

Do I need this reference, if so, where can I get this reference?

Also, do I need this module? My basic requirement is to open up an Excel Worksheet from the Products page, passing one of the field variables (unique key) back to my spreadsheet to query up the underlying table info.

Lastly, is there anyway to open up the excel spreadsheet separately- currently the excel spreadsheet opens up in a browser within the SF application.

Thanks for all your help.

Johns
DevAngelDevAngel

Hi jcherian,

You will need to run your own VB Script code on your spreadsheet.  The reference you mention is to a proprietary dll that has been retired in any case.  This is a good place to use the COM library when it becomes GA (don't know when that will be yet).  In the mean time, you would use MSXML to do your soap calls.

 

As to opening the spreadsheet in excel outside of the browser, I don't know how you would do that.  Not saying it can't be done, it's just beyond my experience.  If you want to save the spreadsheet, you can do so from the browser.

jcherianjcherian
Thx Dave!

Forgive me if I am overlooking this in your response but how do i pass values from the Products page into my spreadsheet? I need to pass an unique key identifier to my excel spredsheet so that I can retrieve all the data required in the spreadsheet using COM?

Your input is greatly appreciated.

Johns
DevAngelDevAngel
Please refer back to my original response to Scot's post.  It is all visible there.
jcherianjcherian
Thanks Dave,

Things make more sense once I understood what merge fields were and how to use them. I am currently running into the issue where I need to open up the data in an Excel spreadsheet with Macros and also be able to save it as an Excel Spreadsheet on our clients workstation. The workaround of having a SF control with a binary file (excel spreadsheet) uploaded does not seem to allow the spreadsheet to be saved as a true excel file once the user opens it up and makes their modifications. What do you suggest.

Thanks in Advance.

Johns
DevAngelDevAngel

Hi jcherian,

You could make a macro that saves it in (opens the save as dialog) and hook that macro to a key combination or something.

jcherianjcherian

Please help,

I am not getting values passed from our Opportunities page with my document location. I was able to do it before.

My ThisWorkbook.FullName property has the following value: which is the location of the file on my local drive

"C:\Documents and Settings\jcherian\My Documents\Sales Force\Schedule A-PPI-6-21-04.xls"

I have the following for my control body and I have uploaded my excel workbook as the binary file for this control:

<SCRIPTS>
<!--
document.location = '{!Scontrol_URL}&p1={!API_Session_ID}&p2={!API_Partner_Server_URL_30}&p3={!User_ID}&p4={!Opportunity_ID}';
-->
</SCRIPTS>

Do I need to change any of the browser settings etc. Anyhelp would be greatly apreaciated!!! I need a solution soon.

Johns

Message Edited by DevAngel on 06-29-2004 08:26 AM

DevAngelDevAngel

Hi Johns,

That is a strange place for the document to be.  I would expect that if you were getting a local drive fully qualified path for the full name it would be in Temporary Internet Files or something similar.  This leads me to believe that the script that is running is running outside the context of the scontrol.

Maybe you could post the steps that you are taking from the point that you click the wil forward (please be specific as I am a little slow).

jcherianjcherian
Thanks Dave.

After I click on the WIL I get a Security Warning as I have Office 2003, and the macros security level is set at medium thus I am forced to confirm before Macro execution. Once, I select Enable Macros, then I go to the Excel Workbook I am opening and in examining the Thisworkbook.Fullname values, I am getting this time as you noted in your previous email the following document location:

"C:\Documents and Settings\jcherian\Local Settings\Temporary Internet Files\Content.IE5\NSD8RB2V\Schedule+A-PPI-6-21-04[2].xls"

Any help would be greatly appreciated.

Johns
ScotScot

Dave,

I just started testing this this morning, and found the same behaviors as Johns. 

I'm using IE 6.0, WinXP, OfficeXP.

The flow is:
    > Click on WIL
    > Respond "Open" to IE's prompt to open, save, cancel
    IE message bar states:
    "downloading https://ssl.salesforce.com/servlet/servlet.FileDownload?file=01N...&p1=Ps6sQ....etc.
    > Respond "Enable macros" to the security prompt
    Excel opens embedded in the IE window, and without, unfortuately, most menus.

By ensuring that Excel was open before this occurs, I can get into VB.
    > "print ThisWorkbook.FullName"
produces:
"C:\Documents and Settings\SStoney\Local Settings\Temporary Internet Files\Content.IE5\FCITT1WX\GetOpp[1].xls"


My control is:
<script>
<!--
document.location = '{!Scontrol_URL}&p1={!API_Session_ID}&p2={!API_Partner_Server_URL_30}&p3={!User_ID} &id={!Opportunity_ID}';
-->
</script>


My Macros are:
Dim name As String
    name = ThisWorkbook.FullName
    Range("a7") = name   ' test
    If InStr(name, "&p1") <> 0 And InStr(name, "&p2") <> 0 And InStr(name, "&p3") <> 0 And InStr(name, "&id") Then
        ParseLocation name
    End If
End Sub
Sub ParseLocation(ByVal lName As String)
    Dim parts
    parts = Split(lName, "?")
    Dim queryPart As String
    queryPart = parts(1)
    Dim params
    params = Split(queryPart, "&")
    Dim i As Integer
    For i = LBound(params) To UBound(params)
        Dim kvPair
        kvPair = Split(params(i), "=")
        Select Case LCase(kvPair(0))
            Case "p1"
                session_ID = kvPair(1)
            Case "p2"
                server_url = kvPair(1)
            Case "p3"
                user_id = kvPair(1)
            Case "id"
                object_id = kvPair(1)
        End Select
    Next
    Range("c2") = session_ID
    Range("c3") = server_url
    Range("c4") = user_id
    Range("c5") = object_id
   
End Sub

 

jcherianjcherian
Any thoughts on this Dave? If this is no longer viable, how can we pass variables from SF into excel. I have a demo this Friday and this is a crucial piece of my SF integration code.

Thanks,

Johns
DevAngelDevAngel

Hi jcherian,

I'm at a loss.  This is not something that salesforce.com has control over.  This is a browser/excel behavior.  Unfortunately, I don't have the cycles to dig into this currently (release coming up soon, slammed with preparation).  I'd be happy to revisit this post release though.

jcherianjcherian
Hi Dave,

Is there documentation for your pilot COM plugin? I am trying to achieve querying one row of data into my excel spreadsheet and after some modification in excel, writing that data back to Sales Force (SF) without using an excel add-in as in Ron Hess's example. Your sample code in the sforceControlWorkbook.xls pulls in data without using an add-in and it gives me hope that I can do the same. If you can point me in the general direction, I will be very grateful.

As for passing parameters, I am looking at passing parameters using Active X.

Thanks,

Johns
ScotScot

Dave,

Sounds like Jcherian has a different solution for passing values to Excel.

I am still interested in yours ... so if, post-release, you can take a look, I'd appreciate it.

Scot

ScotScot

Dave (or anyone else) ....

I'm still stuck on this requirement. As originally stated:

I need to build a WIL which opens an Excel spread-sheet and passes it values from the opportunity record. The excel sheet could be on salesforce or, possibly, already on the user's PC.  At a minimum, I need to start a macro within the sheet and feed it the Opportunity ID.

The original suggestion, using document.location to open the .xls file with parameters, and parsing the values in the FullName property of the worksheet to get them in excel, doesn't always work because IE builds a temp file and opens that, losing the parameters.  (see http://forums.sforce.com/sforce/board/message?board.id=general_development&message.id=1987 for symptoms).

Thanks for any suggestions or help ...

Scot

 

himanshi dashimanshi das
Microsoft Atom perfect Date Solutions excel file password. PDS  excel password recovery software recover your excel file password. PDS  excel password recovery software support all the version of the ms excel file. PDS  excel password recovery software recover any type of the password like any alphabetic password, number combination password, special character combination password. Stella excel password recovery software recover your excel password using three password recovery method. PDS excel password recovery software recover encrypted password of the excel file for more information visit

 Read more- http://www.perfectdatasolutions.com/excel-password-recovery-software.html (http://www.perfectdatasolutions.com/excel-password-recovery-software.html" style="color:blue; text-decoration:underline)