Modify code to prompt user for name to save file as.

MadMatt

New Member
Joined
Sep 27, 2006
Messages
12
Hi All, I'm trying to workout how to modify my code to ask the user the file name to save the file as. As you can see at the minute is saves it as test.csv. Thanks Matt.



Private Sub CommandButton1_Click()
Dim filename As String, RC As Long
filename = "c:\temp\test.csv"
ActiveWorkbook.SaveAs filename:=filename, _
FileFormat:=xlCSV, CreateBackup:=False
RC = Shell("Notepad " & filename, 1)
End Sub
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Matt

Try something like

filename = "c:\temp\" & InputBox(" What name") & ".csv"

This does assume that you will always save to the same directory.

HTH

Tony
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
Why not try GetSaveAsFileName?
 

MadMatt

New Member
Joined
Sep 27, 2006
Messages
12
Not sure which is the best way to do this. What Tony suggest did work with the save as file name. But the code changes the worksheet name as well.As you can see I'm not to good at coding yet and don't know all excels function(But I'm Learning). This is what I'm wanting the code to do.
to get all the infomation on the active worksheet and prompt the user for a file name the save the information as a seperate CSV file. I been looking around the net and have not found a good example of this or some code I can modify. Any help would be great!
Thanks, Matt
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

Matt,
Does something like this work for you?
Code:
Sub SaveAsDemo()
ActiveSheet.Copy
fName = "c:\temp\" & InputBox(" What name") & ".csv"
ActiveWorkbook.SaveAs fName
RC = Shell("Notepad " & Filename, 1)
End Sub
 

MadMatt

New Member
Joined
Sep 27, 2006
Messages
12
Hi it seems to work but when you view the file in notepad it looks like this.
I need it to have comma seperated values.
Thanks,Matt
ÐÏࡱá >  þÿ       þÿÿÿ ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿýÿÿÿ *     

            þÿÿÿ!     " / # $ % & ' ( ) + þÿÿÿ, - . 0 2 1 þÿÿÿþÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿR o o t E n t r y   ÿÿÿÿÿÿÿÿ  À F P!`ƒãåÆ * W o r k b o o k    ÿÿÿÿ  ó+ _ V B A _ P R O J E C T _ C U R "  ÿÿÿÿÿÿÿÿ Ð ž{ãåÆàÚÿ{ãåÆ C t l s
ÿÿÿÿ ÿÿÿÿ P  þÿÿÿ     

    þÿÿÿ           
 

MadMatt

New Member
Joined
Sep 27, 2006
Messages
12
This code seems to work ok but I don't want it to change the worksheet name. Thanks,Matt

Dim filename As String, RC As Long
filename = "c:\temp\" & InputBox(" What name") & ".csv"
ActiveWorkbook.SaveAs filename:=filename, _ >>>>>>>HERE
FileFormat:=xlCSV, CreateBackup:=False
RC = Shell("Notepad " & filename, 1)
End Sub
 

Forum statistics

Threads
1,141,022
Messages
5,703,788
Members
421,316
Latest member
Cyril Beki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top