Save file in new folder, without changing file name

[BrianLearning]

I want to create a macro to save the file to the "July" folder in the Z drive, in a CSV format. Without changing the file name to something different.

I have the macro:

Sub Save_To_Z()
'
' Save_To_Z Macro
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
ChDir "Z:\July"
ActiveWorkbook.SaveAs FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close
End Sub
Sub Macro2()
'
' Macro2 Macro
'
'
End Sub

Here's the problem. It doesn't save the file to the Z drive and the July folder. It just saves the files in the folder that the file is currently in.

I have found sites suggesting I should do the following:

Sub Save_To_Z()
'
' Save_To_Z Macro
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
ChDir "Z:\July"
ActiveWorkbook.SaveAs Filename:= _
"Z:\July\Ledger_Template_final.csv", _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close
End Sub
Sub Macro2()
'
' Macro2 Macro
'
'
End Sub

This works, in that it saves the file in CSV format, to the July folder in the Z drive, with the filename "Ledger_Template_final." The problem is, I don't want the macro to specify the filename. The filename should be whatever the existing name of the file is. The reason is, the macro will be used on many different files that will be saved in the July folder. We can't save them all with the same file name.

Can anyone help me please?

 

[mrshl9898]

Try

Sub Save_To_Z()
'
' Save_To_Z Macro
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
Location = "Z:\July\"
ActiveWorkbook.SaveAs Filename:= _
Location & ActiveWorkbook.Name , _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close
End Sub

 

[BrianLearning]

That gets me most of the way there, but not completely. The code that you provided saves the document to the correct folder and with the filename unchanged (YES! THANK YOU!), but it doesn't save it as a .CSV file format. The saved document is still in .XLSM format.

I don't understand why, because our VBA code does contain:

FileFormat:=xlCSV

 

[mrshl9898]

Sorry, missed that.

Try:

Sub Save_To_Z()

Dim fso As New Scripting.FileSystemObject

Location = "Z:\July\"
ActiveWorkbook.SaveAs Filename:= _
Location & fso.GetBaseName(ActiveWorkbook.Name), _
FileFormat:=xlCSV
ActiveWorkbook.Close
End Sub

You'll need to add the "Microsoft Scrip[ting Runtime" object in Tools>References

 

[BrianLearning]

I added "Microsoft Scripting Runtime."

When I ran the macro, I got the message: "Run-time error ‘424’
Object required."

I clicked the "Debug" button. It highlighted the lines:

ActiveWorkbook.SaveAs Filename:= _
Location & fso.GetBaseName(ActiveWorkbook.Name), _
FileFormat:=xlCSV

And it had a yellow arrow pointing at the last line:

FileFormat:=xlCSV

Do you have any idea what I might be missing?