• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

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
0
eciabattari
Asked:
eciabattari
  • 2
1 Solution
 
seppertCommented:
Two possible options:
1) Program a VLOOKUP?
2)  loop through the cells looking for a match with strSearchValue



0
 
Jeroen RosinkCommented:
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
 
Jeroen RosinkCommented:
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now