Allowing User to save file

[Guest]

I have an application that we copy information into and then need to save it
as a CSV file. I have written the simple code below that does exactly what I
need it to. The user does not need to have the current file or the saved
file open when completed. I have a button they click and the file saves and
closes.

Can anyone tell me how to change to code to allow the user to pick the name
and path of where they would like it to be saved? The way I have it setup if
the path does not exist I get an error.

Also, if the file they were coping from was open and always the same name is
there a way I could get this file to copy the information and paste it. I
would also need to have some type of if error resume so if they did not have
the file open.

Any help will be appreciated.

Private Sub United_Click()
Sheets("Census Data").Select
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Documents and
Settings\Cindy\Desktop\Census Export Files\United.csv", FileFormat _
:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close
End Sub

 

[Kletcho]

Dim sFilename as string

sFilename = Application.GetOpenFilename()
If sFilename = False Then Exit Sub

Sheets("Census Data").Select
Application.DisplayAlerts = False
With ActiveWorkbook
.SaveAs Filename:=sFileName, FileFormat:=xlCSV, CreateBackup:=False
.Close
End with

 

[Kletcho]

This is probably more accurate (and easier!) than the last one I
posted:

Sheets("Census Data").Select
Application.DisplayAlerts = False
Application.Dialogs(xlDialogSaveAs).Show
ActiveWorkbook.Close

 

[Guest]

Kletcho:

Thank you so much for your reply. I got the first one and couldnt get it to
work but the second one works like a charm.

Is there anything I can add to the code to make it default to saving it as a
CSV file.

Thanks again for all your help.

 

[Kletcho]

No problemo. Sorry I mislead you at first. You can change the file
type by changing this line to:

Application.Dialogs(xlDialogSaveAs).Show "", 6

The arguments for this command as found in VBA help are:

xlDialogSaveAs - document_text, type_num, prot_pwd, backup,
write_res_pwd, read_only_rec

So in this case the document name is blank and the type_num is 6 which
means a CSV file.

 

[Guest]

Thank you so much. I actually found information in the help files but just
didnt understand it. This has been very helpful. Thanks again.