Automatically save certain excel sheets (in an xlsm file) as .csv every 15 minutes
I am quite new to VBA. I have a spreadsheet which contains 20 worksheets. There is a macro which runs every 15 minutes which displays data on worksheets 5, 10, 15 and 20. I want to be able to automatically save the data in these worksheets in .csv format every 15 minutes.
The spreadsheet is called 'Events.xlsm'; the worksheets I want to save are Event Total, Event Total(2), Event Total(3) and Event Total(4). I have had an attempt to save one of the worksheets based on some other examples on this site. Not sure if I am on the right track.
Sub SaveWorksheetsAsCsv() Dim WS As Excel.Worksheet Dim SaveToDirectory As String Dim CurrentWorkbook As String Dim CurrentFormat As Long CurrentWorkbook = ThisWorkbook.Events CurrentFormat = ThisWorkbook.xlsm ' Store current details for the workbook SaveToDirectory = "S:\test\" For Each WS In Day Event.Worksheets Sheets(WS.Event Total).Copy ActiveWorkbook.SaveAs Filename:=SaveToDirectory & ThisWorkbook.Events & "-" & WS.Event Total & ".csv", FileFormat:=xlCSV ActiveWorkbook.Close savechanges:=False ThisWorkbook.Activate Next Application.DisplayAlerts = False ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat Application.DisplayAlerts = True ' Temporarily turn alerts off to prevent the user being prompted ' about overwriting the original file. End Sub
You sort of got there. There's a bunch of stuff to clean up. Rather than write a long tutorial, I just refactored the code and commented some of it for you.
There is a caveat here: CSV files are a very specific and unique flat format. This means, there can be only 1 sheet per file, no special formatting or anything like that. If there is, you need to clean it up before saving the csv.
Sub SaveWorksheetsAsCsv() Dim WS As Worksheet Dim SaveToDirectory As String ' Store current details for the workbook SaveToDirectory = "S:\test\" For Each WS In ThisWorkbook.Worksheets If Left(WS.Name, 10) = "Event Total" Then 'evaluate if its a sheet that needs to be saved Dim wbCopy As Workbook WS.Copy Set wbCopy = ActiveWorkbook 'Application.DisplayAlerts = False 'turn this on once you are sure it will save the CSV correctly, even with a warning prompt With wbCopy .SaveAs Filename:=SaveToDirectory & ThisWorkbook.Name & "-" & WS.Name & ".csv", FileFormat:=xlCSV .Close savechanges:=False End With 'Application.DisplayAlerts = True End If Next ThisWorkbook.Save 'a simple save will do here. no need to overwrite the workbook unless you want to End Sub