Search for Value in Closed Workbook and copy Column to Active Sheet Column BE

**Winon** · 12-19-2011, 07:43 AM

Hi guys,

Why is this not possible? I can extract a range from a closed workbook, etc. But apparently what I am asking for seems just too much for Excel to handle.

On Active Sheet Cell BF7 I have an Alpha/numeric value, which I want to use to Search Sheet2 of a closed workbook, locate the column with that value and then copy that column to Cell BE9 on the Active Sheet of the Active WorkBook.

Please do not laugh, but this is what I attempted so far without any joy:

Please Login or Register  to view this content.

Is there anyone of you willing to fix this for me, please. Or can it not be done.

For security purposes we cannot consider opening the closed workbook.

Many Thanks

**StephenR** · 12-19-2011, 08:03 AM

You can't do that I'm afraid. You might be able to use ADO - have a look here http://www.rondebruin.nl/ado.htm - but it won't be straightforward if you really can't open the file.

**Winon** · 12-19-2011, 08:12 AM

Hello stephenR,

Thank you for the quick response.

I have tried Ron's ADO with every conceivable tweak, but yes, you seem to be right. Appears it just can not be done!

**StephenR** · 12-19-2011, 08:32 AM

I wouldn't say that necessarily. Could you import the relevant row/column into your open file and then do the Find?

**snb** · 12-19-2011, 08:58 AM

you can use in cell BE9

PHP Code:


=INDEX('E:\OF\[adressen 002.xls]Blad1'!A9:BF9,Row(),Match($BF$7,'E:\OF\[adressen 002.xls]Blad1'!$A$9:$BF$9,0))

and autofill in column BF downwards

PS. adapt the file's name

**Winon** · 12-19-2011, 10:28 AM

Thank you StephenR,

That is exactly what I am doing at the moment, but the process is a bit slow. It is quite a large field and to get the desired result takes about 15 seconds. To repeat this process 30 to 40 times just to "crunch" some numbers will eventually drive the user mad. I was hoping there might be a quicker way.

@ snb

Thank you for your Formula! it is SUPER QUICK, but on the saving of the file it takes FOR EVER. Literally a couple of minutes,
even with automatic calculation turned off.

Maybe I should see how I can use VBA to enter your Formula in BF9, copy it down, then copy - paste - values in that range to see if that works faster, and repeat this procedure for as many times it may be necessary. That should not have the same adverse effect as it has with 2000 rows of formulas.

Or is there a work around this, snb?

Thank you Gentlemen!

I am pretty certain that other users must have had the same situation. Was just wondering how they solved it?

Hope I get this one sorted out.

**snb** · 12-19-2011, 10:42 AM

Please Login or Register  to view this content.

**Winon** · 12-19-2011, 11:09 AM

Hi snb,

I owe you a Coke and a Bun!

Thanks for all your help. I shall definately use what you have posted. Much, much nicer and better of what I was going to do.

Stay in touch!

**snb** · 12-19-2011, 11:48 AM

I'd prefer a 'Stellenbos' claret

**Winon** · 12-19-2011, 12:57 PM

Quick report Back,

snb, Thank you. Everything is now going like a ROCKET! Retrieving the data and have its Values Pasted, is slick and fast. Saving the WorkBook is now also within the normal "Time Bracket" - Quick!

I would be very surprised if someone else could come up with a better way of solving this issue!

I shall mail you a Pic of the "Skelmbosch" Claret!!!

Search for Value in Closed Workbook and copy Column to Active Sheet Column BE

LinkBack

Thread Tools

Rate This Thread

Display

Search for Value in Closed Workbook and copy Column to Active Sheet Column BE

Re: Search for Value in Closed Workbook and copy Column to Active Sheet Column BE

Re: Search for Value in Closed Workbook and copy Column to Active Sheet Column BE

Re: Search for Value in Closed Workbook and copy Column to Active Sheet Column BE

Re: Search for Value in Closed Workbook and copy Column to Active Sheet Column BE

Re: Search for Value in Closed Workbook and copy Column to Active Sheet Column BE

Re: Search for Value in Closed Workbook and copy Column to Active Sheet Column BE

Re: Search for Value in Closed Workbook and copy Column to Active Sheet Column BE

Re: Search for Value in Closed Workbook and copy Column to Active Sheet Column BE

Re: Search for Value in Closed Workbook and copy Column to Active Sheet Column BE

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions