CODEWITHSUNDEEP
vbaexcelvlookup
Abigal
Abigal

Reputation: 143

Excel Userform VBA VLOOKUP from intranet file

I have a VBA Vlookup on a user form from which I would like the data to come from an excel file saved on the intranet.

When I try the VLookup which takes the data on the intranet, it works as follows: 

=VLOOKUP(I6,'https://intranet.private.private.uk/folder1/folder2/folder3/Documents/[Contract database.xlsm]AvailableContracts'!$H$9:$L$108,2,FALSE)

I would like to incorporate that link into the VBA below without getting an error message: 

Private Sub ContractsList_AfterUpdate()

If WorksheetFunction.CountIf(Sheet2.Range("A:A"), Me.ContractsList.Value) = 0 Then
    MsgBox "This contract is not on the list"
    Me.ContractsList.Value = ""
    Exit Sub
End If
'Lookup values based on first control
With Me
    .TextBox1 = Application.WorksheetFunction.VLookup(Me.ContractsList, Sheet2.Range("A5:E72"), 2, 0)
End With

End Sub

The line that kicks the error is:

.TextBox1 = Application.WorksheetFunction.VLookup(Me.ContractsList, Sheet2.Range("A5:E72"), 2, 0)

when I change Sheet2.Range("A5:E72") with the intranet link above in the excel VLookup formula.

Upvotes: 0

Views: 995

Answers (4)

Shai Rado
Shai Rado

Reputation: 33692

You could save a lot of time and code line using the Application.Match, look at the code below :

Private Sub ContractsList_AfterUpdate()

Dim WB As Workbook
Dim Sht As Worksheet

' set workbook to workbook location at internet (according to your formula)
Set WB = Workbooks.Open("https://intranet.private.private.uk/folder1/folder2/folder3/Documents/Contract database.xlsm")
Set Sht = WB.Worksheets("AvailableContracts")

With Me.ContractsList
    If Not IsError(Application.Match(.Value, Sht.Range("A:A"), 0)) Then '<-- value found in Column A
         'Lookup values based on first control
        Me.TextBox1 = Sht.Range("C" & Application.Match(.Value, Sheet2.Range("A:A"), 0)).Value
    Else '<-- value not found in Column A
        MsgBox "This contract is not on the list"
        .Value = ""
        Exit Sub
    End If
End With

End Sub

Upvotes: 2

Kyle
Kyle

Reputation: 2545

The below will open the workbook and allow you to work with it like any other workbook. I updated your code to include the new workbook and the range you gave in your VLOOKUP from above. I don't think VBA will let you do the lookup like you are hoping. 

Private Sub ContractsList_AfterUpdate()
Dim wb as Workbook

Set wb = Workbooks.Open("https://intranet.private.private.uk/folder1/folder2/folder3/Documents/Contract database.xlsm")
If WorksheetFunction.CountIf(Sheet2.Range("A:A"), Me.ContractsList.Value) = 0 Then
    MsgBox "This contract is not on the list"
    Me.ContractsList.Value = ""
    Exit Sub
End If
'Lookup values based on first control
With Me
    .TextBox1 = Application.WorksheetFunction.VLookup(Me.ContractsList, wb.Sheets("AvailableContractsRange").Range("H9:L108"), 2, 0)
End With

End Sub

Upvotes: 2

Joe Carey
Joe Carey

Reputation: 76

It looks like you've commented out your

With Me

line, but that may just be the formatting in your question.

It would be helpful to know which error message you received.

Upvotes: 0

Vinnie
Vinnie

Reputation: 553

How about this? You should determine the workbook as well.

 Private Sub ContractsList_AfterUpdate()

        If WorksheetFunction.CountIf(Workbooks("Contract database").sheet2.Range("A:A"), Me.ContractsList.Value) = 0 Then
            MsgBox "This contract is not on the list"
            Me.ContractsList.Value = ""
        Exit Sub

        End If
        'Lookup values based on first control
        With Me

            .TextBox1 = Application.WorksheetFunction.VLookup(Me.ContractsList, Workbooks("Contract database").sheet2.Range("A5:E72"), 2, 0)

        End With
 End Sub

Upvotes: 0

Related Questions