Search & copy data from any of 100 columns in closed WorkBook without opening it
Hi everyone,
I urgently need a kickstart with VBA code.
With value from a cell in Active Sheet, Search & Copy data from any of 100 columns in closed WorkBook without opening it, and then paste it to nth row of Column x in Active Sheet.
Any help will be greatly appreciated!!
Last edited by Winon; 12-13-2011 at 08:27 AM.
You need to install additional addons in order to be able to copy out of closed workbooks. That function is not available natively.
Going for Guru! Click the Star to the bottom left of this post if I helped!
Hi Miraun,
I don't agree totally. I do have code that extracts data from a fixed range in a closed book, in seconds. The problem that I am stuck with now is, that I need code to search for a column in a closed workbook and exract the info from that column.
There are BRILLIANT Gurus on this Forum, who will show us how it can be done!
Surely there must be someone, somewhere who has either an answer or link they can suggest?
search for surname in column header
in A1
PHP Code:=INDEX('E:\OF\[addresses 002.xls]Sheet1'!$A:$AE,ROW(),MATCH("surname",'E:\OF\[addresses 002.xls]Sheet1'!$A$1:$AE$1,0))
Last edited by snb; 12-13-2011 at 05:45 AM.
This should give you a start:
See following example-workbooks:
closedWBImport Imports values from test.xls;Sheet1;range A1:I11.
closedWBImport.xls
(note: "test.xls" just contains sample-data so you can see that it works; place it in C:\test\test.xls; or change the values in Sheet1; range B1:B3.
test.xls
_______________________________________________
Remember:
Read the rules!
Use code tags! Place [CODE] before the first line of code and [/CODE] after the last line of code.
Mark your post [SOLVED] if it has been answered satisfactorily.
To do this, click EDIT in your original post, click GO ADVANCED and set the PREFIX. It helps everybody!
If you are approve (or disapprove) of any members response to your problem, please click theicon underneath their post and comment. It matters.
@wamp
The method you use isn't a VBA method but the same 'link formula' approach I suggested.
Good Morning snb,
Thank you for your Formula, but it returns an error:
Which I have changed to:
Please Login or Register to view this content.
It retuns a #REF!Please Login or Register to view this content.
What am I doing wrong?
Hello Wamp,
Thank you for your reply,
I am already using similar formulas to retrieve static info. The problem that I am stuck with now is, that I need code to search for a column based on any of 150 values in a closed workbook and extract the info from that column.
snb seems to be onto something which I like. Just need to maybe polish it a bit!
slight modifications:
Try this new formula.Please Login or Register to view this content.
The formula is 'relative' because of the use of ROW(). so it returns the row of the cell that contains the formula.
You could test this formula to get row 10 in the found column:
Please Login or Register to view this content.
Because I'm unaware of the value in cell BF9, it's possible no match has been found. In that case the formula fails.
Last edited by snb; 12-13-2011 at 06:37 AM.
Thank You snb,
Actually both the Row(), or row number i.e "9" works, but I found a strange twist.
The values in row9 are Transposed from a Column and the ROW() function fails that test when it searches row9 for the value specified. Why? I don't know. Replace ROW() with the actual row number, and it works. That is, let us call it the "Header".
Then I use your Formula with the row number replaced by ROW() further down the column, and it does retrieve the required info below the "Header", exactly as I wanted!
Were you aware that the formula might respond this way with Transposed Data?
I was aware that ROW() makes this formula relative to the cell's row in which the formula resides.Were you aware that the formula might respond this way with Transposed Data?
What you mean with 'transposed data' is not clear to me without seeing a worksheet.
Hi wamp,
Have a look and see what "Maverick" a.k.a. snb has provided me with. Try it!
I shall quickly try and replicate what I am trying to explain and upload the WB.
Re: Search & copy data from any of 100 columns in closed WorkBook without opening it
Hello snb,
It is probably just an extended blue Monday for me, because when I was building a replication of what the "problem" seemed to be, it miraciously just dissapeared!
When that happend I changed your code back to just the following:
It works like a Charm!Please Login or Register to view this content.
Thank you for your time and unselfish support.
Thank you Sir!
Re rep,
Hi snb, I have done just so. It may be that your PM was Nano seconds faster than the Rep I have posted
I have also posted you to my wall of "People Of Note" a long time ago, and if I really find myself in a serious corner, I might just PM you!
Baie Dankie!!!
......exceeded their stored private messages quota and cannot accept further messages until they clear some space.
I fear you have to clear up the PM's yourself.
Niks nie dankie nie.
My side is clean thank you!
This line:
applies to on your sideexceeded their stored private messages quota and cannot accept further messages until they clear some space.
You are absolutely right !
One of the advantages of the 'up'graded forum: no indication of the inbox content...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks