Automating Excel to save file as CSV and run live updates

Question

For starters - I am raw and have limited skills, thus the guidance here is important to me. We have to supply data to a 3rd party in CSV format. The data must be updated at frequent intervals with a timestamp. Some workbooks at 1 minute intervals and other at 30/60 minute intervals. To ensure that data is permanently available I have done a little batch file to start the excel file. Our normal method of collecting data is via excel. I am able to get the data to update at at frequent intervals (10 seconds) courtesy Mr Google with a macro. The Macro also starts automatically on open event.

Private Sub Workbook_Open()
  Range("A1:A1").Calculate
 Application.OnTime DateAdd("s", 10, Now), "Calculate_Range"
End Sub

I have however run into issues when trying to save this to CSV file - the guidelines found thus far does not help me to have this process run unattended. I have managed to get past the overwrite query, but when I tried the format acknowledgement it fails.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.Run "VBATest.xlsm!Calculate_Range"
    ChDir "E:\PI"
                ActiveWorkbook.SaveAs Filename:="E:\PI\VBATest01.xlcsv", FileFormat:=xlCSV, _
                CreateBackup:=False
    Application.Run "VBATest.xlsm!Calculate_Range"
End Sub

What I need to accomplish is to:

1. Run the excel file automatically - the batch file can be added to scheduler to restart this.
2. Update the values at preset intervals - the macro I have now, can run this.
3. Save sheet as CSV file automatically without intervention required (overwrite and format compatibility).
4. Restart the excel file - batch file in scheduler if there is now other better method.

Thank you for your feedback!