Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Return Range of Excel WrkSht from Access Procedure (Access/Excel 2000/7)

    HI all,

    I'm just looking for a little advice regarding returning the Cell Address Within an Excel Spreadsheet from an Access Module prior to importing the Spreadsheet.

    Just to define my problem; I've got a series of Spreadsheets I need to import into Access for Processing, the only problem is I've no control over structure and need to train the software to adapt to changes in structure. I need to look at the Column headings ("A1:BD1") in the Excel spreadsheet and parse for keywords and then change the Heading for standardization when imported to access for application to internal data structures.

    I've Written the Keyword list and the Changes i Need to make.

    But to reference the "Value" I need to have the Range in the First Row that contains the Data to be revised.

    SO HOW DO I "SEARCH" THE EXCEL SPREADSHEET AND RETURN THE CELL ADDRESS???

    Best Wishes Graliv

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Return Range of Excel WrkSht from Access Procedure (Access/Excel 2000/

    Here is some sample code that you can adapt for your purposes. It uses late binding, so you don't have to set a reference to the Excel object library.
    <code>
    Sub SearchXL()
    Dim xlApp As Object
    Dim xlWbk As Object
    Dim xlWsh As Object
    Dim xlRng As Object
    Dim blnStart As Boolean

    ' Get or start Excel application object
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If xlApp Is Nothing Then
    Set xlApp = CreateObject("Excel.Application")
    If xlApp Is Nothing Then
    MsgBox "Can't start Excel.", vbCritical
    Exit Sub
    End If
    blnStart = True
    End If
    On Error GoTo ErrHandler

    ' Open workbook
    Set xlWbk = xlApp.Workbooks.Open("...")
    ' Get worksheet
    Set xlWsh = xlWbk.Worksheets("...")
    ' Search for keyword
    Set xlRng = xlWsh.Range("A1:BD1").Find(What:="...", _
    LookIn:=-4163, LookAt:=1)
    If xlRng Is Nothing Then
    MsgBox "Keyword not found.", vbInformation
    Else
    ' Do something with the value
    MsgBox "Keyword found in " & xlRng.Address
    End If
    ' Save workbook (optional)
    xlWbk.Save

    ExitHandler:
    ' Clean up
    On Error Resume Next
    Set xlRng = Nothing
    Set xlWsh = Nothing
    xlWbk.Close SaveChanges:=False
    Set xlWbk = Nothing
    If blnStart = True Then
    xlApp.Quit
    End If
    Set xlApp = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    </code>
    You need to substitute something for "..." of course - either a literal string value, a variable or an expression.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Return Range of Excel WrkSht from Access Procedure (Access/Excel 2000/

    Thank-you Hans that saves me scratching my head over manuals for a few hours:

    For anyone else who references this thread the key phrase is:

    Set xlRng = xlWsh.Range("A1:BD1").Find(What:="...", _
    LookIn:=-4163, LookAt:=1)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •