Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Question Unanswered: Run-time error -2147024809 (80070057)

    Hy erveryone,

    I have written some marcos in Excell, which are looking checking my harddisk for documents, which start with a specific word. Then the document's path is split up and a hyperlink is set to the document as well.

    This works fine for several documents, but as soon as I use "contrat*.doc" I get the above runtime error with the note: Application-defined or object-defined error.
    The strange thing is that replacing the "o" with any other letter solves the problem, allthough replacing it wit "?" or "*" doesn't help.

    I used the same exact code with other words and they all work fine. Any idea what that could be and how I can solve that?

    Here is the code:

    Sub GetContrats()

    Range("a1").Select
    ActiveCell.EntireRow.Font.Bold = True
    ActiveCell = "Full Path"
    ActiveCell.Offset(1, 0).Select

    ActiveWindow.FreezePanes = True

    With Application.FileSearch
    .LookIn = "X:"
    .SearchSubFolders = True
    .FileName = "contrat*.doc"
    If .Execute(SortBy:=msoSortByFileName, _
    SortOrder:=msoSortOrderDescending) > 0 Then
    MsgBox "There were " & .FoundFiles.Count & _
    " file(s) found."
    For i = 1 To .FoundFiles.Count
    ActiveCell = (.FoundFiles(i))

    ActiveCell.Offset(0, 8).FormulaR1C1 = "=HYPERLINK(RC[-8])"

    ActiveCell.Offset(1, 0).Select
    Next i
    Else
    MsgBox "There were no files found."
    End If

    End With

    Columns("A:A").Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess _
    , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :="\", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
    1), Array(6, 1), Array(7, 1))

    Range("a1").Select
    ActiveCell.Offset(0, 1) = "Drive"
    ActiveCell.Offset(0, 2) = "Client"
    ActiveCell.Offset(0, 3) = "Project"
    ActiveCell.Offset(0, 4) = "Subfolder1"
    ActiveCell.Offset(0, 5) = "Subfolder2"
    ActiveCell.Offset(0, 6) = "Document Name"
    ActiveCell.Offset(0, 7) = "Hyperlink"
    ActiveCell.Offset(0, 9) = "Last updated: " & Now
    ActiveCell.Offset(0, 9).Font.ColorIndex = 3

    Cells.Select
    Cells.EntireColumn.AutoFit


    Cells.Select
    Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range("D2") _
    , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom


    Columns("A:A").Select
    Selection.EntireColumn.Hidden = True

    End Sub

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    This works fine for several documents, but as soon as I use "contrat*.doc" I get the above runtime error with the note: Application-defined or object-defined error.
    When you write "other documents" do you mean other Word documents (i.e., nnsm.doc)? Or do you mean other files, as in Excel.

    The object model for Word is different than for Excel. Perhaps you can check in VB Editor the references, and see whether MS Word is checked.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Mar 2004
    Posts
    3
    By other documents I mean other word documents. The only thing that changes is the name of the document. For example "facture*.doc" works perfectly fine.

    Regards,

    DoubleB

  4. #4
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    Don't know, but a guess would be a very long filename that confusing/overloading the FileSearch object. Try putting a debug.print foundfiles(i) inside your for loop. You might be able to see which file it's tripping on. Use CTRL+G to view the immediate window.

  5. #5
    Join Date
    Mar 2004
    Posts
    3
    Thanks actuary,

    you put me on the right track. I made a search in explorer for word documents of the name of "contrat*.doc" and found a document standing under X:. Once I moved this file into a subfolder, the macro worked fine.
    Is that because I set the SearchSubFolders to TRUE?

    Thanks everyone for your help!

    Regards,

    DoubleB

Posting Permissions

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