Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
Hi,
Can anyone help me please with a data validation issue. I have a set of Oracle Tables which are being used in a PeopleSoft Financials system. There is a requirement to validate all data being input into an Excel Spreadshet against these Oracle Tables. What would be the best way of doing this? I have looked at the Data Validation option within Excel but the volumes of data we are talking about here makes it not possible. Any help would be gratefuly apprecitated. Richard |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation
Define 'validation' in your context? Do you mean that an entry into some
specific cell in the Excel sheet must match some entry in a field in one of the Oracle tables? I would presume that the size of the Oracle database precludes reading it into an Excel sheet and doing the typical things like MATCH or even VLOOKUP(). If you have Read permissions to the Oracle database you may be able to use Oracle Objects for OLE (OO4O) http://www.oracle.com/technology/tec...ole/index.html You could use it to query the database for a match using coding in the worksheet's _Change() event handler. I used OO4O many years ago (think Office/Excel 97) in some work I did for SW Bell and the SBC Corporation and it worked quite well. About 3 lines of code was all it took to run a query against the database in Excel VBA. Your delay in validation will be in waiting for the results to get back to you over the network. You might consider waiting until all data is entered on the worksheet and then doing a validation of all entered items at one time. It would be a longish wait once, rather than possibly longish waits after each data item entry. Come up with a simple, quick to execute query that would return a minimum number of records to improve performance. "TP" wrote: Hi, Can anyone help me please with a data validation issue. I have a set of Oracle Tables which are being used in a PeopleSoft Financials system. There is a requirement to validate all data being input into an Excel Spreadshet against these Oracle Tables. What would be the best way of doing this? I have looked at the Data Validation option within Excel but the volumes of data we are talking about here makes it not possible. Any help would be gratefuly apprecitated. Richard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) | |||
Refresh existing data when changed in data validation list | New Users to Excel |