CODEWITHSUNDEEP
excelvba
Clare
Clare

Reputation: 109

LastWorkDay function not behaving as expected

I have a daily process that opens up a previous working days comments file, so if I'm running the macro for today (6th August) it opens up the file from the 5th of August.

It was working correctly yesterday, but this morning it seems to be looking for a date in the past. I'm unsure how to force it to look in the current year as it was previously doing.

Sub ImportComments()

'Imports comments from yesterday's Test Sheet 

Dim wbDaily As Workbook, wbPath As String
Dim LastWorkDay As Date

Application.DisplayAlerts = False

'Checks for an existing workbook with a date of yesterday and opens the 
workbook
wbPath = "W:\Test\Projects\test\test1\My File " & Format(LastWorkDay - 1, 
"dd.mm.yy") & ".xlsx"
If Len(Dir(wbPath)) > 0 Then  'workbook exists
    Set wbDaily = Workbooks.Open(wbPath)

 'Unhides any hidden rows and takes off filters before copying the data
 Worksheets("Data").ShowAllData
 Columns("A:BC").EntireColumn.Hidden = False

'Copies the data to the template import tab for vlookups
wbDaily.Worksheets("Data").Range("A:BC").Copy
 Windows("TestTemplate.xlsb").Activate
 Sheets("Import").Activate
 ActiveSheet.Paste Destination:=Worksheets("Import").Range("A1")


End If

Application.DisplayAlerts = True

Call RemoveFormulas

End Sub

The debugger tells me it's looking for a file with a date of 29/12/99

Upvotes: 0

Views: 32

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

What is LastWorkDay? it is not defined. You Dim LastWorkDay As Date but you never set a value to this variable so it is 0 by default that means you are using -1 as date and format it.

What you actually do is Format(0 - 1, "dd.mm.yy") because LastWorkDay is 0 which results in 29.12.99 which actually means 29.12.1899 because 1 is defined as 31.12.1899 in VBA so -1 is 2 days before.

So you need do initialize a date for your variable before you use it. For example use the WorksheetFunction.WorkDay method to find the previous work day of today:

Dim LastWorkDay As Date
LastWorkDay = Application.WorksheetFunction.WorkDay(Date, -1) 'get the date of the previous work day

Upvotes: 1

Related Questions