PDA

View Full Version : HELP! application.filesearch wont work in excel 2010



mlmcelligott
06-23-2014, 04:23 AM
Hi, could someone help me out with the code below. It worked in 2003 but not in 2010. I know nothing about VBA so I havent a hope in solving this on my own! The error is with application.filesearch. Hoping someone can work their magic and help get this working again. Thanks in advance.



Dim blah As FileSearch
Dim directory As String
Dim i As Integer, iNewest As Integer
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Risk")
Dim LastMod As Object

Sheets("VaR").Select
MsgBox "Clearing Previous Portfolio Information"

Sheets("VaR Works Export").Range("a4:h65536").ClearContents
Sheets("VaR Works Export").Range("i5:w65536").ClearContents

directory = "file://\\VarWorks"]\\VarWorks]"
Set blah = Application.FileSearch
With Application.FileSearch
.NewSearch
.Filename = "*VaR*"
.LookIn = directory
.LastModified = msoLastModifiedAnyTime
.Execute msoSortByLastModified, msoSortOrderDescending
End With
If blah.FoundFiles.Count = 0 Then
MsgBox "Has a VaR export report been generated?"
Exit Sub
Else
End If


ws.Range("b3").Value = blah.FoundFiles(1)

ws.Range("b4").Value = FileDateTime(blah.FoundFiles(1))
Workbooks.Open (blah.FoundFiles(1)), ReadOnly:=True
MsgBox "Opening Last Modified File"

Worksheets("VaR").Range(Cells(2, 1), Cells(Worksheets("VaR").Cells(Rows.Count, 1).End(xlUp).Row, 8)).Copy
ThisWorkbook.Activate

Sheets("Risk").Select
MsgBox "Check DMD"
Sheets("VaR").Select
Cells(3, 1).Select

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Cells(3, 1).End(xlDown).Select
lastrow = ActiveCell.Row
Debug.Print lastrow
If lastrow > 4 Then
Workbooks("VaR Calculator.xls").Worksheets("VaR").Range("i4:w" & Trim(Str(lastrow))).FillDown
MsgBox "Data Updated"

End If
Workbooks(Dir(blah.FoundFiles(1))).Close
Name blah.FoundFiles(1) As "\\headfop006v\Archive\"]\\headfop006v\Archive" & Format(FileDateTime(blah.FoundFiles(1)), "YYYYMMDDHHMM") & "_VaR.xls"

MsgBox "DMD Archived"


End Sub

GTO
06-23-2014, 04:28 AM
Application.FileSearch was removed (no longer works) in 2007. I would suggest searching FSO remedies.

Kenneth Hobs
06-23-2014, 07:52 AM
Welcome to the forum!

A few threads you might try are:
http://www.vbaexpress.com/forum/showthread.php?49603
http://www.vbaexpress.com/forum/showthread.php?49572
http://www.vbaexpress.com/forum/showthread.php?45692
http://answers.microsoft.com/en-us/office/forum/office_2010-customize/i-have-a-spreadsheet-which-uses/669118de-e3a5-4815-bb95-0a83eb6bf984

If you have a question about those, please post the thread number in a reply to this thread.