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
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