excel vba macro save as csv - every hour

I'm working on a simple vba code. I've a xlsm workbook with some sheets ...

I would like to export all sheets as csv, every hour.

This is what I have allready (but it's not saving...)

Sub ExportSheetsToCSV()
Application.DisplayAlerts = False
Dim xWs As Worksheet
Dim xcsvFile As String
For Each xWs In Application.ActiveWorkbook.Worksheets
    xWs.Copy
    xcsvFile = CurDir & "\" & xWs.Name & ".csv"
    Application.ActiveWorkbook.SaveAs Filename:=xcsvFile, _
    FileFormat:=xlCSV, CreateBackup:=False
    Application.ActiveWorkbook.Saved = True
    Application.ActiveWorkbook.Close
Next
Application.DisplayAlerts = True
Call RefreshDataEachHour
End Sub

In thisworkbook module:

Public Sub RefreshDataEachHour()

Application.OnTime Now + TimeValue("01:00:00"), "ExportSheetsToCSV"

End Sub

When I delete this line: Application.DisplayAlerts = False the export works perfectly, but not every hour & I have to check "yes" on the prompt message about the file format (loss of formulas)

I would love to get this done ...

2 answers

  • answered 2018-10-15 19:50 Tim Williams

    Maybe instead of CurDir try using ThisWorkbook.Path. If you want to this to run every hour and you're also working on the same PC then CurDir can/will change depending on what else you're doing.

    Sub ExportSheetsToCSV()
        Application.DisplayAlerts = False
        Dim xWs As Worksheet, wb As Workbook
        Dim xcsvFile As String
        For Each xWs In Application.ActiveWorkbook.Worksheets
            xWs.Copy
            Set wb = ActiveWorkbook
            xcsvFile = ThisWorkbook.Path & "\" & xWs.Name & ".csv"
            wb.SaveAs Filename:=xcsvFile, _
                FileFormat:=xlCSV, CreateBackup:=False
            wb.Close False 'don't save
        Next
        Application.DisplayAlerts = True
        Call RefreshDataEachHour
    End Sub
    

  • answered 2018-10-16 08:04 Mitch

    Okay,

    The code works perfectly... It just needed to be a module instead of a macro in ThisWorkbook.

    In case someone needs it: Just add a module and use this script:

    Public Sub RefreshDataEachHour()
    
        Application.OnTime Now + TimeValue("00:00:10"), "Book1.xlsm!ExportSheetsToCSV"
    
        End Sub
        Sub ExportSheetsToCSV()
        Application.DisplayAlerts = False
        Dim xWs As Worksheet
        Dim xcsvFile As String
        For Each xWs In Application.ActiveWorkbook.Worksheets
            xWs.Copy
            xcsvFile = CurDir & "\" & xWs.Name & ".csv"
            Application.ActiveWorkbook.SaveAs Filename:=xcsvFile, _
            FileFormat:=xlCSV, CreateBackup:=False
            Application.ActiveWorkbook.Saved = True
            Application.ActiveWorkbook.Close
        Next
        Application.DisplayAlerts = True
        Call RefreshDataEachHour
        End Sub