Save Excel Workbook from code

[MarkMcD]

I have an Excel Workbook that users enter data into and then press a button and the data gets processed into another application.

The processing occurs in code (one call to the other application per row) and I'd like to save the worksheet after each 100 rows have been processed. The update process often hangs and my only option is to terminate the Excel session (CTRL+ALT+DEL) without having saved where the processing was up to.

Any help/suggestions would be appreciated.

 

[JGillespie]

Hi,

If you simply want to save the workbook with it's current name then the code is:

ActiveWorkbook.Save

However what it sounds like you need to do is keep track of where your processing is up to so you can restart from that point if the system hangs.

Sub TestLoop()

SaveLimit = 5 'save after how many rows

'loop through each row in named rang 'MyRange'
For Each MyRow In ActiveSheet.Range("MyRange&quot.Rows

'external process call here

'set value of counter cell. named cell on worksheet
ActiveSheet.Range("Counter&quot.Value = MyRow.Row

If MyRow.Row = SaveLimit Then
SaveLimit = SaveLimit + SaveLimit
ActiveWorkbook.Save
Else
End If


Next


End Sub Jamie Gillespie
j-gillespie@s-cheshire.ac.uk