CODEWITHSUNDEEP
vbaexcel
Mohsin Khan
Mohsin Khan

Reputation: 31

How to save worksheet in a specific folder with a specific name using vba?

Please can someone help me with changing the below code so the user is guided to save it in a specific folder with his username and current date please

When command button2 is pressed, 'save as' box should open in the following path: "C/test/" and the suggested name of worksheet should be in 'username ddmmyy' format.

I know that I should be using 'application.username' somewhere in the code but not sure how the complete code will look like.

What I could prepare so far is as follows (apologies if this is incorrect):

Private Sub CommandButton2_Click()
Dim IntialName As String
Dim sFileSaveName As Variant
IntialName = "Sample Output"
sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, fileFilter:="Excel Files (*.xlsm), *.xlsm")
If sFileSaveName <> False Then
ActiveWorkbook.SaveAs sFileSaveName
End If
End Sub

Upvotes: 0

Views: 1544

Answers (1)

cyboashu
cyboashu

Reputation: 10433

  1. Username : Environ("UserName")

  2. Date : Format(Date, "ddmmyy")

    Dim InitialName As String
Dim sFileSaveName As Variant

InitialName = "C:\temp\" & Environ("UserName") & Space(1) & Format(Date, "ddmmyy")

sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, fileFilter:="Excel Files (*.xlsm), *.xlsm")
If sFileSaveName <> False Then
    ActiveWorkbook.SaveAs sFileSaveName
End If

Upvotes: 2

Related Questions