• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

To Extract data from multiple excel workbooks in a folder without actually opening those workbooks.

Reetesh

New Member
Hello,
I'm quite new to VBA and also not sure whether the question which I'm going to ask can be done or not.

I've a folder in which there are 10 workbooks. What i want to do is to Extract data from all those 10 workbooks into a master workbook.

Now, i do not want those 10 workbooks to be opened for copying.

So i wanted to ask, is there a VBA code to copy the data from all those 10 workbooks present in the folder and paste it into the master workbook, without actually opening those 10 workbooks.

Note: all those 10 workbooks are made in MS Excel Version 2003.

Thanks in advance
 
Reetesh
You should reread Forum Rules:
And after that You would figure something.
 
Hello Jolivanes,
I'll explain in detail what exactly I'm doing because of which I do not want the workbooks to open.

I've made an Activity Tracker which captures data for my team.

So I've made 10 workbooks (Dump File) separately for each Team Member, so that whenever they punch their on going activity on the tracker, the details are stored in these 10 workbooks.
For more clarity I'll give an example, If an individual Starts a task (By clicking on START button), the tracker then opens one the workbook, out of the 10 workbooks which is named under that Individual, enters their details saves that workbook and close it. All of this happens on the click of START button on the tracker.

Now currently I'm using a VBA code which extracts data from all the 10 workbooks (All the workbooks are in the same folder) and that VBA code des that by opening all the 10 workbooks, copying the data and then closing it without saving the workbooks. I use this either at the start of the day or at the end.

Sometimes I need to check the details in the middle of the day for everyone. So when I use the above mentioned method to extract data for everyone, its gives a Runtime Error to some of the users, because they've started or ended their activity and at the same time even I've tried to extract the data from the workbooks (So the workbook opens at the same time on both system).

Because of this reason, I was trying to figure out if there is any way to extract data from these 10 workbooks without actually have to open them, to avoid the above mentioned issue.

Hope I was able to explain what I want, if not kindly ask the details which you'll require.

I understand its a bit on an unusual one and I'm not even sure whether it can be done in VBA or is there any other method to do it. So thought of asking the experts here :)

Thanks in advance.

Note: All the workbooks and the Tracker is made on MS Excel Version 2003.
 
The only way I know how to is with formulas.

Code:
 =+'C:\WhichEverFolder\[HereTheFileName.xls]Sheet1'!A1

This will give you whatever is in A1 of Sheet1 of that workbook in that folder.
If you pul the formula down you get it for all of column A.
Change the A1 to B1 and pull it down for Column B
etc
etc

Or have a macro in your workbook putting the formulas in by code.

I doubt that it will give you data from that workbook that someone else has open and has not been saved.
Never tried it.
 
Thanks for the reply jolivanes.
However, I think this would work only if I want to extract data from one workbook, right?
Actually, the data needs to be extracted from all the workbooks present in the folder (10 workbooks).
 
If you change the workbook names, it'll get data from that workbook.
Cumbersome, yes, impossible, no.
How many sheets for each workbook?
If you enter the formulas with a macro, it would be pretty fast if you have all the workbook names and sheets in a range.
 
There is one worksheet in all the workbooks from which the data needs to be extracted.

From "Column B" to "Column I", the data will be entered in these columns in all the workbooks.

The first row in all the workbooks is header, so the first row should not be pasted in the Master file.

So the data from all the workbooks should be copied from the second row of "Column B" to "Column I".

Could you guide me on how I can use the formula which you mentioned with a Macro, or help me with the VBA code??
Frankly, I'm new to VBA I might not be able to come up with a VBA code with this formula in it.

Thanks in advance mate.
 
How long are the workbook names where you retrieve the data from?
If these are >30 characters, they can't be used for sheet names so you know where the data originates from.
Otherwise I have to name the sheets something like Sheet1, Sheet2, Sheet3 etc.
 
Forgot to ask.
How many rows total for each sheet for columns B to I? Include some extras for good measurement if you want to.
 
Play around with the attached workbook.
Read the comments and change whatever is required.
I do not know if it works because you have a total different setup obviously.
 

Attachments

  • Reetesh at Chandoo.xlsm
    17.8 KB · Views: 11
Posting Rules & Etiquette
Say "Thanks", whenever you can. Recognize when someone, who is a total stranger, has bothered to go to the trouble and time to assist you with your question and they have done it for free. Often readers will spend several hours working on a solution to a problem, a line of recognition and thanks will go a long way.
 
Back
Top