Retrieve data from a workbook to a userform in a different work book

Question

I have an Excel work book which is acting as a database and a UserForm which acts as a UI. Both are in different workbooks.

I want to populate the UserForm with data from Excel workbook .

Private Sub CommandButton4_Click()
    Dim n As Long, i As Long
    n = 0
    Dim mydata1 As Workbook        

    Set mydata1 = Workbooks.Open("\\NTSYDFSP150\Shared\fmd\credit\LEM_Reports\SV References\SV Entry Form Input.xlsx")

    mydata1.Worksheets("sheet1").Activate
    mydata1.Worksheets("sheet1").Range("A1").Select

    n = Worksheets("sheet1").Range("a1").CurrentRegion.Rows.Count

    For i = 2 To n 
        If Trim(Sheet1.Cells(i, 1)) <> Trim(UserForm1.TextBox157.Text) And i = n Then
            MsgBox ("Name not found") 
        End If

        If Trim(Sheet1.Cells(i, 1)) = Trim(UserForm1.TextBox157.Text) Then
            UserForm1.TextBox1.Text = Sheet1.Cells(i, 1)
            Exit For
        End If
    Next i

    mydata1.Save
    mydata1.Close

    MsgBox "Data searched successfully", 0, vbNullString
End Sub

Issue : When I run the code am not able to retrieve data from workbook Excel database.

Sheet1.Cells(i, 1): - This field still refers to Shee1 from User form work book while it should be referring to work book at shared drive location since I had activated and opened that .

Note: n is calculated correctly.

First of all you might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Applying this technique properly will probably already fix your issues. • Note that you didn't ask a question ([ask]) yet. • Note that `Sheet1.Cells(i, 1)` refers to the sheet in the workbook the code is written in. If you want to access a sheet in another workbook you need to specify that like `mydata1.Worksheets("sheet1")` refers to `Sheet1` in worksheet `mydata1`. It's explained in the link above. — Pᴇʜ, Sep 27 '19 at 06:31
Maybe try to avoid opening that second workbook alltogether and throw those values in an array and loop through that array — JvdV, Sep 27 '19 at 06:36

score 0 · Answer 1 · answered Sep 27 '19 at 06:31

I cleaned up your code and qualified the ranges where necessary. Not qualifying the ranges is most likely the error here. Example: Worksheets("sheet1").Range("a1"). ... needs to be mydata1.Worksheets("sheet1").Range("a1"). .... Try the following code:

Private Sub CommandButton4_Click()
Dim n As Long, i As Long
n = 0
Dim mydata1 As Workbook        

Set mydata1 = Workbooks.Open("\\NTSYDFSP150\Shared\fmd\credit\LEM_Reports\SV References\SV Entry Form Input.xlsx")


n = mydata1.Worksheets("sheet1").Range("a1").CurrentRegion.Rows.Count

For i = 2 To n 
    If Trim(mydata1.Sheet1.Cells(i, 1)) <> Trim(UserForm1.TextBox157.Text) And i = n Then
        MsgBox ("Name not found") 
    End If

    If Trim(mydata1.Sheet1.Cells(i, 1)) = Trim(UserForm1.TextBox157.Text) Then
        UserForm1.TextBox1.Text = mydata1.Sheet1.Cells(i, 1)
        Exit For
    End If
Next i

mydata1.Save
mydata1.Close

MsgBox "Data searched successfully", 0, vbNullString
End Sub

Note that activating the workbook and .Selecting a Range is not necessary in this case (so I deleted it) and should be avoided in general (see comment above for additional advice).

score 0 · Answer 2 · answered Sep 27 '19 at 07:32

This is just a suggested way to prevent opening another workbook:

Private Sub CommandButton4_Click()

Dim wbPath As String: wbPath = "\\NTSYDFSP150\Shared\fmd\credit\LEM_Reports\SV References\"
Dim wbName As String: wbName = "SV Entry Form Input.xlsx"
Dim wsName As String: wsName = "sheet1"
Dim arrList As Object: Set arrList = CreateObject("System.Collections.ArrayList")
Dim lr As Long, x As Long

'Get the last row from A column, notice we need R1C1 notation for Excel4Macro
lr = ExecuteExcel4Macro("MATCH(""zzz"",'" & wbPath & "[" & wbName & "]" & wsName & "'!C1)")

'Let's use an ArrayList to get our validation list
For x = 2 To lr
    arrList.Add Trim(ExecuteExcel4Macro("'" & wbPath & "[" & wbName & "]" & wsName & "'!R" & x & "C1"))
Next x

'Check if ArrayList contains your lookup value
If arrList.Contains(Trim(UserForm1.TextBox157.Text)) Then
    UserForm1.TextBox1.Text = UserForm1.TextBox157.Text
Else
    MsgBox ("Name not found")
End If

MsgBox "Data searched successfully"

End Sub

Retrieve data from a workbook to a userform in a different work book

2 Answers2