Addition to existing macro - to save sheet as CSV

[Kickingkangaroos]

Hi All,

I have written a very simple macro, to perform a couple of simple actions to tidy up a sheet, which I have to run each day:

Sub SamplesUpload()
'
' SamplesUpload Macro
'
'
    Range("A:A,G:G").Select
    Range("G1").Activate
    Selection.Delete Shift:=xlToLeft
    Rows("1:8").Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    Application.Run "PERSONAL.xlsm!CSV"
End Sub

The "PERSONAL.xlsm!CSV" is another small macro which wraps quote marks ("") around the content of each populated cell in Column B.

What I am trying to do - is add a small amount of code to this above macro, which automatically saves the worksheet as a CSV file, with the file name as the current date; ideally in the standard format dd-mm-yyyy - but I'm not fussy if that is particularly complex. (EDIT: Please can you confirm how I would save this to C:\Samples Upload\ many thanks.

I understand that it is not particularly long-winded to do Save As --> CSV and type in the date - but this has to be done every day, so if it can be avoided by a simple amount of code, it would be great.

Any help would be greatly appreciated - please let me know if you need any further information.

Many thanks,

Andy

 

[Kickingkangaroos]

Hi again All,

I'm posting myself a reply (sad I know!) - as I managed to cobble together bits of code from various people (and add a tiny bit myself), to sort this out.

So first off - thank you very much to all the people who have posted on here, whose work I have shamelessly "borrowed" to piece together in order to get what I need - the code I ended up using is as follows:

Sub SaveAsCSV()
Dim DTAddress As String
Dim FileName As String
Dim FullyQualifiedFileName As String
'Get the path to the User's Desktop
DTAddress = "C:\Samples Upload\"
'Build the Workbook name to save as CSV format File
FileName = ActiveWorkbook.Name
FileName = Left(FileName, InStr(FileName, ".")) & "csv"
'Build the fully-qualified Workbook "save" path
FullyQualifiedFileName = DTAddress & FileName
'Switch Off all Application alerts before saving (in case the file already exists etc.)
Application.DisplayAlerts = False
'Save the current active sheet as a CSV file to the user's Desktop
ActiveWorkbook.SaveAs FileName:=FullyQualifiedFileName, FileFormat:=xlCSV

'Close the original Workbook without saving changes
ThisWorkbook.Close savechanges:=False
'Switch all Application alerts back on before exiting
Application.DisplayAlerts = True
End Sub

As the workbook (and worksheet) name was always going to be the current date - I was able to reference that to get the CSV file name.

Cheers,

Andy