Lookup to Notepad?? Possible?

[Dave350z]

Hey folks,
I have a list of phone numbers, but it's in Notepad format, I have a second list of internal phone numbers and I want to check if my numbers are on the Notepad file?

I know you can convert the notepad to excel and do a lookup, but the Notepad file is 245mb which equates to about 20M give or take a few numbers, so I don't want the hassle of converting it to excel and splitting it out into 20 odd sheets or workbooks and doing a lookup then...

So question is, is it possible to do a lookup between excel and notepad without having to convert the notepad?

Cheers!

Dave

[K m]

No, it isn't possible

[shg]

I have no idea if you can read a 245MB string ...

Please Login or Register  to view this content.

Run Init in the Open event, and then ...

Please Login or Register  to view this content.

[Dave350z]

Thanks for this, this may seem like a dumb question, I can get into the VBA coding (Alt+F11) but what do I need to do after that? I'm very new to VBA coding...

[JosephP]

I'll be interested to see if shg's method works with a file that big. if it doesn't you could open a recordset from the file and then either load it into an array for searching or filter it repeatedly for each value in your worksheet

[shg]

Please Login or Register  to view this content.

Change the constant sFile to point to your file, then do Alt+F8 and run Init.

I couldn't load a 250MB string (my dummy dataset created by the last routine) -- I get an "Out of string space" error beyond about 150 MB (about 13M numbers) -- but the function works for me at that size. Curious, a little; Excel only consumes 330MB with that string loaded, and I've used workbooks that consumed well over 1GB.

If you can load the whole file, you're golden. I have no suggestion otherwise.

[JosephP]

fyi your code works for me in xl2011-no issues loading the file into the variable (8GB RAM)

[shg]

Cool .