Solved

Vlookup excel via vb script - cannot pass PREFERRED sheet to retrieve information

Posted on 2016-09-01
2
52 Views
Last Modified: 2016-09-02
Hi Experts,

I'm a beginner in vb script and i came across a script(attached below) below to vlookup two files. I made few changes to vlookup 2 sheets in a single file.
Dim objExcel, objWB1, objWB2, objWS1, objWS2

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

file1 = InputBox("Enter the path\name to the excel file:")
Sheet_1 = InputBox("Enter the sheet name to retrieve information from:")
Sheet_2 = InputBox("Enter the lookup sheet name:")

Set objWB1 = objExcel.Workbooks.Open(file1)
Set objWS1 = objWB1.Sheets(Sheet_1)

'Set objWB2 = objExcel.Workbooks.Open("C:\My Documents\Microsoft Scripts\Amended Script\New\file2.xlsx")
Set objWS2 = objWB1.Sheets(Sheet_2)

objWS2.Columns(2).Insert

objWS2.Range("B1:B" & objWS2.UsedRange.Rows.Count).Formula = "=VLOOKUP($A:$A,?????!$A:$A,1,false)"
objWS1.Range("B1:B" & objWS1.UsedRange.Rows.Count).Formula = objWS2.Range("B1:B" & objWS2.UsedRange.Rows.Count).Value

'objWS1.Columns(2).Insert

'objWS1.Range("B1:B" & objWS1.UsedRange.Rows.Count).Formula = "=VLOOKUP(INDEX($A:$A,ROW()),file1.xlsx!$A:$A,1,false)"
'objWS1.Range("B1:B" & objWS1.UsedRange.Rows.Count).Formula = objWS2.Range("B1:B" & objWS1.UsedRange.Rows.Count).Value

objWB1.Save
objWB1.Close false
'objWB2.Close false

objExcel.Quit

I am not able to choose the sheet i want to retrieve the information from, thus i have inserted "?????" in the vlookup formula because im not sure what variable to insert. I tried using object set objWS1 and Sheet_1, but did not work. I want to be able to vlookup any sheet and return any information(column) to the vlookup sheet in the in the excel ,thus i have inserted inputbox to prompt user to input the sheet name. I have attached a file1.xlsx for testing purposes. Appreciate your help on this. Let me know if you need more information
0
Comment
Question by:Adrian Raj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 41781078
I think this is what you are looking for

objWS2.Range("B1:B" & objWS2.UsedRange.Rows.Count).Formula = "=VLOOKUP($A:$A,'" & objws2.name & "'!$A:$A,1,false)"
1
 

Author Comment

by:Adrian Raj
ID: 41781468
hi Saqib,

nicely done, it works. I just have to change to objWS1.name because the table array is from Sheet_1. Thanks for the help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question