Save as csv - every hour

Question

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 ...

Possible duplicate of [How to use workbook.saveas with automatic Overwrite](https://stackoverflow.com/questions/14634453/how-to-use-workbook-saveas-with-automatic-overwrite) — BruceWayne, Oct 15 '18 at 19:42
How are you handling the situation where a cell is being edited (or any of the numerous other situations that will prevent Excel from saving) when this code is called? — Frank Ball, Oct 15 '18 at 19:54
Hi, The modification in the file are all automatic using scripts, I see the export of the files are working now, just the hourly repeat is not working proper... — Mitch, Oct 15 '18 at 19:57

score 0 · Answer 1 · answered Oct 15 '18 at 19:50

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

Mitch · Accepted Answer · 2018-10-16T08:13:18.787

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

Save as csv - every hour

2 Answers2