Searching Excel through VBA

Hi,

I'm using VBA within MS Project to open an Excel workbook that contains a mapping table. So far, I've been able to open the workbook, get cell value but I'm unable to execute a search.  Can anyone help on how to search Excel through VBA?

Here's the code that I have so far:

------------------------------------------------------------------------------------------------------------------------
Dim iExcel As Excel.Application
Dim iBook As Workbook
Dim iSheet As Worksheet
   
Sub RunExcel()
    Call OpenExcel("ControlFiles", _
        "\\HOUIC-S-01117\XXX.XXXXXX$\cached\My Documents\X_Files\Planning\BPMA\Sub-teams\Mapping.xls")
End Sub

Private Sub OpenExcel(strWorksheet As String, strWorkbook As String)
On Error GoTo ErrorHandler

        Set iExcel = CreateObject("excel.application")
        Set iBook = iExcel.Application.Workbooks.Open(strWorkbook)
        Set iSheet = iBook.Worksheets(strWorksheet)
       
        Call SearchExcel("Master")
       
        Set iSheet = Nothing
        iBook.Close True
       
        Set iBook = Nothing
        iExcel.Quit
       
        Set iExcel = Nothing
    Exit Sub
       
ErrorHandler:
    Call KillExcel
End Sub

Private Sub SearchExcel(strSearchValue As String)
On Error GoTo ErrorHandler
    Dim frg As Range
    Dim str1 As String, str2 As String
   
    Set frg = iSheet.Rows(1).Find(What:=strSearchValue, LookIn:=xlValues, LookAt:=xlPart)
   
    iExcel.Application.ActiveCell.Offset(0, 1).Activate
   
    Dim lngRow As Long
    Dim lngColumn As Long

    lngRow = iExcel.Application.Selection.row
    lngColumn = iExcel.Application.Selection.Column

    str1 = iExcel.Application.Cells(lngRow, lngColumn).Value
   
    Debug.Print "value: " & str1
   
    Exit Sub

ErrorHandler:
    Call KillExcel    
End Sub

Private Sub KillExcel()
    iExcel.Quit
    Set iExcel = Nothing
    Debug.Print "ERROR: " & Err.Description
End Sub
eciabattariAsked:
Who is Participating?
 
Jeroen RosinkSoftware testing consultantCommented:
perhaps this code works also, I removed a few lines:

Private Sub SearchExcel(strSearchValue As String)
On Error GoTo ErrorHandler
    Dim frg As Range
    Dim str1 As String, str2 As String
   
    Set frg = iSheet.Rows(1).Find(What:=strSearchValue, LookIn:=xlValues, LookAt:=xlPart)
   
    iExcel.Application.ActiveCell.Offset(0, 1).Value
    str1 = iExcel.Application.Cells(lngRow, lngColumn).Value
    Debug.Print "value: " & str1

    Exit Sub

ErrorHandler:
    Call KillExcel    
End Sub
0
 
seppertCommented:
Two possible options:
1) Program a VLOOKUP?
2)  loop through the cells looking for a match with strSearchValue



0
 
Jeroen RosinkSoftware testing consultantCommented:
Sorry removed the rwong line:
Private Sub SearchExcel(strSearchValue As String)
On Error GoTo ErrorHandler
    Dim frg As Range
    Dim str1 As String, str2 As String
    Set frg = iSheet.Rows(1).Find(What:=strSearchValue, LookIn:=xlValues, LookAt:=xlPart)
   
    str1 = frg.Offset(0, 1).Value
    Debug.Print "value: " & str1

    Exit Sub

ErrorHandler:
    Call KillExcel    
End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.