Compile error

Damian37

Board Regular
Joined
Jun 9, 2014
Messages
227
Hello all,
I'm attempting to write a script to open up the most current Excel spreadsheet once it has been placed into a specific folder. I've run into a compiling issue when I attempt to run my code.

Rich (BB code):
Sub OpenMostRecent()
    Dim fs As String, ws As Worksheet, i As Long
    Dim loc As Long, fdt As Double
    Set fs = "C:\Users\WindowsUser\Desktop\VBA Code Files"
    With fs
        .SearchSubFolders = False ' set to true if you want sub-folders included
        .FileType = msoFileTypeExcelWorkbooks
        .LookIn = Dir("C:\Users\WindowsUser\Desktop\VBA Code Files")
        If .Execute > 0 And .FoundFiles.Count > 0 Then
          loc = 0
          For i = 1 To .FoundFiles.Count
            If FileDateTime(.FoundFiles(i)) > fdt Then loc = i
          Next i
          Workbooks.Open .FoundFiles(loc)
        Else
            MsgBox "No files found"
        End If
    End With
End Sub
Any and all help is always appreciated.

D.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,843
Office Version
  1. 365
Platform
  1. Windows
Get rid of the word "Set". You do not use set when assigning values to string, only when assiging values to objects, like ranges.
 

Damian37

Board Regular
Joined
Jun 9, 2014
Messages
227
Get rid of the word "Set". You do not use set when assigning values to string, only when assiging values to objects, like ranges.
Thank you Joe for the assistance. I'm still receiving a compile error, but this time it pertains to there being no object within the With statement. Not exactly sure. During debug it highlights the line With fs. Thank you again for your help.

D.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,843
Office Version
  1. 365
Platform
  1. Windows
You only use "With" statements with objects like ranges, worksheets, workbooks, etc.
You cannot use them with strings (it doesn't really make sense).
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Use this to open up the most current Excel spreadsheet


Code:
Sub OpenMostRecent()
    Dim fso As FileSystemObject, folder As Object
    Dim wPath As String, wMax As Long, wFile As Variant
   
    wPath = "C:\Users\WindowsUser\Desktop\VBA Code Files"
   
    Set fso = CreateObject("scripting.FileSystemObject")
    Set folder = fso.getfolder(wPath)
    wMax = 0
    wFile = ""
    For Each wfiles In folder.Files
        If LCase(wfiles.Type) Like LCase("*Excel*") Then
            If fso.GetFile(wfiles).DateLastModified > wMax Then
                wMax = fso.GetFile(wfiles).DateLastModified
                wFile = wfiles
            End If
        End If
    Next
    If wFile <> "" Then Workbooks.Open wFile
End Sub
 
Last edited by a moderator:

Damian37

Board Regular
Joined
Jun 9, 2014
Messages
227
Use this to open up the most current Excel spreadsheet


Code:
Sub OpenMostRecent()
    Dim fso As FileSystemObject, folder As Object
    Dim wPath As String, wMax As Long, wFile As Variant
   
    wPath = "C:\Users\WindowsUser\Desktop\VBA Code Files"
   
    Set fso = CreateObject("scripting.FileSystemObject")
    Set folder = fso.getfolder(wPath)
    wMax = 0
    wFile = ""
    For Each wfiles In folder.Files
        If LCase(wfiles.Type) Like LCase("*Excel*") Then
            If fso.GetFile(wfiles).DateLastModified > wMax Then
                wMax = fso.GetFile(wfiles).DateLastModified
                wFile = wfiles
            End If
        End If
    Next
    If wFile <> "" Then Workbooks.Open wFile
End Sub

Hello Dante,
I appreciate your help, and I tried using the code you provided, but I am now receiving a User-defined type not defined. This is the code that is highlighted:
Code:
fso As FileSystemObject
I've also checked to make sure the Microsoft Active X Data Objects have been checked.
 
Last edited by a moderator:

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,678

ADVERTISEMENT

To use this proposed early binding method, you need to select the Microsoft Scripting Runtime option (Tools > References).

I would actually use late binding like so:

Code:
Dim fso As Object, folder As Object

HTH

Robert
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Hello Dante,
I appreciate your help, and I tried using the code you provided, but I am now receiving a User-defined type not defined. This is the code that is highlighted:
Code:
fso As FileSystemObject
I've also checked to make sure the Microsoft Active X Data Objects have been checked.
<section class="bSe left" style="margin: 0px; padding: 0px; border: 0px; outline: 0px; vertical-align: baseline; background: transparent; float: left; width: 799.188px;"><article style="margin: 0px; padding: 0px; border: 0px; outline: 0px; vertical-align: baseline; background: transparent; color: rgb(52, 52, 52); line-height: 1.6em; box-sizing: border-box; position: relative;">


  1. In the VB Editor, click on Tools.
  2. Click on References.
  3. In the References dialog box that opens, scroll through the available references and check the ‘Microsoft Scripting Runtime’ option.
  4. Click OK.

</article></section>
 

Damian37

Board Regular
Joined
Jun 9, 2014
Messages
227
Hello Dante,
I modified my code as you suggested, and the code is running without error. However, the code seems to only recognize the 6-19-2019 file even though there is a more recent file (I.e. 6-20-2019) I've pasted the code below.
Code:
Sub OpenMostRecent()
    Dim fso As Object, folder As Object
    Dim wPath As String, wMax As Long, wFile As Variant
  
    wPath = "C:\Users\WindowsUser\Desktop\VBA Code Files"
  
    Set fso = CreateObject("scripting.FileSystemObject")
    Set folder = fso.getfolder(wPath)
    wMax = 0
    wFile = ""
    For Each wfiles In folder.Files
        If LCase(wfiles.Type) Like LCase("*Excel*") Then
            If fso.GetFile(wfiles).DateLastModified > wMax Then
                wMax = fso.GetFile(wfiles).DateLastModified
                wFile = wfiles
            End If
        End If
    Next
    If wFile <> "" Then Workbooks.Open wFile
End Sub

Sub CopyNeg()
    Dim NewWb As Workbook
    Dim NewWs As Worksheet
    Dim CurWs As Worksheet
    Set CurWs = ActiveWorkbook.Worksheets("Report Sheet")
    Set NewWb = Workbooks.Add
    Set NewWs = NewWb.Sheets(1)
    CurWs.Range("A:T").AutoFilter Field:=20, Criteria1:="<0"
    CurWs.AutoFilter.Range.EntireRow.Copy
    NewWs.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    NewWb.SaveAs "C:\Users\WindowsUser\Desktop\VBA Code Files\Negative Replenishment file_" _
    & Format(Date, "yyyy.mm.dd") & ".xlsx", FileFormat:=51
End Sub

As always all help is forever appreciated! Thank you so much!
D.
 
Last edited by a moderator:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Hello Dante,
I modified my code as you suggested, and the code is running without error. However, the code seems to only recognize the 6-19-2019 file even though there is a more recent file (I.e. 6-20-2019) I've pasted the code below.

As always all help is forever appreciated! Thank you so much!
D.


The code reviews the modification dates (DateLastModified) and obtains the most current one.
Works for me

Try this, run this macro on a new sheet

Code:
Sub OpenMostRecent()
    Dim fso As Object, folder As Object
    Dim wPath As String, wMax As Long, wFile As Variant
   
    wPath = "C:\Users\WindowsUser\Desktop\VBA Code Files"
   
    Set fso = CreateObject("scripting.FileSystemObject")
    Set folder = fso.getfolder(wPath)
    wMax = 0
    wFile = ""
    dim i as long
    i = 2
    For Each wfiles In folder.Files
        If LCase(wfiles.Type) Like LCase("*Excel*") Then
            cells(i, "A").value = wfiles
            cells(i, "B").value = fso.GetFile(wfiles).DateLastModified
            i = i + 1
            'If fso.GetFile(wfiles).DateLastModified > wMax Then
            '    wMax = fso.GetFile(wfiles).DateLastModified
            '    wFile = wfiles
           ' End If
        End If
    Next
    'If wFile <> "" Then Workbooks.Open wFile
End Sub


Check the file with the most current modification date
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,113,836
Messages
5,544,596
Members
410,624
Latest member
smartsanjiv64
Top