CODEWITHSUNDEEP
excelvba
mrod
mrod

Reputation: 49

VBA subscript out of range for workbook name

I pulled some code from online to open the latest file in a folder, which seems to work well. Later in the code however, I added an additional line to SET that same, recent file which was opened. The Workbook subscipt is out range when trying this and I think it has something to do with the syntax? May need to incorporate additional quotes into the workbook name, any ideas?

The 'path' and 'latestFile' variables seem to be reading in correctly

Dim path$, file$, latestFile$
Dim LatestDate As Date, LMD As Date
Dim D As worksheet, dash As worksheet

'open latest file
path = "R:\Dept\"
If Right(path, 1) <> "\" Then path = path & "\"

file = Dir(path & "*.xls", vbNormal)

If Len(file) = 0 Then
    MsgBox "No files were found in the folder", vbExclamation
End If

Do While Len(file) > 0
    LMD = FileDateTime(path & file)
    If LMD > LatestDate Then
        latestFile = file
        LatestDate = LMD
    End If
    file = Dir
Loop


Application.DisplayAlerts = False
Workbooks.Open path & latestFile
Application.DisplayAlerts = True

Set dash = Workbooks("dashboard.xlsm").Worksheets("D data")
Set D = Workbooks(path & latestFile).Worksheets("D Data") 'error here

Upvotes: 0

Views: 1645

Answers (1)

Tim Williams
Tim Williams

Reputation: 166331

Just use the name:

Set D = Workbooks(latestFile).Worksheets("D Data")

But cleaner to do this:

Application.DisplayAlerts = False
Set D = Workbooks.Open(path & latestFile).Worksheets("D Data") '<< edit
Application.DisplayAlerts = True

Upvotes: 1

Related Questions