How to detect that an Excel cell has been changed by formula and write data to a CSV

[Cowden]

I have an Excel file that is updated every few seconds by an application. Using the data delivered by the application, several cells in the worksheet (called "TSdata") are calculated using various formulae. If the value of a specific cell (B41) changes, the macro should write the contents of the worksheet to a CSV file. After viewing several related posts in the forum, I created a version based on Worksheet_Change that worked perfectly if the content of the cell was manually updated. I then created a version using Worksheet_Calculate that I expected to work the same way when the cell value was changed by the formula. This is the code I used:

 Private Sub Worksheet_Calculate()
    If Worksheets(“TSdata”).Range(“B41”).Value<>prevval Then
    Call ExportWorksheetAndSaveAsCSV
    End If
    prevval = Worksheets(“TSdata”).Range(“B41”).Value
    End Sub


    Public Sub ExportWorksheetAndSaveAsCSV()
    Dim wbkExport As Workbook
    Dim shtToExport As Worksheet
    Set shtToExport = ThisWorkbook.Worksheets("TSdata")     'Sheet to export as CSV
    Set wbkExport = Application.Workbooks.Add
    shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
    Application.DisplayAlerts = False                       'Possibly overwrite without asking
    wbkExport.SaveAs Filename:="C:\TSCSV\TSCSV1.csv", FileFormat:=xlCSV
     Application.DisplayAlerts = True
    wbkExport.Close SaveChanges:=False
    FileCopy "C:\TSCSV\TSCSV1.csv", "C:\ChartInfo\Data\TSCSV2.csv"
    End Sub

I know from the earlier test using a manual update that the Public Sub works OK (It's copied from another query regarding writing CSV files) but when I launch the macro, it seems to attempt multiple updates (the screen blinks rapidly several times), it generates multiple VB Workbooks, and then crashes Excel. So, either something in the Private Sub is incorrect or the Public Sub works differently when used with Worksheet_Calculate instead of Worksheet_Change. I've based the Private Sub on other responses to similar questions, so I'm at a loss to figure out what's wrong/missing. Note: the FileCopy at the end of the Public Sub is so that another program can work on the CSV without disrupting the Excel updates.
Using VB 7.0 and Excel 2010 on Windows 7.
Thanks in advance for any help

[patel45]

you posted in vb.net section, you have to ask for moving to Office Development section

[si_the_geek]

Welcome to VBForums

Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.

you posted in vb.net section, you have to ask for moving to Office Development section

It is better if an existing member asks for it, by clicking on the "Report Post" link at the bottom of a post (the triangle), as new members don't know their way around yet.

In this case it was lucky that I looked at this thread, but I (or another moderator) would have moved it hours earlier if someone had let us know.

[westconn1]

when I launch the macro, it seems to attempt multiple updates (the screen blinks rapidly several times), it generates multiple VB Workbooks, and then crashes Excel

try putting application.enableevents = false at the top of the sub, make sure to enable again at the end