VBA: Save as new file name on dedicated folder

[josephmary]

Hi,

I wanted to create a code where my file is being saved as CSV with a file name that also changes every month. I feel like my code is correct as it saves perfectly. However, I encountering an error when i open that saved file (See screenshot)
Here's the VBA code:

SUB save
ChDir _
"C:\Users\" & Environ("username") & "\Documents\Reports\QA Report\GCP"
Dim File_Name As String
File_Name = Format(Now(), "yyyy-mm") & " QAAttributes" & ".csv"
ActiveWorkbook.SaveAs Filename:=File_Name
END

 

[rpaulson]

try this,

Sub save()

Dim File_Name As String

myPath = "C:\Users\" & Environ("username") & "\Documents\Reports\QA Report\GCP\"
    If Dir(myPath, vbDirectory) = "" Then Shell ("cmd /c mkdir """ & myPath & """")
ChDir myPath
File_Name = Format(Now(), "yyyy-mm") & " QAAttributes" & ".csv"
ActiveWorkbook.SaveAs Filename:=File_Name
End Sub

 

[josephmary]

Is there another option without running cmd? we're not allowed to open cmd as part of our work policy

 

[Skyybot]

Add the FileFormat property to your SaveAs method

SUB save()
ChDir _
"C:\Users\" & Environ("username") & "\Documents\Reports\QA Report\GCP"
Dim File_Name As String
File_Name = Format(Now(), "yyyy-mm") & " QAAttributes" & ".csv"
ActiveWorkbook.SaveAs Filename:=File_Name, FileFormat:=xlCSV
END sub

 

[josephmary]

this works great! thank you

 

[josephmary]

Good day Skyybot or anyone. This is almost similar to my question above. but this time, i want my file to be saved on a different folder every month. (eg. I want my november file to be saved on the november folder).

 

[josephmary]

nvm. i think i got it

 

[Skyybot]

Good day Skyybot or anyone. This is almost similar to my question above. but this time, i want my file to be saved on a different folder every month. (eg. I want my november file to be saved on the november folder).

Try this

Sub SaveToFolder()
Dim File_Name As String
Dim Folder_Name As String
ChDir "C:\Users\" & Environ("username") & "\Documents\Reports\QA Report\GCP"
Folder_Name = Format(Now(), "yyyy-mm")
MkDir CurDir & "\" & Folder_Name
ChDir CurDir & "\" & Folder_Name
File_Name = Format(Now(), "yyyy-mm") & " QAAttributes" & ".csv"
ActiveWorkbook.SaveAs Filename:=File_Name, FileFormat:=xlCSV
End Sub