I've used similar code below to run "public" PeopleSoft queries from Excel using VBA. When I look at the hyperlink produced from the code below, it is similar to the public queries we run with this method. The only difference is this is a "private" query. I can manually run the query in PeopleSoft with these same prompts and it runs and produces results like it is supposed to (32 rows). However, when I run this code from Excel, PeopleSoft opens, you sign in and it looks like the query runs. However, it doesn't return any results (0 rows). Is this there some twist with running "private" queries. Is it not possible to run "private" queries with this method? Any suggestions would be greatly appreciated as this has us stumped. Thanks for the help......
We are using Excel 2010, PeopleSoft 9.1 and PeopleTools 8.52 if that helps
Dim vFROMACCT As Date
Dim vTOACCT As Date
vFROMACCT = "07/01/2016"
vTOACCT = "07/21/2016"
vFROMACCT = Format(vFROMACCT, "MM/DD/YYYY")
vTOACCT = Format(vTOACCT, "MM/DD/YYYY")
vHypRoot = "http://pswebfsprd.myco.com/fsprd_1/EMPLOYEE/ERP/q/?ICAction=ICQryNameExcelURL=PRIVATE.CAR_SALES_1"
vHypLnk = vHypRoot
vHypLnk = vHypLnk & "&bind1=" & vFROMACCT
vHypLnk = vHypLnk & "&bind2=" & vTOACCT
With Worksheets("Sheet1")
.Hyperlinks.Add .Cells(10, 1), vHypLnk
.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End With
If you are getting 0 results then the query is running. I believe your problem is with your date parameters. I was able to run a private query with a date parameter but I had to use the date in YYYY-MM-DD format.
&BIND1=2016-07-21
This was on DB2 so it may be different for your DB. If you need to use a format with slashes in it, ensure you encode the URL before submitting it. Here is a Stack Overflow question on urlencoding in VBA
My full URL was:
https://dev.removed.com/psp/mydev/EMPLOYEE/ERP/q/?ICAction=ICQryNameURL=PRIVATE.TEST_DS&BIND1=2016-07-21
Note: This was on PeopleTools 8.54.13 but should work on 8.52