Save As Macro

[Guest]

I am in the process of recording a macro and would like at the very end for the Save As dialogue box to pop up and allow the user to enter a file name. I pasted the portion of the macro I would like to change below. If anyone knows how I could get rid of the current path and allow the user to create their own every time they the macro is run I would be greatly appreciative.

Thanks
Will

'
Workbooks.Open FileName:="C:findatupEXCEL.CSV"
ActiveWorkbook.SaveAs FileName:="C:findatuptest_change.csv", FileFormat:= _
xlCSV, CreateBackup:=False
ActiveWindow.Close
End Sub

 

[Barrie Davidson]

Here you go:

Dim SaveFileName As String
Workbooks.Open FileName:="C:findatupEXCEL.CSV"
SaveFileName = Application.GetSaveAsFilename
ActiveWorkbook.SaveAs FileName:=SaveFileName, FileFormat:= _
xlCSV, CreateBackup:=False
ActiveWindow.Close

Regards,

 

[Dave Hawley]

Barries code will certainly do the trick, all I would add is a line in case they Cancel.

Dim SaveFileName As String
Workbooks.Open Filename:="C:\findatup\EXCEL.CSV"
SaveFileName = Application.GetSaveAsFilename
If SaveFileName = "False" Then Exit Sub 'Cancelled
ActiveWorkbook.SaveAs Filename:=SaveFileName, FileFormat:= _
xlCSV, CreateBackup:=False
ActiveWindow.Close

Or you could use:

Workbooks.Open Filename:="C:\findatup\EXCEL.CSV"
Application.Dialogs(xlDialogSaveAs).Show ("Mine")

The "Mine" is a default name to place in the Save as name box.

 

[Barrie Davidson]

Nice improvement Dave