Compile error

D

Damian37

Active Member
Joined
Jun 9, 2014
Messages
301
Office Version
  1. 365
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:
Sort by date Sort by votes
DanteAmor said:
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
Click to expand...

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:
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Damian37 said:
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.
Click to expand...


You tried the code of post #10
 
Upvote 0
DanteAmor said:
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
Click to expand...

DanteAmor said:
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
Click to expand...

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:
Upvote 0
Damian37 said:
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.
Click to expand...

the error is because you do not have a sheet with that name
 
Last edited by a moderator:
Upvote 0
DanteAmor said:
the error is because you do not have a sheet with that name
Click to expand...

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:
Upvote 0
Damian37 said:
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
Click to expand...


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:
Upvote 0
DanteAmor said:
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.
Click to expand...

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:
Upvote 0
Damian37 said:
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.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
File System Object Late Binding
Replies
2
Views
492
tiredofit
T
R
  • Question Question
Add a code or number at the end of the file name using VBA (Looped)
Replies
3
Views
122
HighAndWilder
H
S
  • Question Question
VBA - compile error. cannot find project or library
Replies
6
Views
480
SeniorNewbie
SeniorNewbie
T
  • Question Question
How do you populate a list of Sharepoint Files in A combobox
Replies
0
Views
694
thardin
T
H
  • Question Question
Permision Denied-VBA Code to list sub-folders
Replies
4
Views
122
howard
H

Forum statistics

Threads
1,224,823
Messages
6,181,185
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top