User prompt for CSV format


M

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
 
Ad

Advertisements

C

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)
 
M

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
 
K

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
 
K

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
 
Ad

Advertisements

M

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top