?
Solved

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

Posted on 2016-09-01
2
Medium Priority
?
66 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 2000 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

762 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