database ODBC custom formula retrieval and other
 

Hi,
I am using vlookup formula in Excel to retrieve data from two sheets.
(since my data is more than 65536 rows I am dividing it to two
different sheets).
My original data comes as a text file export from other software.
Recently I've set up an ODBC text driver to connect to that text file
and it works, but it comes only to one worksheet. How to split it to
two sheets?
My second question is: Is that possible to write custom function in VB
to retrieve data right from ODBC connection, i.e. some replacement to
undermentioned formula.
I've recorded a macro during retrieval from ODBC, but it downloads
data and puts it on the all rows. I need to do a query for each cell
separately, like my original vlookup formula.

My actual lookup formula looks like this
=IF(ISERROR(VLOOKUP(A2,first_sheet,1,FALSE)),VLOOK UP(A2,second_sheet,2,FALSE),VLOOKUP(A2,first_sheet ,2,FALSE))


Thanks for your help.

database ODBC custom formula retrieval and other
 

Are you using ADO?

If yes, execute an SQL that returns a COUNT(*) of the number of records, say
CountR

65535 (save first row for column headers) MOD CountR gives you the number
of wheets you need; that is your loop variable

Use the PageSize property (set to 65535) to retrieve 65535 records per ADO
Recordset Page.

Use CopyFromRecordset to copy each Page to a new sheet

If you want to subselect data, write your SQL such that it selects ONLY the
data you need,

database ODBC custom formula retrieval and other
 

Thanks for the reply,
I am not using ADO (I don't know even what it is :)
I use only ODBC Microsoft's text file driver.
If somebody has the code already I would appreciate if he will share it.