Populate array dynamimically from a different Excel workbook

Bruce Tjosvold

Use this construction. Change the sheet numbers where I have commented. I have tested the code using my own file paths.

Sub TestThis()

    Dim LR As Long
    Dim Tester_IDs As Variant
    Dim user_name As String
    Dim nMatch As Long

    user_name = "ANDERSD4"

    Workbooks.Open ("\\spnas2\spdept\dept\SAP_TECH\S:\Upload Master Templates\JEMASTER\Bruce_test\Tester_IDs.xlsx")
    
    LR = ActiveWorkbook.Sheets(2).Range("A" & Rows.Count).End(xlUp).Row    ''' change the Sheets() num to suit
    Set Tester_IDs = ActiveWorkbook.Sheets(2).Range("A1:A" & LR)    ''' change the Sheets() num to suit

    On Error Resume Next    ''' allow for not found
    nMatch = WorksheetFunction.Match(user_name, Tester_IDs, 0) + 1
    If nMatch > 0 Then
        MsgBox "Yes, it is there"
    Else
        MsgBox "No, not found"
    End If
    On Error GoTo 0

    Workbooks("Tester_IDs.xlsx").Close False   ''' remember to close the master template at some stage
    Set Tester_IDs = Nothing    ''' free up memory

End Sub

gmk,

Thanks for the help. I'm an experienced SAP ABAP developer. But this VBA is brand new to me, as is any Windows programming.

I have been tasked with modifying a very complex VBA application to replace numerous HARD CODED values with parameters that will be in several different EXCEL file.

You have started my on my way. I appreciate the help.

Bruce

Bruce

Pleased to be of assistance.

Please mark the the thread as solved if it is the answer you require and if you feel it is warrented then add to my reputation by clicking on the star at the bottom of my reply.

gmk