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

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)


On Fri, 13 Feb 2009 06:53:01 -0800, MrRJ
wrote:

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,
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

"Chip Pearson" wrote:


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)


On Fri, 13 Feb 2009 06:53:01 -0800, MrRJ
wrote:

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

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

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

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

"Kenneth Hobson" wrote:


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