Save live data to CSV

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.
[Code start]
Private Sub Workbook_Open()
Range("A1:A1").Calculate
Application.OnTime DateAdd("s", 10, Now), "Calculate_Range"

End Sub
[Code End]
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.
[Code Start]
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
[Code End]
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 and repeat process - batch file in scheduler if there is no other better method.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however, you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)