+91-8826547882

Print Value from Closed workbook

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

Hi,

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

 

Challenges:

  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.

 

Solution:

  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.

[php]
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
[/php]

May 28, 2016

0 Responses on Print Value from Closed workbook"

Leave a Message

© copyright www.excelcrazy.com. All rights reserved. Design and Development by www.creativebrainweb.com