Please help!

[Lloydmp]

HI there, I am a complete novice in excel and get by by scavenging code off of forums and playing with them until i get them to work.I have how ever hit a wall. The following code works as a macro assigned to a button, but when i place it in the *This workbook page so the macro is run on start up i get the compiler error,*User-defined type not defined*I would very much appreciate some help and some advice on what i am doing wrong. Thank you https://www.mrexcel.com/forum/images/smilies/icon_nya.gifPrivate Sub Workbook_Open()Dim fdr, fdr2 As Scripting.Folder Dim target As Scripting.File For Each fdr In CreateObject("Scripting.FileSystemObject").GetFolder("S:\Systems Eng\Rolling Stock\MCU\Reports\Today's in service plan's\2018").SubFolders On Error Resume Next For Each target In fdr.Files If InStr(1, UCase(target), UCase(".xls")) > 0 Then If target.DateLastModified > dteFile Then dteFile = target.DateLastModified strFile = target End If End If Next Next DateFirstFile = dteFile FirstFile = strFile For Each fdr2 In CreateObject("Scripting.FileSystemObject").GetFolder("S:\Systems Eng\Rolling Stock\MCU\Reports\Today's in service plan's\2018").SubFolders On Error Resume Next For Each target In fdr2.Files If InStr(1, UCase(target), UCase(".xls")) > 0 Then If target.DateLastModified > dteFile Then dteFile = target.DateLastModified strFile = target End If End If Next Next DateSecondFile = dteFile SecondFile = strFile If DateFirstFile >= DateSecondFile Then If Len(Dir(FirstFile)) Then Workbooks.Open FirstFile ElseIf DateSecondFile >= DateFirstFile Then If Len(Dir(SecondFile)) Then Workbooks.Open SecondFile End IfEnd Sub

 

[Paul Ked]

Hi and welcome to the forum.

No fix, just put the code in a readable way!

Sub Workbook_Open()
    Dim fdr, fdr2 As Scripting.Folder
    Dim target As Scripting.File
    For Each fdr In CreateObject("Scripting.FileSystemObject").GetFolder("S:\Systems Eng\Rolling Stock\MCU\Reports\Today's in service plan's\2018").SubFolders
        On Error Resume Next
        For Each target In fdr.Files
            If InStr(1, UCase(target), UCase(".xls")) > 0 Then
                If target.DateLastModified > dteFile Then dteFile = target.DateLastModified
                    strFile = target
                End If
            End If
        Next
    Next
    DateFirstFile = dteFile
    FirstFile = strFile
    For Each fdr2 In CreateObject("Scripting.FileSystemObject").GetFolder("S:\Systems Eng\Rolling Stock\MCU\Reports\Today's in service plan's\2018").SubFolders
        On Error Resume Next
        For Each target In fdr2.Files
            If InStr(1, UCase(target), UCase(".xls")) > 0 Then
                If target.DateLastModified > dteFile Then
                    dteFile = target.DateLastModified
                    strFile = target
                End If
            End If
        Next
    Next
    DateSecondFile = dteFile
    SecondFile = strFile
    If DateFirstFile >= DateSecondFile Then
        If Len(Dir(FirstFile)) Then Workbooks.Open FirstFile
    ElseIf DateSecondFile >= DateFirstFile Then
            If Len(Dir(SecondFile)) Then Workbooks.Open SecondFile
    End If
End Sub

 

[Paul Ked]

To stop the error you need to have the reference to the 'Microsoft Scripting Runtime' ticked in the VBA Tools, References.

 

[Paul Ked]

Readable code corrected!:

Sub Workbook_Open()
    Dim fdr, fdr2 As Scripting.Folder
    Dim target As Scripting.File
    For Each fdr In CreateObject("Scripting.FileSystemObject").GetFolder("S:\Systems Eng\Rolling Stock\MCU\Reports\Today's in service plan's\2018").SubFolders
        On Error Resume Next
        For Each target In fdr.Files
            If InStr(1, UCase(target), UCase(".xls")) > 0 Then
                If target.DateLastModified > dteFile Then 
                    dteFile = target.DateLastModified
                    strFile = target
                End If
            End If
        Next
    Next
    DateFirstFile = dteFile
    FirstFile = strFile
    For Each fdr2 In CreateObject("Scripting.FileSystemObject").GetFolder("S:\Systems Eng\Rolling Stock\MCU\Reports\Today's in service plan's\2018").SubFolders
        On Error Resume Next
        For Each target In fdr2.Files
            If InStr(1, UCase(target), UCase(".xls")) > 0 Then
                If target.DateLastModified > dteFile Then
                    dteFile = target.DateLastModified
                    strFile = target
                End If
            End If
        Next
    Next
    DateSecondFile = dteFile
    SecondFile = strFile
    If DateFirstFile >= DateSecondFile Then
        If Len(Dir(FirstFile)) Then Workbooks.Open FirstFile
    ElseIf DateSecondFile >= DateFirstFile Then
            If Len(Dir(SecondFile)) Then Workbooks.Open SecondFile
    End If
End Sub

 

[Lloydmp]

Hi,

Thanks for the quick reply Paul,

My code looks exactly like your post, i just don't know how to post on this forum.
What i found is that i didn't have the right reference under tools ticked relating to scripts.
Its all sorted now!

Now can some one tell me how to set this page to done? :D
Thanks again Paul!

 

[Paul Ked]

Glad it's sorted.

To post code you can either a) Click the # button and insert your code between the square bracketed code tags or b) Paste your code and select it, then click the # button.

To preview your post click the 'Go Advanced' button at the bottom of the text box.

Cheers

 

[Lloydmp]

Once again mate thanks for the advice. I have been following this forum for most of all my excel questions and code. Hopefully my next Thread will be more legible