none
BEGIN CODE RRS feed

  • Question

  • Sub OpenLatestDataDetails()

     

     Dim ofile As Object
     Dim dteMax As Date
     Dim oFoundFile As Object
     Dim oFold As Object
     Dim FSO As Object
     Dim strFileName As String
     Set FSO = CreateObject("scripting.filesystemobject")
     'change folder to suit
     Set oFold = FSO.GetFolder("Z:\Reports\SyncFromProd\SopafDetails")
     dteMax = 0
     For Each ofile In oFold.Files
     x = ofile.Name
     y = ofile.datelastmodified
     Z = ofile.Type
     With ofile

         If .Type = "Microsoft Excel 97-2003 Worksheet" Or .Type = "Microsoft Excel Worksheet" Then
              If .Name Like "*Details*" And .datelastmodified > dteMax Then
                 Set oFoundFile = ofile
                 dteMax = .datelastmodified
             End If
         End If

     End With
     Next ofile
     Workbooks.Open oFoundFile.Path, ReadOnly:=True
     Set FSO = Nothing

     Range("A1").Select
     Range(Selection, Selection.End(xlToRight)).Select
         Range(Selection, Selection.End(xlDown)).Select
         Selection.Copy
         strFileName = ActiveWorkbook.Name
         Windows("PSC_Dashboard-2017-Draft-1.xlsb").Activate
         Sheets("Details").Select
         Range("A1").Select
         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
             :=False, Transpose:=False


         Workbooks(strFileName).Close

    End Sub

    Monday, March 27, 2017 12:19 AM

All replies