VBA Save Copy as csv complete with name and datestamp, and some additional format

[Flitbee]

Hi
i am new to VBA, and would like some help getting my table to save as a CSV, but with some additional columns added.

This table is a report that is generated out of 8 different sales forecasts:

the final CSV has to be in this specific format for importing:

the CSV needs to save to a specific location, with name and date stamp.

I would like to be able to refresh the table and run the Macro to save the report automatically in the correct format.

hopefully this is do-able?

thank you
Flitbee

 

[Flitbee]

Does anyone have any ideas for this one?

 

[John_w]

The basic code can be generated with the macro recorder (select and copy the whole table, paste into a new workbook, insert the extra columns and headers and save as CSV) and then edited to merge the Select and Selection statements, determine the last row in the table (needed to fill column A with 'Stock item') and specify the file name and date stamp.

You'll need to change the SaveAs folder path and file name in this code.

Public Sub Save_Table_As_CSV()
    
    Dim n As Long
    
    n = ActiveSheet.ListObjects(1).Range.Rows.Count
    ActiveSheet.ListObjects(1).Range.Copy
    Workbooks.Add
    ActiveSheet.Paste
    
    Columns("B:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B1:C1").Value = Array("KEY 2", "KEY 3")
    Columns("A:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1:C1").Value = Array("SEQUENCE 1", "SEQUENCE 2", "SEQUENCE 3")
    Range("A2:A" & n).Value = "Stock item"
    
    Application.DisplayAlerts = False 'suppress warning if csv file already exists
    ActiveWorkbook.SaveAs Filename:="C:\path\to\folder\File name " & Format("YYYYMMDD") & ".csv", FileFormat:=xlCSV, CreateBackup:=False
    Application.DisplayAlerts = True
    ActiveWorkbook.Close SaveChanges:=False
    
End Sub

 

[Flitbee]

Thank you John_W.

This is great. The macro doesn't give the opportunity to add the date. is there a specific code for this?

 

[John_w]

Sorry, the SaveAs line, to use the current date should be:

    ActiveWorkbook.SaveAs Filename:="C:\path\to\folder\File name " & Format(Date, "YYYYMMDD") & ".csv", FileFormat:=xlCSV, CreateBackup:=False

 

[Flitbee]

Thank you John_w.
truely appreciated.
one last quetsion.
is it possible to include a time stamp also?

 

[John_w]

is it possible to include a time stamp also?

    ActiveWorkbook.SaveAs Filename:="C:\path\to\folder\File name " & Format(Now, "YYYYMMDD HHNNSS") & ".csv", FileFormat:=xlCSV, CreateBackup:=False

Look at the Format function's Format argument for the possible date and time symbols:

Format function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

 

[Flitbee]

Thank you John_W
much appreciated.