AutoSave Excel in Different format

[humboldtstate]

Hi,
This is my first post to this forum, although I am working on a potential project for my University and was curious if what I need to do is going to be possible.

1] Have Excel document save every second.
* Found some people discussing this with code so I know that this is possible.

2) Have Excel document save every second, but save in a CSV format?
* Havent found this one yet :/

Im assuming this would be done with the programming. Im familiar with code, just not the Macro setup(in process of learning). If someone knows if this is possible, and could possible share some code it would be greatly appreciated! Thanks for your time.....
Sincerely,
J.W.T.
Humboldt State University

 

[patel45]

Sub save_sheet_to_new_workbook()
ThisWorkbook.Worksheets(3).Copy
fileSaveName = Application.GetSaveAsFilename(fileFilter:="Excel files (*.xls), *.xls")
If fileSaveName <> False Then
  ActiveWorkbook.SaveAs fileSaveName
End If
ActiveWorkbook.Close True
'Call CloseExcel
End Sub

Sub savetocsv()
Filename = "pippo"
ActiveWorkbook.SaveAs "D:\DATI\Prova\" & Filename & ".csv", 6
'ActiveWorkbook.Close True
End Sub

 

[HaHoBe]

Hi, J.W.T.,

first of all I wouldn´t recommend saving a workbook every second as that would leave nearly no time for input. You should have a look at Application.OnTime in the VBA Help as that would trigger the macro on openng the workbook (or by pressing a button) and be cancelled either by another button/menu point or closing the workbook.

In this code you need to trigger the event by starting the macro either manually from the VBE, the Macro window (ALT+F8) or calling it from ThisWorkbook and the event Workbook_Open. Please note that the workbook needs to be saved and that the macro will write into Range B2 on the active sheet:

Sub SavePerMinute()
   Dim NextTime As Date
   Dim sPathB As String, sPathW As String, sPath As String
   Application.DisplayAlerts = False
   sPath = ThisWorkbook.Path
   sPath = sPath & "\"
   sPathW = sPath & Format(Now, "yymmddhhmmnn") & ".xls"
   sPathB = sPath & Format(Now, "yymmddhhmmnn") & ".bak"
   ActiveWorkbook.SaveAs sPathB
   ActiveWorkbook.SaveAs sPathW
   Range("B2").Value = Now + TimeValue("00:01:00")  'TimeValue("00:00:01") for a second
   Application.OnTime CDate(Range("B2").Value), "SavePerMinute"
   Application.DisplayAlerts = True
End Sub

Sub StopSave()
   If IsEmpty(Range("B2")) Then Exit Sub
   Application.OnTime _
      earliesttime:=CDate(Range("B2").Value), _
      procedure:="SavePerMinute", _
      schedule:=False
   Range("B2").ClearContents
End Sub

Ciao,
Holger