Excel Macro Error "Object does not support this action" help

Question

I got this code from another excel forum...

Sub App_FileSearch_Example()

Dim OWB As Workbook

With Application.FileSearch

.NewSearch

.LookIn = \\Network\reports\

.Filename = "*.xlsx"

If .Execute(SortBy:=msoSortByLastModified, SortOrder:=msoSortOrderDescending) > 0 Then

For i1 = 1 To .FoundFiles.Count

Set OWB = Workbooks.Open(.FoundFiles(i1))

OWB.Save

OWB.Close

Next i1

End If

End With

End Sub

It gives me the "Object does not support this action" error on the line...

With Application.FileSearch

Does excel 2010 no longer support this?

Answer 1

Try this... You will want to swap out the message box at the end with a file open line...

Sub Read_Dir()

Dim ofile As Object

Dim dteMax As Date

Dim oFoundFile As Object

Dim oFold As Object

Dim FSO As Object

Set FSO = CreateObject("scripting.filesystemobject")

'change folder to suit

Set oFold = FSO.GetFolder("H:")

For Each ofile In oFold.Files

With ofile

    If .Type = "Microsoft Excel Worksheet" Then

        If .datelastmodified > dteMax Then

            Set oFoundFile = ofile

            dteMax = .datelastmodified

        End If

    End If

End With

Next ofile

MsgBox oFoundFile.Path

Set FSO = Nothing

End Sub

---

If this post answers your question, please mark it as the Answer... Jim Thomlinson

Answer 2

Thank you for the quick response. if i use..

Sub Read_Dir()

Dim ofile As Object

Dim oFold As Object

Dim FSO As Object

Set FSO = CreateObject("scripting.filesystemobject")

'change folder to suit

Set oFold = FSO.GetFolder("\\Network\reports\")

For Each ofile In oFold.Files

Selection.TypeText ofile.Name & vbCrLf

Next

Set FSO = Nothing

End Sub

how would i modify this to "open" the last modified file,

---

Answer 3

You will need quotes around \\Network\reports\. There may be other issues but that is a start...

Sub App_FileSearch_Example()

Dim OWB As Workbook

With Application.FileSearch

.NewSearch

.LookIn = "\\Network\reports\"

.Filename = "*.xlsx"

If .Execute(SortBy:=msoSortByLastModified, SortOrder:=msoSortOrderDescending) > 0 Then

For i1 = 1 To .FoundFiles.Count

Set OWB = Workbooks.Open(.FoundFiles(i1))

OWB.Save

OWB.Close

Next i1

End If

End With

End Sub

---

If this post answers your question, please mark it as the Answer... Jim Thomlinson

Answer 4

Filesearch is no longer supported

See this thread

http://social.answers.microsoft.com/Forums/en-US/officeprog/thread/c5e5c46a-5511-4c39-a4d4-c049cba67af6

---

Microsoft® MVP PowerPoint® since 2007.

Web Page: PowerPoint Alchemy Hints and Tips