+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,101

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

    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
    Last edited by Winon; 12-19-2011 at 12:58 PM. Reason: Solved

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

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

    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.

  3. #3
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,101

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

    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!

  4. #4
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

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

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

  5. #5
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

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

    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



  6. #6
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,101

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

    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.

  7. #7
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

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

    Please Login or Register  to view this content.

  8. #8
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,101

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

    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!

  9. #9
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

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

    I'd prefer a 'Stellenbos' claret

  10. #10
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,101

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

    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!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1