I'm hoping for some direction on this: Given my level of expertise I was able to use a Web Query exported from Oracle's Discoverer Plus to connect to an Oracle view. I setup the query in Excel to use Parameters in a cell. To populate this field, I have a macro to take a table column, remove duplicates & put a comma between values.
When I tested this, things were working good so I ignorantly spend some time spiffing up the user input form and connecting some basic lookups to the Oracle output.
Anyway, after really testing it I have found that I can only pass about 8 values as parameters. (I think this is related to the max length of a URL of ~255 characters?) This kills my forms generic appeal as I might be looking at 100 to 5000 unique values in a given week. I have set out to find a work around and so far I have come up with nothing that helps be @ my level of expertise (which is little!).
More than likely, I should be using an ODBC connection in VBA using SQL. This is beyond my understanding. I have the thought of splitting by values up in batches of 8 and having multiple web queries update based on the batches. This doesn't seem like a very elegant solution but it would probably work.
Before I go down this path; is there an obvious solution?
Finally, I hesitate to add this, but another option I can think of is to create a macro to put the values into a temp txt file and run a Discoverer report through the command line and finally do a vlookup to the results.
When I tested this, things were working good so I ignorantly spend some time spiffing up the user input form and connecting some basic lookups to the Oracle output.
Anyway, after really testing it I have found that I can only pass about 8 values as parameters. (I think this is related to the max length of a URL of ~255 characters?) This kills my forms generic appeal as I might be looking at 100 to 5000 unique values in a given week. I have set out to find a work around and so far I have come up with nothing that helps be @ my level of expertise (which is little!).
More than likely, I should be using an ODBC connection in VBA using SQL. This is beyond my understanding. I have the thought of splitting by values up in batches of 8 and having multiple web queries update based on the batches. This doesn't seem like a very elegant solution but it would probably work.
Before I go down this path; is there an obvious solution?
Finally, I hesitate to add this, but another option I can think of is to create a macro to put the values into a temp txt file and run a Discoverer report through the command line and finally do a vlookup to the results.
