Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Question Unanswered: Open an excel workbook from access and search for...

    i have this working as below..

    Private Sub Command14_Click()

    Dim i As Variant
    Dim Result As Integer


    DoCmd.RunMacro "test1"
    With Application.FileSearch
    .NewSearch
    .LookIn = "\\xxx"
    .SearchSubFolders = True
    .FileName = "*.XLS"
    .MatchTextExactly = False
    .TextOrProperty = [PO] 'this is what i am looking for'
    If Len(.FileName) > 0 Then
    If .Execute > 0 Then
    DoCmd.Close acForm, "PleaseWait"
    MsgBox "There were " & .FoundFiles.Count & _
    " file(s) found.", vbOKOnly + vbInformation
    For i = 1 To .FoundFiles.Count
    Result = MsgBox(.FoundFiles(i), vbExclamation + vbOKCancel, "OK to View, Cancel to go to next.")
    If Result = vbOK Then
    Application.FollowHyperlink .FoundFiles(i)
    End If

    If Result = vbCancel Then
    End If
    Next i
    Else
    DoCmd.Close acForm, "PleaseWait"
    MsgBox "There were no files found."
    End If
    End If
    End With

    End Sub

    this all works and will open the excel workbook with the "[PO]" in it, but then i want it to 'Find all' "[PO]" and list them?

    Can this be done, if so can someone help me out please.

    Thanks
    Dave.

  2. #2
    Join Date
    Sep 2004
    Posts
    161
    With Excel you can create the following code (using tools menu, new macro)
    Code:
        Cells.Find(What:="xxxxxx", After:=ActiveCell, LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Activate
        Cells.FindNext(After:=ActiveCell).Activate
    and include in your access code

  3. #3
    Join Date
    Feb 2004
    Posts
    7

    Question

    Quote Originally Posted by jepi
    With Excel you can create the following code (using tools menu, new macro)
    Code:
        Cells.Find(What:="xxxxxx", After:=ActiveCell, LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Activate
        Cells.FindNext(After:=ActiveCell).Activate
    and include in your access code
    Sorry, i must be missing something, can i just add this code to mine? if so where?

    Sorry
    cheers
    Dave.

  4. #4
    Join Date
    Sep 2004
    Posts
    161
    Sorry i have dont see your method to open your workbook. With FollowHyperLink you can not control the application that open your file.
    For use my code you must create an instance of Excel in your access application,
    open your woorkbook, and use my code.
    You can found help in Excel

Posting Permissions

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