Avatar of Adrian Raj
Adrian Raj
 asked on

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

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.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.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.Close false
'objWB2.Close false


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
Microsoft ExcelVB Script

Avatar of undefined
Last Comment
Adrian Raj

8/22/2022 - Mon
Saqib Husain

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Adrian Raj

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy