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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

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
75,915
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,453

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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,984
Messages
5,545,345
Members
410,678
Latest member
glasi
Top