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:

Damian37

Board Regular
Joined
Jun 9, 2014
Messages
227
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

Hi Dante,
I did use the code to list the most current files, but not only is my code not working, I'm also receiving a run-time error 9 - Subscript is out of range. Here is the code I'm currently using:

Code:
Sub butt*******()
    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("*.xslx*") 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, "mm.dd.yyyy") & ".xlsx", FileFormat:=51
End Sub

Any and all help is greatly appreciated.

D.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Hi Dante,
I did use the code to list the most current files, but not only is my code not working, I'm also receiving a run-time error 9 - Subscript is out of range. Here is the code I'm currently using:

Any and all help is greatly appreciated.

D.


You tried the code of post #10
 

Damian37

Board Regular
Joined
Jun 9, 2014
Messages
227
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

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

Hi Dante,
I'm also getting the bolded line within the second part of code and, believe it has something to do with not having the file isn't open.

I did use the code to list the most current files, but not only is my code not working, I'm also receiving a run-time error 9 - Subscript is out of range. Here is the code I'm currently using:

Rich (BB code):
Sub butt*******()
     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("*.xslx*") 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, "mm.dd.yyyy") & ".xlsx", FileFormat:=51
End Sub

Any and all help is greatly appreciated.

D.
 
Last edited by a moderator:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Hi Dante,
I'm also getting the bolded line within the second part of code and, believe it has something to do with not having the file isn't open.

I did use the code to list the most current files, but not only is my code not working, I'm also receiving a run-time error 9 - Subscript is out of range. Here is the code I'm currently using:

Rich (BB code):
Sub butt*******()
     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("*.xslx*") 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, "mm.dd.yyyy") & ".xlsx", FileFormat:=51
End Sub

Any and all help is greatly appreciated.

D.

the error is because you do not have a sheet with that name
 
Last edited by a moderator:

Damian37

Board Regular
Joined
Jun 9, 2014
Messages
227

ADVERTISEMENT

the error is because you do not have a sheet with that name

Hi Dante,
I tried to use the code in #10 post, and it is working in identifying the newest files within the folder.
However, I am still unable to open up the most recent file within the folder. When I attempt to just run the open file portion of code,
I receive a run-type error message. If I try to use the button I have assigned the macro to, I receive a subscript out of range error, because the worksheet
I'm attempting to use isn't available, because the file was never opened. I am also using a version of Excel that
is newer than 2007. Here's the code I have tried to just use:
Code:
Sub butt*******()
    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
Thank you very 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
Hi Dante,
I tried to use the code in #10 post, and it is working in identifying the newest files within the folder.
However, I am still unable to open up the most recent file within the folder. When I attempt to just run the open file portion of code,
I receive a run-type error message. If I try to use the button I have assigned the macro to, I receive a subscript out of range error, because the worksheet
I'm attempting to use isn't available, because the file was never opened. I am also using a version of Excel that
is newer than 2007. Here's the code I have tried to just use:

Thank you very much!
D


The code that I put from post #5 works for me.
I added another option to open the file.


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

Try a folder with an excel file, the macro should open that file.
 
Last edited by a moderator:

Damian37

Board Regular
Joined
Jun 9, 2014
Messages
227
The code that I put from post #5 works for me.
I added another option to open the file.


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

Try a folder with an excel file, the macro should open that file.

Thank you Dante, I did get the code to work, and open another worksheet after creating a new worksheet with current date attached to naming convention ("Negative Replenishment file_" & Format(Date,"mm.dd.yyyy") & ".xlsx", FileFrmat:=51)
Which results in a new file being created with current date. However, now I would like to perform a vlookup in which I match the email address to the Regional Store Manager for a specific store. I've gotten my code to do everything successfully except for the vlookup portion. I'm trying to use a vlookup using a list of store numbers and Regional Store Managers address. I recorded a macro to perform a vlookup with both files, but, when I attempt to copy the formula into to all rows containing data, I receive the following error: Syntax error
Rich (BB code):
Sub vlookup() -- This I highlighted yellow with the below red bolded line is where I receive the Syntax error...…..
'
' vlookup Macro
'

'
    ActiveCell.FormulaR1C1 = "SMMO EMAIL"
    Range("AE2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(VALUE(RC[-30]),'[Mobile Stores - SMMO Listing.xlsx]Sheet1'!C1:C5,5,FALSE)"
    Range("AE3").Select
    Workbooks("Negative Replenishment file_" & Format(Date, "mm.dd.yyyy") & ".xlsx", FileFormat:=51)
    LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   
    Range("AE3:B" & LastRow).FillDown
End Sub
All help is greatly appreciated!!!
D.
 
Last edited by a moderator:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Thank you Dante, I did get the code to work, and open another worksheet after creating a new worksheet with current date attached to naming convention ("Negative Replenishment file_" & Format(Date,"mm.dd.yyyy") & ".xlsx", FileFrmat:=51)
Which results in a new file being created with current date. However, now I would like to perform a vlookup in which I match the email address to the Regional Store Manager for a specific store. I've gotten my code to do everything successfully except for the vlookup portion. I'm trying to use a vlookup using a list of store numbers and Regional Store Managers address. I recorded a macro to perform a vlookup with both files, but, when I attempt to copy the formula into to all rows containing data, I receive the following error: Syntax error
Code:
.[/QUOTE]

With pleasure I continue to help you, but this definitely corresponds to another topic. You could create another thread.
I suggest you do not use macro names or variables with reserved words. instead of vlookup you could put v_look_up or something like that.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,824
Messages
5,544,541
Members
410,619
Latest member
gregor222
Top