Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    San Francisco, California, USA
    Posts
    93
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Searching a column in a worksheet from Word

    hello,

    I've started an instance of Excel FROM WORD and need to search column D (or 4?) in a worksheet for a text string:

    xlBook As Excel.Workbook
    xlApp As Excel.Application
    xlSheet As Excel.Worksheet

    Set xlApp = New Excel.Application
    xlApp.Visible = True

    Set xlBook = xlApp.Workbooks.Open("C:\Users\" + Username + "\Documents\FAR Guide.xls", False, True)
    Set xlSheet = xlBook.Sheets("DFAR")

    ------------------------------------------------

    I can't figure out what syntax to use to search Column D in xlSheet. When the text string is found then I do some stuff in Word; however, the Range object (oRng) always comes back as Nothing.

    I've tried each of the following with no success:

    Dim strClause as string, Dim oRng as Range

    Set oRng = xlSheet.Columns("D:D").Find(What:=strClause, LookIn:=xlValues).Activate
    Set oRng = ActiveSheet.Range.("D:D").Find(What:=strClause, LookIn:=xlValues)
    Set oRng = xlSheet.Cells.Find(What:=strClause, LookIn:=xlValues)

    If Not (oRng Is Nothing) Then - I'll do some stuff in Word after this.

    I'd appreciate any help, thank you.
    Last edited by ajulyguy; 2016-09-15 at 19:55.

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    You were pretty much there.
    This will show you the cell address of the first found instance
    Code:
    Sub aa()
      Dim cell As Range
      Set cell = xlSheet.Columns("D:D").Find(What:=strClause, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
      If Not cell Is Nothing Then
        Debug.Print cell.Address
      End If
    End Sub
    Last edited by Andrew Lockton; 2016-09-15 at 23:53. Reason: Took out the After:=ActiveCell setting
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    San Francisco, California, USA
    Posts
    93
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Andrew! I really appreciate this. There are two errors that are coming up.

    ---------------------------------------------------------------------------------------------
    This always comes back as Nothing

    Set Cell = xlSheet.Columns("D").Find(What:=strClause, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    ---------------------------------------------------------------------------------------------

    And

    ---------------------------------------------------------------------------------------------
    Method or Data member not Found for:

    Debug.Print Cell.Address

    ---------------------------------------------------------------------------------------------

    Public xlBook As Excel.Workbook
    Public xlApp As Excel.Application
    Public xlSheet As Excel.Worksheet

    Sub OpenExcel()

    Set xlApp = New Excel.Application
    xlApp.Visible = True

    Set xlBook = xlApp.Workbooks.Open("C:\Users\" + Username + "\Documents\FAR Guide.xls", False, True) ' load and open xlWorkBook
    Set xlSheet = xlBook.Sheets("DFAR") ' here we will need to figure out way to get the right sheet/tab

    End Sub

    Sub BuildTable()
    Dim oRng As Range
    Dim Cell As Range
    Dim oParagraph As Paragraph
    Dim strClause As String
    Dim oTable as Table
    'Dim oDocMerge as Document (Public) in modPublic
    'Dim oDoc as Document (Public) in modPublic

    Set oTable = oDocMerge.Tables(1)

    For Each oParagraph In oDoc.Paragraphs
    If oParagraph.Range.Characters.Count > 0 Then
    strClause = oParagraph.Range.Text
    End If
    Set Cell = xlSheet.Columns("D").Find(What:=strClause, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not Cell Is Nothing Then
    Debug.Print Cell.Address
    'Next grab information from cell to plop into Word table (oTable)
    Else
    'Do other stuff to Word Table (oTable)
    End If
    Next oParagraph

    End Sub
    Last edited by ajulyguy; 2016-09-16 at 15:14.

  4. #4
    Star Lounger
    Join Date
    Jan 2003
    Location
    San Francisco, California, USA
    Posts
    93
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Andrew, what's interesting is that in the immediate window this:

    ?xlsheet.cells(2,4).text

    Returns the contents of cell 2 in Column D.

    252.203-7000

  5. #5
    Star Lounger
    Join Date
    Jan 2003
    Location
    San Francisco, California, USA
    Posts
    93
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I think I figured it out. I have a space at the end of strClause than is preventing a find...stay tuned.

  6. #6
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    I am guessing that strClause = oParagraph.Range.Text will include the return at the end of the paragraph. It is highly unlikely that the paragraph mark is being found in the excel cell.

    You need to be very careful of invisible characters when trying to match strings.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    FWIW, using:
    strClause = Split(oParagraph.Range.Text, vbCr)(0)
    will exclude both the return at the end of a paragraph and the end-of-cell marker at the end of a cell.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Very clever Paul.

    I've never thought of removing the vbCr that way and solving for end of cell markers at the same time is pure genius
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  9. #9
    Star Lounger
    Join Date
    Jan 2003
    Location
    San Francisco, California, USA
    Posts
    93
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Me either, Thank you Paul. Everything works now.

Posting Permissions

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