VBA ScriptControl to run Java Script Function

kriskyk
kriskyk used Ask the Experts™
on
I am tryng to insert the following java script function in word doc project to obtain a value from a cookie.


function getCookie(name) {
        var cookieArray = document.cookie.split(";");
        for (index in cookieArray)
        {
            var keyValuePair = cookieArray[index].split("=");
            var key = keyValuePair[0];
            key  = key.replace(/^\s+|\s+$/g, "");
            if (key == name)
            {
                var value = keyValuePair[1];
                return value;
            }
        }
    }

Open in new window






Here is a functional example of calling java script within a VBA Word Project however I am having trouble modifying the example below with above java script function getcookie that i need.

NOTE for the below example to work you must add the Microsoft Script Control 1.0 reference object library.


Public Sub x()
     Dim o As New ScriptControl
     o.Language = "JScript"
     With o
         .AddCode "function x(a,b) {return 'the answer is:' +(a+b);}"
         Debug.Print .Run("x", 1, 2)
     End With
 End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,

I can get this JScript code to run (from a ".htm" file) as expected in Microsoft Internet Explorer 10.0.9200.16599:

<html>
<head>
<title>Q28190006 - VBA ScriptControl to run Java Script Function</title>
</head>
<body>
<script type="text/jscript">

document.cookie='Q28190006=fanpages';

alert(getCookie('Q28190006'));

function getCookie(name)
{
  var cookieArray = document.cookie.split(";");

  for (index in cookieArray)
      {
      var keyValuePair = cookieArray[index].split("=");
      var key = keyValuePair[0];

      key  = key.replace("/^\s+|\s+$/g", "");				// Changed

      if (key == name)
         {
         var value = keyValuePair[1];
         return value;
         }
      }
}
</script>
</body>
</html>

Open in new window


However, using Visual Basic for Applications code (utilising Late Binding & the creation of an "MSScriptControl.ScriptControl" object) to undertake the same tasks, I receive a "'document' is undefined" error.

I also receive this error if I open the HTML file in Mozilla Firefox 22.0 so, I presume, that Internet Explorer is not being used by default in my environment (Microsoft Windows 8).

Even setting the "Default Program" for HTML/web content to Microsoft Internet Explorer does not affect the outcome.

Does your environment only have one web browser (a version of Microsoft Internet Explorer) installed?

PS. Using Late Binding, for your example JScript function above:

Public Sub JScript_Example()
     
  Dim objScriptControl                                  As Object
     
  Set objScriptControl = CreateObject("MSScriptControl.ScriptControl")
  
  objScriptControl.Language = "JScript"
  
  objScriptControl.AddCode "function x(a,b) {return 'the answer is:' +(a+b);}"
  
  MsgBox objScriptControl.Run("x", 1, 2), _
         vbInformation Or vbOKOnly

  Set objScriptControl = Nothing
  
End Sub

Open in new window


...

How about creating a temporary ".htm" file via VBA code that contains the definition of the getCookie(...) function in J(ava)Script, & opening this via a created "InternetExplorer.Application" object?

If the return from the getCookie(...) function was directed to the resultant HTML document, the result could be read directly back into the VBA code.

This said, I am presuming that the cookie being read is being set via SharePoint, as you mentioned in your earlier question:

"VBA to read Internet Cookie Information"
[ http://www.experts-exchange.com/Microsoft/Applications/Q_28185570.html ]

Is this the case?

If so, have you successfully returned any cookies set by the SharePoint process using local HTML/JScript code?

BFN,

fp.

Author

Commented:
Hi,
     Please see my answers below:

Does your environment only have one web browser (a version of Microsoft Internet Explorer) installed?

Answer: Has IE9 only


How about creating a temporary ".htm" file via VBA code that contains the definition of the getCookie(...) function in J(ava)Script, & opening this via a created "InternetExplorer.Application" object?

Answer: That seems like it might work can you provide some example code on how to do this?

If the return from the getCookie(...) function was directed to the resultant HTML document, the result could be read directly back into the VBA code.

This said, I am presuming that the cookie being read is being set via SharePoint, as you mentioned in your earlier question:

"VBA to read Internet Cookie Information"
[ http://www.experts-exchange.com/Microsoft/Applications/Q_28185570.html ]

Is this the case?

Answer:  Yep thats the case :)

If so, have you successfully returned any cookies set by the SharePoint process using local HTML/JScript code?

Answer: Yes I have, i have succesfully been able to use the set/getcookie jave function to automatically default the parent key in a child record between sharepoint lists.

I extremely greatful for you help,

-K
Hi K,

If you have just one browser, perhaps the VBA I wrote to create/execute the getCookie(...) function will run as expected without the need for the use of external HTM[L] files.

The code within the attached workbook is transposed below:

Public Sub Q_28190006()

' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28190006.html ]

' function getCookie(name)
' {
'   var cookieArray = document.cookie.split(";");
'
'   for (index in cookieArray)
'       {
'       var keyValuePair = cookieArray[index].split("=");
'       var key = keyValuePair[0];
'
'       key  = key.replace("/^\s+|\s+$/g", "");                 <- Note: Changed
'
'       if (key == name)
'          {
'          var value = keyValuePair[1];
'          return value;
'          }
'       }
' }

  Dim objScriptControl                                  As Object
  Dim strGetCookie                                      As String
     
  strGetCookie = ""
  strGetCookie = strGetCookie & "function getCookie(name) "
  strGetCookie = strGetCookie & "{ "
  strGetCookie = strGetCookie & "var cookieArray = document.cookie.split("";""); "
  strGetCookie = strGetCookie & "for (index in cookieArray) "
  strGetCookie = strGetCookie & "{ "
  strGetCookie = strGetCookie & "var keyValuePair = cookieArray[index].split(""=""); "
  strGetCookie = strGetCookie & "var key = keyValuePair[0]; "
  strGetCookie = strGetCookie & "key  = key.replace(""/^\s+|\s+$/g"", """"); "             ' Changed
  strGetCookie = strGetCookie & "if (key == name) "
  strGetCookie = strGetCookie & "{ "
  strGetCookie = strGetCookie & "var value = keyValuePair[1]; "
  strGetCookie = strGetCookie & "return value; "
  strGetCookie = strGetCookie & "} "
  strGetCookie = strGetCookie & "} "
  strGetCookie = strGetCookie & "}"
  
  Set objScriptControl = CreateObject("MSScriptControl.ScriptControl")
  
  objScriptControl.Language = "JScript"
  
' objScriptControl.AddCode "document.cookie='Q28190006=fanpages';"
  
  objScriptControl.AddCode strGetCookie
  
' Debug.Print objScriptControl.Run("getCookie", "Q28190006")

  Set objScriptControl = Nothing

End Sub

Open in new window



Please see if this runs for you, & then let me know if we need to look at other approaches.

Thanks.

BFN,

fp.
Q-28190006.xls
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Just for information: the HTML (as above, as below, & attached_ is now functioning as expected in Mozilla Firefox 22.0! :)

<html>
<head>
<title>Q28190006 - VBA ScriptControl to run Java Script Function</title>
</head>
<body>
<script type="text/jscript">

document.cookie='Q28190006=fanpages';

alert(getCookie('Q28190006'));

function getCookie(name)
{
  var cookieArray = document.cookie.split(";");

  for (index in cookieArray)
      {
      var keyValuePair = cookieArray[index].split("=");
      var key = keyValuePair[0];

      key  = key.replace("/^\s+|\s+$/g", "");				// Changed

      if (key == name)
         {
         var value = keyValuePair[1];
         return value;
         }
      }
}
</script>
</body>
</html>

Open in new window

Q-28190006.htm

Author

Commented:
HI FanFages,

                  As you anticipated i recieved the document undefined error.  Can you provide an example of the html approach you previously mentioned?

I greatly appreciate your help,

-K

Document Undefined Error
Hi,

Just to let you know I have not forgotten about this thread.

If you still need assistance, I can look at providing further code this weekend.

Please let me know either way.

Thanks.

BFN,

fp.

Author

Commented:
Yes I'm still in need of assistance.  I greatly appreciate your help.

-K
OK.

Sorry I have not had any free time this week to look at this further, but I will find some before the end of the weekend for you as I am conscious of the prolonged delay to date.
Hi again,

I have been looking at code to meet your requirements for about 2.5 hours now & I am hitting a barrier regarding executing J(ava)Script from a locally created ".htm" file, as Internet Explorer is blocking the content (& waits for confirmation of a security prompt before the file is loaded).  The VBA code I have is not executing beyond the loading of the file due to this blocked access.

In variations of the code I am finding that using a local file is not possible, as remote content is expected (& the local file "protocol" is not supported).

Whilst researching other methods (beyond the six varying approaches I have already tried!), I found this:

[ http://www.ericphelps.com/scripting/samples/Reference/Web/GetCookie.txt ]

Function GetCookie(strUrl)
	Dim web, strCookie
	On Error Resume Next
	Set web = Nothing
	Set web = CreateObject("WinHttp.WinHttpRequest.5.1")
	If web Is Nothing Then Set web = CreateObject("WinHttp.WinHttpRequest")
	If web Is Nothing Then Set web = CreateObject("MSXML2.ServerXMLHTTP")
	If web Is Nothing Then Set web = CreateObject("Microsoft.XMLHTTP")
	web.Open "GET", strUrl, False
	web.SetRequestHeader "REFERER", strUrl
 	web.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)"
 	web.SetRequestHeader "Accept", "text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5"
 	web.SetRequestHeader "Accept-Language", "en-us,en;q=0.5"
 	web.SetRequestHeader "Accept-Charset", "ISO-8859-1,utf-8;q=0.7,*;q=0.7"
	web.Send
	strCookie = web.getResponseHeader("Set-Cookie")
	strCookie = Split(strCookie, ";")(0)
	GetCookie = strCookie
End Function

Open in new window


Please could you try this code with the "http://" address of your SharePoint server specified as the strUrl parameter to see if any usable data is returned?

Removing (commenting-out) this single line may also be worthwhile in initial testing in case any extended information returned helps with analysis:

	strCookie = Split(strCookie, ";")(0)

Open in new window


Thanks.

Author

Commented:
Hi FP,
         I tried using "http://connect/" but received the following error.   Further below is the cookie details i'm trying to retrieve....



TEst
Cookie Properties
Cookie value
Hi,

Does your SharePoint server site not have an external address, perhaps even an exposed I.P. address (instead of using [ http://connect ], using something like [ http://100.200.10.0 ])?

Also, what is the home page of the server site, or the page that sets the cookie?  Is it [ http://connect/defaults.aspx ], or something similar?

Perhaps you can try an address of this nature instead.

This aside, if you know which file the cookie data is stored within, can we simply not read that file (& obtain the contents as ASCII text) from the Internet Explorer cache folder as a ".txt" file in order to gain the information you require?

Author

Commented:
The later sounds much easier. Can you provide some guidance?
Hi,

Expanding on code I provided in this previous question:
[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28140660.html ]

I have added the (Public) Const lngCSIDL_COOKIES & removed the two previous (Public) Constants that were being used by the Test() subroutine.

Option Explicit

Private Declare Sub CoTaskMemFree _
                Lib "ole32.dll" _
             (ByVal pvoid As Long)

Private Declare Function SHGetPathFromIDList _
                     Lib "shell32.dll" _
                   Alias "SHGetPathFromIDListA" _
                   (ByVal Pidl As Long, _
                    ByVal pszPath As String) As Long

Private Declare Function SHGetSpecialFolderLocation _
                     Lib "shell32.dll" _
                  (ByVal hWnd As Long, _
                   ByVal nFolder As Long, _
                   ByRef ppidl As Long) As Long
    
'Public Const lngCSIDL_PERSONAL                          As Long = &H5
'Public Const lngCSIDL_DESKTOPDIRECTORY                  As Long = &H10
Public Const lngCSIDL_COOKIES                           As Long = &H21
Public Function strSpecial_Folder(ByVal lngFolder As Long) As String
  
' --------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28140660.html ]
'
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
'
' ID:               28140660
' Question Title:   macro to export call range to pdf
' Question Asker:   Goraps                                    [ http://www.experts-exchange.com/M_4270685.html ]
' Question Dated:   2013-05-28 at 16:10:36
'
' Expert Comment:   fanpages                                   [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited                           [ http://NigelLee.info ]
' --------------------------------------------------------------------------------------------------------------
  
  Dim lngFolderFound                                    As Long
  Dim lngPidl                                           As Long
  Dim lngPidlFound                                      As Long
  Dim strPath                                           As String
  Dim strReturn                                         As String
  
  On Error GoTo Err_strSpecial_Folder
  
  Const MAX_PATH                                        As Long = 260&
  
  strReturn = ""
  strPath = Space(MAX_PATH)
  
  lngPidlFound = SHGetSpecialFolderLocation(0, lngFolder, lngPidl)

  If lngPidlFound = 0& Then
     If (SHGetPathFromIDList(lngPidl, strPath)) Then
        strReturn = Left$(strPath, InStr(1&, strPath, vbNullChar) - 1&)
    End If ' If (SHGetPathFromIDList(lngPidl, strPath)) Then
  End If ' If lngPidlFound = 0& Then
  
Exit_strSpecial_Folder:

  On Error Resume Next
  
  If lngPidl <> 0& Then
     Call CoTaskMemFree(lngPidl)
  End If ' If lngPidl <> 0& Then
  
  strSpecial_Folder = strReturn
  
  Exit Function
  
Err_strSpecial_Folder:

  On Error Resume Next
  
  strReturn = ""
  
  Resume Exit_strSpecial_Folder
  
End Function
Public Sub Test()

  Dim strCookies_Folder                                 As String
' Dim strDesktop_Folder                                 As String
' Dim strPersonal_Folder                                As String
  
  strCookies_Folder = strSpecial_Folder(lngCSIDL_COOKIES)
' strDesktop_Folder = strSpecial_Folder(lngCSIDL_DESKTOPDIRECTORY)
' strPersonal_Folder = strSpecial_Folder(lngCSIDL_PERSONAL)
  
' MsgBox "Desktop Folder:" & _
         vbCrLf & _
         strDesktop_Folder & _
         vbCrLf & vbLf & _
         "Personal Folder:" & _
         vbCrLf & _
         strPersonal_Folder, _
         vbInformation Or vbOKOnly, _
         ThisWorkbook.Name
         
  MsgBox "Cookies Folder:" & _
         vbCrLf & _
         strCookies_Folder, _
         vbInformation Or vbOKOnly, _
         ThisWorkbook.Name
         
End Sub

Open in new window



Hopefully when you run the Test() subroutine you should see the name of the folder where the Cookie files are stored on your PC.

Is that the case?

Author

Commented:
Thats the case :) however i had to comment out ThisWorkBook.Name to get it to run since the word document is a template being opened from SharePoint.


Msgbox
Sorry, yes, good catch! :)

OK, do you know the filename of the file you wish to read data from within this "Cookies" folder?

I see you have mentioned it above, but does it change each time it is updated, or does it remain static?

Author

Commented:
it's dynamic, the title will always be "cookie:UserID@connect/" with the UserID being variable and the chache name changes as well.



Cookie Properties

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial