Automate Data Extraction Without Opening Destination Workbook




About this tutorial:

Video duration: 27:11
Our Excel training videos on YouTube cover formulas, functions and VBA. Useful for beginners as well as advanced learners. New upload every Thursday.
For details you can visit our website:

In today’s video we learn how to automate data extraction without opening destination workbook using Excel VBA. The concept can be used to get data from multiple workbooks into a new workbook. We can also get data on to user-forms automatically.

Get the book Excel 2016 Power Programming with VBA:…

Post Author: OfficeTutes.com

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

22 thoughts on “Automate Data Extraction Without Opening Destination Workbook

    Technically Specific

    (September 17, 2019 - 7:52 pm)

    LOVE IT!! SO EXCITED IT WORKS! How would I retrofit it to look in that other workbook, locate a specific named sheet "AcctgDept", then locate a specific column name "Tot_Inventory", then jump to the last row of that column and extract the Summed Total that's sitting in that last cell? (using your other macro to place those totals in that other sheet first, then I would run this one to go extract specific totals needed in this Master workbook???

    deepu hatti

    (September 17, 2019 - 7:52 pm)

    HI SIr,,

    thanks for update, have done the code , but having some error, can you please share mail id so i can send the file ,

    Harry Rawat

    (September 17, 2019 - 7:52 pm)

    Hi sir, I want to know how to define days and date if I have date and days in one cell….like
    Cells("A1"). value= Sunday 27/1/19
    I need this cell value to be another cell … please let me know

    Parag Shah

    (September 17, 2019 - 7:52 pm)

    Mr. Dinesh Takyar
    Can you please show VBA code, How to copy only last row from closed csv file to new excel workbook. Parag Shah. Thank you

    SATYEN PUROHIT

    (September 17, 2019 - 7:52 pm)

    Sir, I tried your codes and it works perfectly, the only problem i am facing is that I want to Extract data from multiple sheets of source file. and data of goal cell will be shown in each such sheets, so extracting of such data would not be a problem i guess. Please help me with codes,

    nader abdussalam

    (September 17, 2019 - 7:52 pm)

    Dear Dinesh,
    I am following your steps but I think I'm missing something as when i run the macro i get error message says the file could not be found in the pat. So can you help me please?

    Samarendra Pattanayak

    (September 17, 2019 - 7:52 pm)

    Hi sir
    I am working with excel everyday i want to copy columns from one excel to template.i want to learn also so i can make my work easier and error free. So please let me know how can i do that.my mail id spattanayak1986@ gmail.com

    ravi shekar

    (September 17, 2019 - 7:52 pm)

    sir in below code(transfer specific data to specific worksheets)
    data transferring one row below in every page
    from mydata page row 3,col 2 and row 3,2 is coming in row 2,col2 row2,col2 in another page
    (i want to row2 in row row 2 in seprae sheet)

    ravi shekar

    (September 17, 2019 - 7:52 pm)

    Sub transfertata()
    Application.ScreenUpdating = False
    'Dim myData As Worksheet, ItemA As Worksheet, ItemB As Worksheet, ItemC As Worksheet, ItemD As Worksheet, ItemE As Worksheet
    Dim ItemName As String
    Dim price As Long, qty As Long

    Dim a1 As Long, b1 As Long, c1 As Long, erow As Long
    r1 = 1
    r2 = 2
    r3 = 3

    Sheets(Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6")).Select
    Sheets("Sheet2").Activate
    Cells.Select
    Selection.ClearContents
    Sheets(Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6")).Select
    Sheets("Sheet2").Activate
    Range("A1").Select
    ActiveCell.Value = "Item"
    Range("B1").Select
    ActiveCell.Value = "Price"
    Range("C1").Select
    ActiveCell.Value = "Quantity"

    mydata.Activate
    Do While Cells(r1, 1) <> ""

    ItemName = Cells(r1, 1).Value
    r1 = r1 + 1
    price = Cells(r1, 2).Value
    r2 = r1 + 1
    qty = Cells(r1, 3).Value
    r3 = r1 + 1
    p = Worksheets.Count
    For q = 1 To p
    If ActiveWorkbook.Worksheets(q).CodeName = UCase(ItemName) Then
    Worksheets(q).Activate
    erow = Worksheets(q).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Cells(erow, 1).Value = ItemName
    Cells(erow, 2).Value = price
    Cells(erow, 3).Value = qty
    End If
    Next q
    mydata.Activate
    Loop
    Application.ScreenUpdating = True
    End Sub

    Doy Alegato

    (September 17, 2019 - 7:52 pm)

    Hi Takyar, I have like (300+ files in .iqy format) that has filename as sheetname whenever opened. The content of the iqy file is a website wherein it extracts the data to excel whenever it is ran. Would you be so kind to create a code that can bypass the name of the sheet to copy whatever the contents to a specific file?

    Manas behera

    (September 17, 2019 - 7:52 pm)

    Hi Dinesh Sir, I want to Extract sheet one data from a closed workbook to sheet 2 in the current workbook with a single button in sheet 1 current workbook

    J.Mc

    (September 17, 2019 - 7:52 pm)

    how would I go about extracting certain data ( Name and ID number)  from a program called reflection and have it entered into excel?

    john watkins

    (September 17, 2019 - 7:52 pm)

    perhaps do the samething i mentioned earlier but make an update info from another worksheet.

    john watkins

    (September 17, 2019 - 7:52 pm)

    what if i wanna pull data from a worksheet from inside the current workbook. i wanna copy data from sheet 3 and paste into sheet 1. how do i do it.

    Johnny Phan

    (September 17, 2019 - 7:52 pm)

    Hello again,
    would you help. how to extract database from ticketmaster.com (price, seat…)? thank you sir.

    Raju J

    (September 17, 2019 - 7:52 pm)

    Dear Dinesh sir .your upload  very useful and easy to understand. sir I want to copy  data in a  particular rows from 100 different  excel files in to new single excel file ,edit and put back the data in 50 excel files in the same row. All my 50 files use same  excel format . Can you please explain. I am engg. Awaiting your reply. Thank you regards

    Bhargav Reddy

    (September 17, 2019 - 7:52 pm)

    hello sir,
    this is bhargav reddy from Bangalore.
    please tell me how to transfer the data from workbook to new workbook, is it possible to write code for that please help me sir, i am new to to vba please give me some easy tutorials if you have
    thank you.

    Kenneth Lopez

    (September 17, 2019 - 7:52 pm)

    Sir I tried this VBA just want to know if there's multiple same entry name can we extract them all? or just the unique one?

    Kenneth Lopez

    (September 17, 2019 - 7:52 pm)

    Sir I tried this VBA just want to know if there's multiple same entry name can we extract them all? or just the unique one?

    Tim Dent

    (September 17, 2019 - 7:52 pm)

    I enjoyed this! You're the king of Excel VBA. Keep 'em coming.

    Calm Ore Ion Aswan

    (September 17, 2019 - 7:52 pm)

    This is realy helpful good sir. pls keep it up!

    Sak

    (September 17, 2019 - 7:52 pm)

    100 lines of code to find a single value from another workbook . that can be done with a singe Vlookup macro….Come on !!!!!

Leave a Reply

Your email address will not be published. Required fields are marked *