CODEWITHSUNDEEP
excelvba
Arun Yadav
Arun Yadav

Reputation: 3

Forcefully save workbook on open with workbook name and current date on desktop

I need a macro to save the excel sheet whenever opened with workbook name and current date, on desktop. Also if the same name is there on desktop i.e. workbook name and current date macro should not run (to avoid overwrite).

Please help to edit my below code:

Sub SaveInFormat()
    Application.DisplayAlerts = False
    Workbooks.Application.ActiveWorkbook.SaveAs Filename:="I:\CAP_Profile\Desktop\" & Format(Date, "yyyymm") & "DB" & ".xls", leFormat:=51
    Application.DisplayAlerts = True
End Sub

Upvotes: 0

Views: 87

Answers (1)

Alex D
Alex D

Reputation: 696

Well to save it when the workbook is opened use the Workbook Open sub:

Private Sub Workbook_Open()

End Sub

And then for getting the desktop path, use the special folders.

Dim path As String
path = CreateObject("WScript.Shell").specialfolders("Desktop")

To check if the file exists on the desktop use something like this (DirFile being a string to the exact path of the file):

If Len(Dir(DirFile)) = 0 Then
    ''Go ahead and save it
Else
    End
End If

Upvotes: 0

Related Questions