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:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Get rid of the word "Set". You do not use set when assigning values to string, only when assiging values to objects, like ranges.
 
Upvote 0
Joe4 said:
Get rid of the word "Set". You do not use set when assigning values to string, only when assiging values to objects, like ranges.
Click to expand...
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.
 
Upvote 0
You only use "With" statements with objects like ranges, worksheets, workbooks, etc.
You cannot use them with strings (it doesn't really make sense).
 
Upvote 0
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:
Upvote 0
DanteAmor said:
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
Click to expand...

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:
Upvote 0
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
 
Upvote 0
Damian37 said:
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.
Click to expand...
<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.
    Tools-in-Excel-VB-Editor-Toolbar.png
  2. Click on References.
    Reference-Option-in-Excel-VB-Editor-Toolbar.png
  3. In the References dialog box that opens, scroll through the available references and check the ‘Microsoft Scripting Runtime’ option.
    Scrrun-Microsoft-Scripting-Runtime-Library-Option-Checked.png
  4. Click OK.

</article></section>
 
Upvote 0
DanteAmor said:
Click Ok
Click to expand...
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:
Upvote 0
Damian37 said:
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.
Click to expand...


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:
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,180
Members
453,021
Latest member
Justyna P

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