Save As Filename (Macro)

Posted by Will on March 04, 2002 10:18 AM

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:\findatup\EXCEL.CSV"
ActiveWorkbook.SaveAs FileName:="C:\findatup\test_change.csv", FileFormat:= _
xlCSV, CreateBackup:=False
ActiveWindow.Close
End Sub

Posted by Tom on March 04, 2002 6:34 PM

Private Sub Wills_Macro()
Dim File_To_Open_Name As String
Dim File_To_SaveAs_Name As String
'For Clarity Assigns Name of file to open
'to File_To_Open_Name string variable
File_To_Open_Name = "C:\findatup\EXCEL.CSV"
'opens workbook
Workbooks.Open Filename:=File_To_Open_Name
'Application.GetSaveAsFilename open a typical windows
'saveAs dialog and gets a string from the user's choice
'including the entire path, but does not actually save the file
File_To_SaveAs_Name = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.cvs), *.cvs")
'turns off the "do you want to save changes" dialog
'you obviously want the user to save the new file. Right?
'If not, delete the next and last line
Application.DisplayAlerts = False
'from your original code with File_To_SaveAs_Name string
'collected from user with Application.GetSaveAsFilename
ActiveWorkbook.SaveAs Filename:=File_To_SaveAs_Name, FileFormat:= _
xlCSV, CreateBackup:=False
'your code
ActiveWindow.Close
Application.DisplayAlerts = True
'for more help do a help search for "GetSaveAsFilename" in VB
End Sub

Posted by Will on March 05, 2002 5:50 AM

Thank you. It seems to be working.

Posted by Will on March 05, 2002 5:52 AM

Thank you. It seems to be working