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

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.

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

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.

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