Automate Picklists

[billhayes]

Has anyone ever successfully used sendkeys in a macro to automatically select all values in a picklist? After searching the posts it doesn't look like this can even be done.

 

[christenhusz]

hi,

I never tried this but did you use the Down Arrow option to select an item from the picklist? sendkeys"{DOWN 5}" to send 5 "down arrow"commands.

The keys are sent to the active window...

Good luck

 

[griffindm]

Bill,

Do you want to select only the values in the picklist, or is this a way to present either a list of values to pick from, and you are looking for an alternative to also allow the user to select all values? The picklist, using an IN clause, will cause increasing load on the database depending on the number of values in the picklist (imagine an In SQL clause with a thousand values in the list). An alternative is to use a conditional filter, and either give the user a second prompt (i.e. Enter (A) for ALL) or include an "ALL" value in the picklist presented, either by adding it to a file list, or by adding it to the catalog or report picklist by inserting the extra value into the source table, or using a view to cause it to appear as if it was added.

Regards,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[billhayes]

The report I'm running is never used by anyone else. We're using impromptu as a query tool only. I'll be the user and on a quarterly basis I get a list of IDs that I need to query for. I don't want to do this in the SQL writer because I don't want to lose all the functionality of impromptu. I understand the in statement on nearly 1,000 items will cause performance problems, but the added functionality of impromptu outweighs this and performance is not extremely bad. That said, I want to be able to, in a macro, automatically select all the values in this picklist as this picklist is used in 10 queries that the macro will run. Being such a large job I want to run this at night and can't be there to select the values.
Thanks.

 

[griffindm]

Bill,

If you get the list on a quarterly basis and need to have the report run for all items in the list, consider getting the list in a Database format (i.e. DB4, ascii, or even Excel) and use Impromtpu to create a hotfile with the contents of the list in it. If ascii or Excel, use the ODBC drivers MS provides for either of these. Then just add the hotfile to the catalog and report with a join to the data element in the true database. This version would automatically run as if all items in the picklist were selected.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[billhayes]

i don't have the capability to do what you suggest. we're very limited in what we're allowed to do. i would like to explore using the sendkeys. i understand the theory but i can't get them to work. it seems that in the macro when the report opens and the picklist appears, the macro comes to a halt. if i manually hit shift + end then enter to select all the values the macro will continue.

 

[billhayes]

Nevermind, after hours of frustration I figured it out. The sendkeys needed to be sent before the openreport method occurs. I was able to get the following example to work with three picklists.

Sub Main()

Dim objImpApp as Object
Dim objImpRep as Object
Set objImpApp = CreateObject("CognosImpromptu.Application&quot
objImpApp.OpenCatalog "C:\catalog.cat", "Userid","Password"_, , ,TRUE
objImpApp.Visible True
SendKeys "+{End}"
sendkeys "{tab}"
SendKeys "+{End}"
sendkeys "{tab}"
sendkeys "+{end}"
sendkeys "{enter}"
Set objImpRep = objImpApp.OpenReportNoExecute_("C:\report1.imr&quot
objimprep.retrieveall
objImpRep.Exportdbase("C:\data.dbf&quot
objImpRep.CloseReport
objImpApp.Quit
set objImpRep = Nothing
Set objImpApp = Nothing

End Sub