Print Value from Closed workbook

Share Now

Last week one student sent this question over email, so sharing it now..


I am a student of Analytix Lab and I have attended your class held on 3rd & 9th January 2016 on Excel Macro.

Few days ago, I had appeared in an interview for the post of “Analyst” in “Indus Tower”. In final round they asked me to prepare a excel macro program which was:-

There is an excel file which consist data of 2500 customers having columns of Customer ID, name, address, tariff plan, billing date etc. and there is another excel file which have few customer ID (lets assume 50) whom have filed complaint regarding services. So, the requirement was: as soon as we write customer ID in second excel file, the respective name should appear automatically in front of that ID.

There was condition of not to use VLOOKUP and Record Macro by any how.

I am looking forward to hearing from you for the solution or idea.

Thanking You,

Gaurav Chandra Srivastava


  1. Workbook is closed from where we want to fetch the result.
  2. Run macro when user enter the customer ID in workbook 2 to print customer name.


  1. If workbook is closed we cannot fetch value through vlookup function in vba, so we will write here vlookup formula in VBA.
  2. To Run macro on Value Change, we will write our code in “Worksheet_Chnage” event

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub 

Now let’s perform this, we have two workbooks named as Workbook1 and Workbook2.

Workbook1 = File containing information Customer ID, name, address, tariff plan, billing date etc.

Workbook2 = File containing Customer ID’s, whom have filed complaint regarding services

Note: We assuming here that we have data in sheet1 of both workbooks.

Based of customer ID we want to print customer name from workbook1 that means we want to run macro on “Cell Value Changed” event.

You can use below code to perform the task, only need to update your workbook address in below Vlookup function.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim str As String
Dim val As String

If Not Intersect(Target, Range(“A2:A1048576”)) Is Nothing Then

val = Target.Address

Target.Offset(0, 1).Formula = “=VLOOKUP(” & val & “,’C:\Users\lokesh paliwal\Desktop\[Book11.xlsx]Sheet1′!$A:$B,2,0)”

Target.Offset(0, 1).Copy
Target.Offset(0, 1).PasteSpecial xlValues
Application.CutCopyMode = False

End If

End Sub

Share Now
November 15, 2019

0 responses on "Print Value from Closed workbook"

    Leave a Message

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

    Social Media

    facebook page EC Analytics Consulting   Linkedin Page EC Analytics Consulting   Youtube EC Analytics Consulting


    0124- 4601426


    EC Analytics will help your business make better decisions by providing expert-level business intelligence (BI) services. Forecasting, strategy, optimization, performance analysis, trend analysis, customer analysis, budget planning, financial reporting and more. EC Analytics also offers Advanced Data Analytics training in corporate and retail.

    EC Analytics Consulting @ 2019 ALL RIGHTS RESERVED