generalQuery to site requiring user and password - code help needed

36 views
Skip to first unread message

David Scott Stokes

unread,
Oct 15, 2013, 2:56:36 AM10/15/13
to excel-r...@googlegroups.com
Hi,
The following URL, after basic authentication by user and password, and with header "application-json", happily returns an array in JSON when using Firefox's REST Addin. However, I do not see it in Excel when using the settings below. The user name and password are fictional for commercial reasons. As my code below fails, can you please advise the code to try?
 Method = GET
 https://newtemplate.hosts.webrecs.com/alfresco/service/webrecs/fundsearcher?full=true

However, in Excel, MsgBox returns:
Could find not data for query full=true-{"_deserialization":null}

Macro based on the HTTP authentication section plus modifying DuckDuckGo:
Public Sub webrecs_newtemplate_fundsearcher()
    Dim cr As cRest
    Set cr = restQuery("newtemplate_fundsearcher", "newtemplate_fundsearcher", _
    , , , , , True, , , True, , "user.xx.yy", "pwordxyz")
   
    generalQuery("newtemplate_fundsearcher", "newtemplate_fundsearcher", "full=true", True, True).tearDown
                End Sub

In Function createREstLibrary
        With .add("newtemplate_fundsearcher")
            .add "restType", erRestType.erSingleQuery
            .add "url", "https://newtemplate.hosts.webrecs.com/alfresco/service/webrecs/fundsearcher?"
            .add "results", ""
            .add "treeSearch", False
            .add "ignore", vbNullString
        End With

In Sheet newtemplate_fundsearcher, the titles in each column are
title shortName description isSiteManager siteTheme
based on the JSON below:
  •        {
  •            "title": "xxx yyy zzz",
  •            "shortName": "mytest",
  •            "description": "",
  •            "isSiteManager": "true",
  •            "siteTheme": "hcCyan"
  •        }

(2) I could not find any documentation on generalQuery to define the final "True, True)" I am lost with the many modules and Find discovered "generalQuery" being used in so many macros. Can you please advise what the True are? Also, could you please advise where is generalQuery so I can find it and read more?

Thank you in advance.

Bruce Mcpherson

unread,
Oct 15, 2013, 5:46:55 AM10/15/13
to excel-r...@googlegroups.com
The arguments are here for restQuery



I haven't checked basic authentication for a while but I assume it still works.

Are you sure that the site is expecting http basic ?

Your library entry looks ok at first glance.

generalQuery is just a simplifed version of restQuery, with most of the arguments defaulted as below 

' simplified interface
Public Function generalQuery(sheetName As String, _
                libEntry As String, queryString As String, _
                Optional breport As Boolean = True, _
                Optional queryCanBeBlank As Boolean = False, _
                Optional appendQuery As String = vbNullString) As cRest
    Set generalQuery = generalReport( _
            restQuery(sheetName, libEntry, queryString, , , , , , , , queryCanBeBlank, , , , , , appendQuery), breport)

End Function

You'll get that error when no data is returned.

I recommend you set a break point in the httpget method of the cBrowser class  to see what status and data  is being returned during the authentication process

bruce
 




--
You received this message because you are subscribed to the Google Groups "Excel Liberation" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-ramblin...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

Bruce Mcpherson

unread,
Oct 15, 2013, 5:50:03 AM10/15/13
to excel-r...@googlegroups.com
.. if it is digest authentication going on there, rather than basic, I havent implemented it in VBA since its little used nowadays. I do have a Google Apps version of digest authentication here..


bruce


On 15 October 2013 07:56, David Scott Stokes <david.sco...@inman.com.au> wrote:

--

David Scott Stokes

unread,
Oct 15, 2013, 5:11:35 PM10/15/13
to excel-r...@googlegroups.com
Hi Bruce,
It works. :-) This is brilliant. Thank you very much.

With your help, I now realise that the parameter required was the JSON node and subnodes to be rendered. I was working with the mistaken belief that the parameter required was the REST GET parameters.

I will now build on this with dropdown boxes etc. the setting of the parameters in the various web queries. Once that is completed, I will have a go at the REST POST scripts.

Regards
David
Reply all
Reply to author
Forward
0 new messages