I'm also interested in solution of this problem.
I have more than 65536 rows of data so I'm doing a vlookup in two
sheets, with if iserror vlookup in sheet1 vlookup in sheet2. But it's
slow and sheet2 in couple of weeks will become more than 65000 limit,
so doing lookup in three sheets almost imposable.
Going a little bit from subject, I want to say that new excel 2007
support more than million rows, and there is beta version available and
it calculates much faster and uses both parts of my dual core
processor, so I connected the text file of my original data trough ODBC
and database import and it works, but there is a problem with beta
version of this new excel, when you save such big file you will not be
able to reopen it later, excel says that data is corrupted and he needs
to recover it, but can't. So prior to save it I'm deleting data sheet.
Saying the truth I'm doing lookup from two different and such a big
text files and it's getting worse. I know that there should be a better
solution. Since I don't know any SQL maybe somebody can help.
Just an idea. It sholud be a custom excel function, just like vlookup,
so it could be dragged down using excel's functionality, so if the
function is =ourlookup(A1,2) when you drag it down it changes to
=ourlookup(A2,2). Inside of this custom function should be SQL which
access to any database, in this case to my ODBC connected text file.
But if it would be function does it necessary for the function to
connect to database and select and close for each row?

How to do that?

Sorry for my English.

RB Smissaert wrote:
Have tested now and this works fine:

Sub test()

Dim rs As ADODB.Recordset
Dim TempTextConn As String
Dim strQuery As String

TempTextConn = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
"C:\TempTables\;" & _
"Extended Properties=Text;"

'query example :
'---------------
strQuery = "SELECT " & _
"P.PATIENT_ID, " & _
"P.OLD_EXTERNAL_NO, " & _
"P.FORENAME_1, " & _
"P.SURNAME, " & _
"P.AGE, " & _
"P.GENDER_TYPE, " & _
"P.ADDRESS_LINE_2, " & _
"P.REGISTERED_GP, " & _
"E.READ_CODE, " & _
"E.START_DATE, " & _
"E.ADDED_DATE " & _
"INTO ResultFile.txt IN " & _
"'C:\TempTables\' " & _
"'Text;FMT=Delimited' " & _
"FROM " & _
"2IDALL.txt P INNER JOIN 3Morb_FULL.txt E ON " & _
"(P.PATIENT_ID = E.PATIENT_ID) "

Set rs = New ADODB.Recordset

rs.Open Source:=strQuery, _
ActiveConnection:=TempTextConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText

'can't close RecordSet here as it is closed already
Set rs = Nothing

End Sub


RBS


"Hari" wrote in message
oups.com...
RBS,

Thanks for your response. I have used SQL within SAS (and in a small
way in Oracle) but havent come across where Joins are done directly on
text files. I would like to learn this method of yours. When you say
text file, can it be done on CSV format files?

(Just to clue you in, as to how I was planning to approach this was by
doing an non-equi join to produce a table which doesnt have te lookup
values and secondly an equi join to produce a table which contains the
lookup values, then I was planning to stack these tables one below the
other with an additional column indicating as to what table they are
from.)

Regards,
HP
India