Bit Of A Poser (but not for the Excel geniuses!)

[ExcelBuffoon]

Hi Guys,

Excel 2007 (Office 2007 Pro) VSL

I have a workbook (.xlsm)
In that workbook are several sheets. The last sheet is a simple list of information data which is automatically extracted from one or two of the other sheets (using simple =IF()) formulas. That last sheet needs to be saved as a .csv file to a specific location. This filename needs to be a variable though. I have recorded a simple macro which looks like the following:

Sub SaveCSV()
'
' SaveCSV Macro
'
Sheets("CSV").Select
Sheets("CSV").Copy
ChDir "C:\Documents and Settings\User\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\User\Desktop\12345678.csv", FileFormat:=xlCSV, _
CreateBackup:=False
End Sub

This macro works fine but will simply prompt to overwrte the file 12345678.csv, but what I need to be able to do is input the filename during the macro being run, then after the filename is manually typed in, either the macro closes at that point or it continues to run (but doesn't do anything else).

So the idea would be to have on the first sheet a button. When the button is pressed, it runs the macro which selects the last sheet, sets it to be saved as a .csv but prompts for the filename. When the filename is typed in, the sheet is saved then the macro stops.

An alternative would be that it saves it as the current workbook name (inherits the name). So the workbook is first saved as filename6.xlsm for example, then when the macro is run, it saves the last sheet as filname6.csv.

Thanks in advance to anyone who can help out.

 

[VoG]

Perhaps like this

Sub SaveCSV()
'
' SaveCSV Macro
'
Dim FName As Variant
Sheets("CSV").Copy
FName = Application.GetSaveAsFilename()
If FName = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlCSV
End Sub

 

[ExcelBuffoon]

Perhaps like this

Sub SaveCSV()
'
' SaveCSV Macro
'
Dim FName As Variant
Sheets("CSV").Copy
FName = Application.GetSaveAsFilename()
If FName = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlCSV
End Sub

The eternally grateful side of me wants to say brilliant Peter, thanks very much. The finnicky side of me wants to say, brilliant Peter, thanks very much but it would be great if it also defaulted with the .csv extension (as the would be user is a muppet!). When presented with the box to save, although it is saving as a csv format file, it needs the .csv adding as shown in screenshot.

 

[VoG]

Try

Sub SaveCSV()
'
' SaveCSV Macro
'
Dim FName As Variant
Sheets("CSV").Copy
FName = Application.GetSaveAsFilename(FileFilter:="CSV Files(*.csv),*.csv")
If FName = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlCSV
End Sub

 

[ExcelBuffoon]

Try

Sub SaveCSV()
'
' SaveCSV Macro
'
Dim FName As Variant
Sheets("CSV").Copy
FName = Application.GetSaveAsFilename(FileFilter:="CSV Files(*.csv),*.csv")
If FName = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlCSV
End Sub

brilliant Peter, thanks very much. Really appreciated.