July 26, 2017

Get Column Data from Closed Workbook

Posted on by Dinesh Kumar Takyar

How to get column data from a closed workbook into another workbook automatically using VBA. Here’s a picture of the workbook with data:

Workbook with data
Workbook with data

Here’s an image of the workbook which will get the data from the above workbook:

Receiving workbook with the macro
Receiving workbook with the macro

Watch the video before studying the complete VBA code to transfer column data from one workbook to another:

 

You can watch this video on YouTube.

Sub copyColData()
Dim lastRow As Long
Dim myApp As Excel.Application
Dim wkBk As Workbook
Dim wkSht As Object

Set myApp = CreateObject(“Excel.Application”)
Set wkBk = myApp.Workbooks.Open(“C:\Users\takyar\Desktop\mydata.xlsx”)

lastRow = wkBk.Sheets(1).Range(“D” & Rows.Count).End(xlUp).Row
wkBk.Sheets(1).Range(“D1:D” & lastRow).Copy
myApp.DisplayAlerts = False
wkBk.Close
myApp.Quit
Set wkBk = Nothing
Set myApp = Nothing
Set wkBk = ActiveWorkbook
Set wkSht = wkBk.Sheets(“Sheet1”)
wkSht.Activate
Range(“A1”).Select
wkSht.Paste
Exit Sub

End Sub

Further reading:

Get data from closed workbook

Looping through Directory

Loop through sub-folders

6 thoughts on “Get Column Data from Closed Workbook”

  1. Hey

    I’m a student from Israel and I love your channel !! It helps me a lot!

    I have a question for you

    Can you please show me how to get value from a closed work book using VBA and the user form.
    I have to put the employee number on the form, and then, his name should appear in the following text box as I update it.
    the names and Employee numbers are placed in another closed workbook file in a different track.

  2. Hello, my task requires me to use variables for my SheetName, because the data I want to get from another closed workbook is published weekly on a new Sheet, I’m required to get the last published sheet…. I have written the following code but still not working, kindly help me or suggest another way of doing this, mind you the close workbook is saved in a central databank, all changes take place centrally and must be updated on my worksheet, when any changes been made.

    Sub DataQuoteAufrufen()
    Range(“I57”).Select ‘activecell
    totalrows = ActiveCell.CurrentRegion.Rows.Count – 1

    For x = 1 To totalrows
    mypath = ActiveCell.Offset(x, -4).Value
    mysheet = ActiveCell.Offset(x, -3).Value
    mycell = ActiveCell.Offset(x, -2).Value

    Workbooks.Open Filename:=mypath
    ActiveWorkbook.Sheets(mysheet).Activate
    mygotvalue = Range(mycell).Value

    ActiveWorkbook.Close False
    Windows(“ZTL-Infosheet.xlsm”).Activate
    ActiveCell.Offset(x, 0).Value = mygotvalue

    Next x

    End Sub

  3. Hello,
    I’m french student and I work on VBA project, your channel is very good to learn.

    I have a question, when I use this code, I have an error message
    “The license information for this component can not be found. You do not have the appropriate license to use this feature in the authoring environment.”
    Have you a reply for this probleme?

    Thank you in advance!!

  6. Hello,
    I have been trying to get the following script to work which you used in your video: ‘How to get column data from closed workbook’.
    When I run it, I get: Run-time error ’13’ Type mismatch
    When I debug I get:
    Set myApp = CreateObject(“Excel.Application”) highlighted in yellow.
    Any idea why?… I am using Excel for Mac.
    Thank-you!

Comments are closed.