User prompt for CSV format

[MrRJ]

Hello,

I have a code that I would like to change so it can prompt the user to
select the directory and file name. However, it must be in CSV format and
the file will close.

ChDir "I:\GP Finance\WAP09"
ActiveWorkbook.SaveAs Filename:="I:\GP Finance\WAP09\PBGCAN Test.csv", _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWindow.Close

Thanks for your help.
MrRJ

 

[Chip Pearson]

Try code like

Dim FName As Variant
FName = Application.GetSaveAsFilename( _
vbNullString, "CSV Files (*.csv),*.csv")
If FName = False Then
Debug.Print "user cancelled"
Else
Debug.Print "Selected file: " & FName
End If


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[MrRJ]

Chip,
I tried it and it did not work. It only prompt to give it a file name. It
did not close it and save it.

MrRJ

 

[Kenneth Hobson]

Dim FName As Variant
FName = Application.GetSaveAsFilename( _
vbNullString, "CSV Files (*.csv),*.csv")
If FName = False Then
Debug.Print "user cancelled"
Else
ActiveWorkbook.SaveAs Filename:=FName, _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWindow.Close
End If

 

[Kenneth Hobson]

Dim FName As Variant
FName = Application.GetSaveAsFilename( _
vbNullString, "CSV Files (*.csv),*.csv")
If FName = False Then
Debug.Print "user cancelled"
Else
ActiveWorkbook.SaveAs Filename:=FName, _
FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close False
End If

 

[MrRJ]

Thanks Kenneth.
One thing, it prompts you "do you want to save the changes you made to ..."

Can I avoid this? They answer should always be yes.

MrRJ