CODEWITHSUNDEEP
excelvbams-word
zuzi
zuzi

Reputation: 3

How can I reference two different sheets in an excel file from word using vba?

I am trying to get some information using Vlookup function from an Excel file to a Word file using VBA. I have two different sheets with different tables in the excel.

This is my code:

Private Sub CommandButton1_Click()
           
    Dim objExcel As New Excel.Application
    Dim WB As Excel.Workbook
    Dim SH1 As Excel.Worksheet
    Dim SH2 As Excel.Worksheet
    
    Set WB = objExcel.Workbooks.Open("C:\Users\WB.xlsx")
    WB.Worksheets(Array("Sheet1", "Sheet2")).Select
    
    Set SH1 = WB.Worksheets("Sheet1")
    Set SH2 = WB.Worksheets("Sheet2")
       
    Dim LookupValue1 As Variant
    Dim FirstColumn1 As Long
    Dim LastColumn1 As Long
    Dim ColumnIndex1 As Long
    Dim FirstRow1 As Long
    Dim LastRow1 As Long
    Dim Result1 As Variant
    Dim Array1 As Variant
          
    LookupValue1 = Me.ComboBox1
    FirstColumn1 = 1
    LastColumn1 = 2
    ColumnIndex1 = 2
    FirstRow1 = 1
    LastRow1 = WB.Application.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
           
    Set Array1 = Range(Cells(FirstRow1, FirstColumn1), Cells(LastRow1, LastColumn1))
        
    Result1 = SH1.Application.WorksheetFunction.VLookup(LookupValue1, Array1, ColumnIndex1, False)
            
    ThisDocument.TextBox2 = Result1
         
    Dim LookupValue2 As Variant
    Dim FirstColumn2 As Long
    Dim LastColumn2 As Long
    Dim ColumnIndex2 As Long
    Dim FirstRow2 As Long
    Dim LastRow2 As Long
    Dim Result2 As Variant
    Dim Array2 As Variant
    
    LookupValue2 = Me.ComboBox2
    FirstColumn2 = 1
    LastColumn2 = 4
    ColumnIndex2 = 2
    FirstRow2 = 1
    LastRow2 = WB.Application.Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

    Set Array2 = Range(Cells(FirstRow2, FirstColumn2), Cells(LastRow2, LastColumn2))
        
    Result2 = SH2.Application.WorksheetFunction.VLookup(LookupValue2, Array2, ColumnIndex2, False)
                
    ThisDocument.TextBox10 = Result2
     
    WB.Close
    Set WB = Nothing
End Sub

The two lookup functions work when I run them individually, but when I run the whole code I get message "Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class" either for the first or second function.

Result1 = SH1.Application.WorksheetFunction.VLookup(LookupValue1, Array1, ColumnIndex1, False)

or

Result2 = SH2.Application.WorksheetFunction.VLookup(LookupValue2, Array2, ColumnIndex2, False)

I am guessing that the problem might be with active worksheets in excel, but I defined where to look for the information. Any ideas, please, how can it be fixed?

Upvotes: 0

Views: 77

Answers (2)

FaneDuru
FaneDuru

Reputation: 42256

Try the next simplified code, please:

Sub openExcelDirect()
 Dim objExcel As New Excel.Application
    Dim WB As Excel.Workbook, SH1 As Excel.Worksheet, SH2 As Excel.Worksheet
    Dim LastRow1 As Long, LastRow2 As Long, Array1 As Excel.Range, Array2 As Excel.Range
    Dim Result1, Result2, LookupValue1, LookupValue2
    
    Set WB = objExcel.Workbooks.Open("C:\Users\WB.xlsx")
    objExcel.Visible = True 'this allows you to see the newly created session window
                            'you may comment this line when everything runs smooth
    Set SH1 = WB.Worksheets("Sheet1")
    Set SH2 = WB.Worksheets("Sheet2")
    
    LastRow1 = SH1.Range("A" & SH1.Rows.Count).End(xlUp).Row
    LastRow2 = SH2.Range("A" & SH2.Rows.Count).End(xlUp).Row
    Set Array1 = SH1.Range(SH1.Cells(1, 1), SH1.Cells(LastRow1, 2)) 'well qualified range
    Set Array2 = SH2.Range(SH2.Cells(1, 1), SH2.Cells(LastRow1, 4)) 'well qualified range
    
    LookupValue1 = Me.ComboBox1
    LookupValue2 = Me.ComboBox2
    
    Result1 = SH1.Application.WorksheetFunction.VLookup(LookupValue1, Array1, 2, False)
    Result2 = SH2.Application.WorksheetFunction.VLookup(LookupValue2, Array2, 2, False)
    'Debug.Print Result1, Result2
    ThisDocument.TextBox2 = Result1
    ThisDocument.TextBox10 = Result2
     
    WB.Close
End Sub

Your main code problem is to not fully qualify the ranges...

When you tried selecting of the both sheets, this did not change anything in terms of the active sheet, which can be only one.

Upvotes: 1

JohnnieL
JohnnieL

Reputation: 1231

Application.Worksheetfunction.VLookup throws this error when it doesnt find a match regardless of whether the 4th parameter is True or False so I think you have to write an On Error ... trap for it.

You can test that by makign a few tests with Debug.Print etc

so I would recommend something like

  Result1 = CVErr(xlErrNA)



  On Error Resume Next
  Result1 = SH1.Application.WorksheetFunction.VLookup( _
  LookupValue1, Array1, ColumnIndex1, False)
  On Error GoTo 0

Upvotes: 0

Related Questions