Get data from another excel file with not fixed worksheet name

Question

I have a excel file that contain daily order id and I need to get some data from other excel use the order id as index. The source file contain many worksheet that means a listbox with sheet name for selection is required. The workbook & worksheet used for data source is not fixed and will determine by user, so a listbox for user to select relevant worksheet is required The workflow is when i call the vba at the daily excel file, a listbox with all sheet name of the source excel file will pop up for select worksheet, then the daily excel file will get data from the source excel base on the order id as index.

Now I have a vba using activeworkbook and activeworksheet to set the lookup range, but I don't think this is a good coding method. Could someone can give me some suggestion?

For the userform code if the strfile is set to an exact file the code is fine, but the source file may be change.

All source files are save in same location, the required source file name is in Range("Z1") of the daily excel file, is it possible the strfile can change base on Range("Z1")?

Please let me know if I can clarify anything for you.

Sub example()
Dim dest_wbk As Workbook
Dim dest_ws As Worksheet
Dim source_wbk As Workbook
Dim source_ws As Worksheet
Set dest_wbk = ThisWorkbook
Set dest_ws = dest_wbk.ActiveSheet
sourcefilename = Range("Z1")

UserForm1.Show

Set source_wbk = ActiveWorkbook
Set source_ws = source_wbk.ActiveSheet

sourcelastrow = source_ws.Cells(Rows.Count, 2).End(xlUp).Row
Set lookuprange = source_ws.Range("A2:E" & sourcelastrow)

dest_lastrow = dest_ws.Cells(Rows.Count, 4).End(xlUp).Row
For i = 2 To des_lastrow
    ID = dest_ws.Range("D" & i)
    dest_ws.Range("K" & i) = Application.VLookup(ID, lookuprange, 3, False)
    dest_ws.Range("L" & i) = Application.VLookup(ID, lookuprange, 4, False)
Next i
    source_wbk.Close
End Sub

'Below in the code in the userform
Private Sub ListBox1_Click()
    Sheets(ListBox1.Value).Activate
    Unload Me 
End Sub

Private Sub UserForm_Initialize()
    Dim sh As Worksheet
    strfile = ("C:\Documents\" & sourcefilename)
    Set wbk = Workbooks.Open(strfile, ReadOnly:=True)
    For Each sh In wbk.Sheets
        ListBox1.AddItem sh.Name
    Next sh
End Sub

You dont need VBA. You can reference another workbook/sheet from a formula (vlookup). Open both files and edit the formula, then select the range in the other workbook you want to use. — user844705
Thank you for your reply. But the workbook & worksheet used for data source is not fixed and will determine by user, so a listbox for user to select relevant worksheet is required. — bob
Please read how to create a minimal reproducible example. As it stands this question is too broad to really answer and / or kind of unclear what exactly your question is. — Scott Holtzman

Dr Yunke Dr Yunke · Accepted Answer · 2018-03-27T01:59:26

You need to change your two variables dest_wbk and dest_ws to something like

In case your destination Workbook is already open

'Change Workbook2.xls to whatever the file is (assuming it is open already)
Set dest_wbk = Workbooks("Workbook2.xls")
'Change SheetName to whatever the sheet name is inside dest_wbk
Set dest_ws = dest_wbk.Sheets("SheetName")

Otherwise, you need to open the workbook

'Change Workbook2.xls to whatever the file is
Set dest_wbk = Workbooks.Open("Workbook2.xls")
'Change SheetName to whatever the sheet name is inside dest_wbk
Set dest_ws = dest_wbk.Sheets("SheetName")

It is up to you, to get those values (Workbook name and Sheet name) from the UserForm, which I believe it shouldn't be a problem for you.

Get data from another excel file with not fixed worksheet name

1 Answers