User prompt for CSV format
 

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

User prompt for CSV format
 

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

User prompt for CSV format
 

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

User prompt for CSV format
 

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

User prompt for CSV format
 

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

User prompt for CSV format
 

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