CODEWITHSUNDEEP
vbaexcel
Nidhi Sharma
Nidhi Sharma

Reputation: 135

Save Excel file in every 2 second without using macro

I want to save an excel at every 2 seconds. Data is updated in this excel through DDE and want to read this data every 2 seconds. Unfortunately this data is not saved on hard disk.

I am aware of macro which can be used to save file after specified point of time but do not want to use macro.

Since data is updated frequently in this sheet through DDE (at every 100 MS) so sheet change event triggers too often.

Below is the code which i am trying but not getting success.

Dim ctme, ptme As Integer

Private Sub Workbook_Open()
  ctme = Second(Now)
  ptme = ctme - 2

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
ctme = Second(Now)

If (ctme - ptme) = 2 Then
 Me.Save
 ptme = ctme

End If


End Sub

Please help

Upvotes: 0

Views: 3478

Answers (2)

Vikas
Vikas

Reputation: 805

Nidhi, people here are trying to help you and you need to understand that no one has the access to your brain to understand what you actually meant to ask. So it is quite natural to ask questions to understand the issue clearly before suggesting any answer. The people here, get equally frustrated when they are unable to understand a simple question, the time they spend could have been easily saved, had the person spent a little extra time in explaining the things better. So giving credit to those who are trying to help you, will not harm at all.

Ok, coming back to your question. I may be wrong, but I think that SheetChange event is not fired on DDE update. Please correct me if I am wrong.

The next option can be Application.OnTime functionality.

Write the following code in Workbook Open Method:

Private Sub Workbook_Open()
    Dim currentTime As Date
    currentTime = DateAdd("s", 2, Now)
    Application.OnTime currentTime, "SaveFile"
End Sub

Then Add a new Module and add the following Function there in new Module:

Public Sub SaveFile()
    ThisWorkbook.Save

    Dim currentTime As Date
    currentTime = DateAdd("s", 2, Now)
    Application.OnTime currentTime, "SaveFile"
End Sub

The above code will create a timer which would run every two seconds to save your file. There are pros and cons for this approach, but it's Excel's best possible Timer functionality. Let me know if you have any questions.

Thanks, Vikas

Upvotes: 2

whytheq
whytheq

Reputation: 35605

(this is totally away from the OP tags but just thought I'd put forward a possible alternative)

Create a small .NET console application.

User one of the Timer objects available to create this timed loop you require.

Then using a reference to Excel Interop library on each sweep of the loop it looks like you might need to open this workbook, save it, and then close it again .....depending on the calculations within the book and the size of the Excel file is it physically possible on your machine to open/calculate/save within 2 seconds?

Upvotes: 1

Related Questions