• 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.

Extract data in 1 workbook from another workbooks without opening.

Good morning friends,

I want to extract the data from specific excel sheets into 1 sheet at the same time.

In below example, I have 3 workbook which contains some data in the specific sheet, I want to consolidate them into 1 sheet without opening them.

(Please refer the attached excel files for better understanding)
upload_2019-3-14_11-5-55.png

Hope somebody will understand this and revert me as they get some solution.

Thanks in advance.
 

Attachments

  • Consolidated Master File.xlsx
    13.6 KB · Views: 19
  • Amit.xlsx
    10.4 KB · Views: 16
  • Ramesh.xlsx
    10.6 KB · Views: 17
  • Rima.xlsx
    10.4 KB · Views: 14
one more thing if possible;

While clicking on the run button of the VBA, it should prompt like below;
(from which sheet you want to extract the data)

upload_2019-3-14_11-16-49.png
 
Hi Mehmud Khan,

You could as alternative to VBA choose for a Power Query solution using a parameter table.
I've done something simple to show how it is possible.

Note: on open you might get an empty "selection" table because the path to the files is different, but I added some comments on how to easily change that.

Or wait for VBA code by the fine ladies and gents who can deliver that.
 

Attachments

  • Consolidated Master File.xlsx
    33.5 KB · Views: 33
Hi Mehmud Khan,

You could as alternative to VBA choose for a Power Query solution using a parameter table.
I've done something simple to show how it is possible.

Note: on open you might get an empty "selection" table because the path to the files is different, but I added some comments on how to easily change that.

Or wait for VBA code by the fine ladies and gents who can deliver that.



--

Dear GraH - Guido,

Thank you so much for showing interest into this.
But, this is not the correct solution which I am looking for.

I think you consolidated all the sheets of all the files (i.e. Sheet name: Amit, Misc Data, Other).
But, I want the data only from the first sheets from all 3 files.

Please refer the vletm's reply below. it's more relevant.

thanks one more time.

If possible, please try to help me again.

 
Mehmud Khan
I want to consolidate them into 1 sheet without opening them.
Test this too...
If needed, You should update links.
Default is that all files are in same folder.


Hi, vletm,
Thank you very much...
many times you gave me the solutions.

But this time I think this is not the correct solution.

When I change the data from the main source file (i.e Amit file - Amit Sheet), then it's not extracting the correct data.

Please help me with this...
 
Mehmud Khan
You think something?
You asked something with those four files - okay?
Did it work? Yes/No?

After You change something -
do You know what to change that the result would be needed?
= Do You tell to Excel as You would like it work?
= It's much easier to get incorrect result than correct result!
Many things matter to get correct result!
Without proper details
... it's challenge to even guess
- what have You done?
 
I am so sorry for the inconvenience.
Let me explain what exactly I want...

Actually, I have 3 staffs are working under me.
I have given a file to each staff to fill their daily working production.

Now, every staff is filling their files on a daily basis.

The file names are based on the staff name (i.e. Amit, Ramesh, Rima).

In the excel file, there is a sheet based on their name wherein these 3 staffs are filling the productivity on a daily basis.
[e.g. In Amit Workbook there is a Sheet named "Amit", wherein Amit is filling his tasks on a daily basis.]

Now, I want to make a common file for my reference, wherein I can see the list of task filled by each staff.
Please refer to the "Consolidated Master File" in my first query attachment.

The Heading of each sheet in all 3 excel are same, hence it is a little bit easy while consolidating all three files.

I think it will help you to solve this issue.

Thanks a lot, vletm....
your work is really appreciable.
 
Now, I want to make a common file for my reference, wherein I can see the list of task filled by each staff.
How do You do that?
What have You done?
Even this won't work with any 'copy&paste'!
... nothing is not something 'bit easy' ... many things would be possible.

It's challenge to try to help,
if You won't answer to any question!

as I wrote:
It's much easier to get incorrect result than correct result!
Send that Your a common file for my reference,
then it would be possible to see ... what?
 
Last edited:
Hi,

As you asked,
Send that Your a common file for my reference,

The below-attached file is created by manual copy-paste.

But I want to update the same using VBA.
 

Attachments

  • Consolidated Master File.xlsx
    11.7 KB · Views: 11
Mehmud Khan
I asked answers ...
As I already wrote:
I(=You) want to consolidate them into 1 sheet without opening them.
... and now,
You sent ... isn't that same file as with original?
My sent file uses 'VBA' and
'pulls' those datas WITHOUT opening those files
as You have wanted!
Would You try to explain ... what else would You need?
 
Hi,

I am really very week in VBA, hence I tried to make some VBA script using recording method and also edited somewhere....

Code:
Sub Macro2()
'
    Application.ScreenUpdating = False
  
   Workbooks.Open ("C:\Users\Mehmud Khan\Desktop\New folder\Amit")
    Windows("Amit.xlsx").Activate
    Worksheets("Amit").Activate
    Range("A2:K2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Consolidated Master File.xlsx").Activate
    Range("A2").Select
    ActiveSheet.Paste
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Workbooks("Amit.xlsx").Close
  

   Workbooks.Open ("C:\Users\Mehmud Khan\Desktop\New folder\Ramesh")
    Windows("Ramesh.xlsx").Activate
    Worksheets("Ramesh").Activate
    Range("A2:K2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Consolidated Master File.xlsx").Activate
    ActiveSheet.Paste
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Workbooks("Ramesh.xlsx").Close
  
      
    Application.ScreenUpdating = True
  
End Sub

Please read the line one by one then u will know what I want....
 
Last edited by a moderator:
Mehmud Khan
1) Have You noticed below text?
Screen Shot 2018-12-20 at 13.32.42.png

2) I asked answers
... without answers, it's challenge to help

3) As Your original want was:
Extract data in 1 workbook from another workbooks without opening.
Which I tried to confirm many time!

... and Your code starts .. of course

Workbooks.Open
= with opening file
I don't have any more replies here!
 
--

I think you consolidated all the sheets of all the files (i.e. Sheet name: Amit, Misc Data, Other).
But, I want the data only from the first sheets from all 3 files.
Please refer the vletm's reply below. it's more relevant.

If possible, please try to help me again.
With small changes, same idea.
 

Attachments

  • Copy of Consolidated Master File.xlsx
    32.7 KB · Views: 22
Mehmud Khan
1) Have You noticed below text?
View attachment 58733

2) I asked answers
... without answers, it's challenge to help

3) As Your original want was:
Extract data in 1 workbook from another workbooks without opening.
Which I tried to confirm many time!

... and Your code starts .. of course

Workbooks.Open
= with opening file
I don't have any more replies here!

----

Hi, vletm...

As I told you, I am very week in VBA, hence I created the above script with the help of VBA recording and Google.

In my script, I have written "Workbooks.Open" the reason is I don't know how to extract the data from a closed sheet, hence I hide the Screen Updation and silently open the workbook.

Please refer my above script, and understand the steps one by one. then only you will able to know exactly what I want.

Please help me.....



Thank in advance.....
 
Mehmud Khan
Points:
-- Question > answer ( still skipping those )
-- without open > my version do it
-- with VBA > my version do it.
It's basically same as Your recording, but without open a file.
So far, I don't know other ways to do it 'without open a file'!
... which was the point of Your case!
Of course, this could do manually like:
open file | select range to copy | Ctrl+C | select range cell to paste | Ctrl+V
 
You Can Use ADO in VBA library

Sure, but I wouldn't recommend it to anyone who's not experienced in VBA to use it.

Maintenance, if there is some structure change in source, can be a pain to those not familiar with quirks of ADO (such as handling of mixed data type column, referencing table range etc). There is very little benefit to not opening the workbook when importing data as well.

However, vletm's method of using INDEX() function is much easier to manage, if not opening source workbooks is a requirement. Although, if PQ/Get & Transform is available, I'd recommend use of that, as it has better GUI to manage if changes occur in the source.
 
Sure, but I wouldn't recommend it to anyone who's not experienced in VBA to use it.

Maintenance, if there is some structure change in source, can be a pain to those not familiar with quirks of ADO (such as handling of mixed data type column, referencing table range etc). There is very little benefit to not opening the workbook when importing data as well.

However, vletm's method of using INDEX() function is much easier to manage, if not opening source workbooks is a requirement.
Hello!
Sure ,Firstly I like ADO :)and Secondly User Persist in that files Not Opening..
 
Back
Top