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

Answers


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

Need Your Help

android.support.design.widget.TextInputLayout failed to instantiate

android-layout material-design

I am getting error while try to use TextInputLayout in android