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.Applic ation")
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(fi le1)
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.Coun t).Formula = "=VLOOKUP($A:$A,?????!$A:$ A,1,false) "
objWS1.Range("B1:B" & objWS1.UsedRange.Rows.Coun t).Formula = objWS2.Range("B1:B" & objWS2.UsedRange.Rows.Coun t).Value
'objWS1.Columns(2).Insert
'objWS1.Range("B1:B" & objWS1.UsedRange.Rows.Coun t).Formula = "=VLOOKUP(INDEX($A:$A,ROW( )),file1.x lsx!$A:$A, 1,false)"
'objWS1.Range("B1:B" & objWS1.UsedRange.Rows.Coun t).Formula = objWS2.Range("B1:B" & objWS1.UsedRange.Rows.Coun t).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
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.Applic
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(fi
Set objWS1 = objWB1.Sheets(Sheet_1)
'Set objWB2 = objExcel.Workbooks.Open("C
Set objWS2 = objWB1.Sheets(Sheet_2)
objWS2.Columns(2).Insert
objWS2.Range("B1:B" & objWS2.UsedRange.Rows.Coun
objWS1.Range("B1:B" & objWS1.UsedRange.Rows.Coun
'objWS1.Columns(2).Insert
'objWS1.Range("B1:B" & objWS1.UsedRange.Rows.Coun
'objWS1.Range("B1:B" & objWS1.UsedRange.Rows.Coun
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
nicely done, it works. I just have to change to objWS1.name because the table array is from Sheet_1. Thanks for the help.