1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'VBA Macros' started by Mehmud Khan, Mar 14, 2019.

  1. Mehmud Khan

    Mehmud Khan Member

    Messages:
    64
    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.

    Attached Files:

  2. Mehmud Khan

    Mehmud Khan Member

    Messages:
    64
    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
  3. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    984
    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.

    Attached Files:

  4. vletm

    vletm Excel Ninja

    Messages:
    4,918
    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.

    Attached Files:

    Mehmud Khan and sathishsusa like this.
  5. Mehmud Khan

    Mehmud Khan Member

    Messages:
    64


    --

    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.

  6. Mehmud Khan

    Mehmud Khan Member

    Messages:
    64

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

    vletm Excel Ninja

    Messages:
    4,918
    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?
  8. Mehmud Khan

    Mehmud Khan Member

    Messages:
    64
    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.
  9. vletm

    vletm Excel Ninja

    Messages:
    4,918
    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: Mar 15, 2019 at 9:43 AM
  10. Mehmud Khan

    Mehmud Khan Member

    Messages:
    64
    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.

    Attached Files:

  11. vletm

    vletm Excel Ninja

    Messages:
    4,918
    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?
  12. Mehmud Khan

    Mehmud Khan Member

    Messages:
    64
    Hi,

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

    Code (vb):

    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: Mar 15, 2019 at 4:30 PM
  13. vletm

    vletm Excel Ninja

    Messages:
    4,918
    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!
  14. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    984
    With small changes, same idea.

    Attached Files:

  15. Mehmud Khan

    Mehmud Khan Member

    Messages:
    64
    ----

    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.....
  16. Mehmud Khan

    Mehmud Khan Member

    Messages:
    64

    ---

    Thanks sir...
    Is it possible by VBA?
    I have no idea of the Query tool..

    Please help me so as I can implement on the main working file...
  17. vletm

    vletm Excel Ninja

    Messages:
    4,918
    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

Share This Page