2

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
Stevoisiak
  • 23,794
  • 27
  • 122
  • 225
Shaves
  • 884
  • 5
  • 16
  • 46

1 Answers1

1

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

Community
  • 1
  • 1
Darryls99
  • 921
  • 6
  • 11
  • 1
    @Darryls99...........Thanks for the help. You were correct. Once I changed the date format, I got it to run. It doesn't work a100 % of the time, I still 0 results some times but it did produce the correct results a couple of times. Thanks again........ – Shaves Jul 22 '16 at 19:12