Start Free Trial

asked on

save as workday

I use the below to save files which works fine but I need to change it to save as Workday.

sFilename = "JK Daily" 'Set filename
sPath = "C:\DATA\JKE\LSO \MI\Test\" 'Set Test base path
If Right(sPath, 1) <> "\" Then sPath = sPath & "\" 'Check for "\"
sPath = sPath & Format(Date, "yyyy\\mm mmm yyyy\\")
If Dir(sPath, vbDirectory) = "" Then 'If folder doesn't exist, create it
Shell "cmd /c mkdir """ & sPath & """"
End If
ActiveWorkbook.SaveAs sPath & sFilename & Format(Date - 1, " ddmmyyyy") & ".xls"

I thought it would be a case of changing the last line to :

ActiveWorkbook.SaveAs sPath & sFilename & Format(Application.WorkDay(Date, -1), "DD/MM/YYYY") & ".xls"

But this does not work.

Please assist.

Thanks

perhaps something like this

ActiveWorkbook.SaveAs sPath & sFilename & Format(WORKDAY(date,-1) " ddmmyyyy") & ".xls"

ASKER

Unfortunately that does not work. Expected List Separator so I put comma after (Date, -1) but then I got Sub or function not defined

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Saqib Husain, Syed thanks that works a treat and so simple.

ASKER

ProfessorJimJam that works as well thanks.

ASKER

both as good but you have to put one down as the best sorry JimJam

it is fine. glad it worked for you.

Saqib is a very knowledgeable fellow. I never cease to learn from others.

ASKER

I'll second that