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

[MadMatt]

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

 

[acw]

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]

Why not try GetSaveAsFileName?

 

[MadMatt]

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]

Matt,
Does something like this work for you?

Sub SaveAsDemo()
ActiveSheet.Copy
fName = "c:\temp\" & InputBox(" What name") & ".csv"
ActiveWorkbook.SaveAs fName
RC = Shell("Notepad " & Filename, 1)
End Sub

 

[MadMatt]

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]

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