Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data logging with a DDE link and VBA
I have a created a spreadsheet that uses a DDE link to show values from plant
equipment. The information is displayed on four cells in sheet1. I have put together a macro that copys the information from the four cells in sheet1 and pastes the information on sheet 2. This routine runs every second and moves to the next cell. . It saves the information every seven hours and starts over. In other words I know what the equipment was doing every second during a 7 hour shift. The program seems to work fine when I test it in the office, without the link and without letting it run 7 hours. When I leave it running on the machine the program crashes, I have not seen the error that comes up. Will a DDE link cause any type of error that can stop the program from running? Is there a way to program around DDE errors? This is the only program running on this computer can windows cause it to crash in any way? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data logging with a DDE link and VBA
A DDE Link should not cause any problems in an Excel spreadsheet as
long as the links are implemented correctly. It is more likely that the problem is in whatever code is running every second. You may want to throw in an error handler to record all the errors so that you can track down what is going wrong. A better way to do things might be to use the Excel SetLinkOnData method to monitor the DDE data and trigger the macro that you are currently triggering with a timer. The SetLinkOnData method lets you configure a VBA subroutine to run automatically whenever data from a DDE link changes. Using this approach, you would never miss a single data value. With a timer taking a snapshot of the data every second, you could easily miss data as well as record redundant data. On Wed, 18 Jul 2007 11:58:00 -0700, Elceller in distress wrote: In other words I know what the equipment was doing every second during a 7 hour shift. The program seems to work fine when I test it in the office, without the link and without letting it run 7 hours. When I leave it running on the machine the program crashes, I have not seen the error that comes up. Will a DDE link cause any type of error that can stop the program from running? Is there a way to program around DDE errors? This is the only program running on this computer can windows cause it to crash in any way? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data logging with a DDE link and VBA
Thanks for your help Thomas,
I am new to error handles, In every sub I am guessing I need to add: "On Error GoTo ErrorHandler"? Then also add a sub called "sub Errorhandler()"? What code would I use to store the error? Sub errorhandler() ??? end sub For reference purposes, I have attached a portion of my program. I just took a piece of it so it will not work. sub controller() If time = TimeSerial(19, 0, 0) Then ExportandSave insertsheet FormatSheet ResetRange Setnewtime setoldtime test Else If OldTime < NewTime Then ZeroScan = False setoldtime Rng1 = Rng1 + 1 Rng2 = Rng2 + 1 getrange Sheets("Sheet1").Range("A2:g2").Copy Sheets(Sheetname).Range(InsertRange).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets(Sheetname).Range(DTETMEInsertRange).Offset( 0, 7).Value = Date Sheets(Sheetname).Range(DTETMEInsertRange).Offset( 0, 8).Value = time ElseIf NewTime = 0 Then OldTime = 0 If ZeroScan = False Then Rng1 = Rng1 + 1 Rng2 = Rng2 + 1 getrange Sheets("Sheet1").Range("A2:g2").Copy Sheets(Sheetname).Range(InsertRange).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets(Sheetname).Range(DTETMEInsertRange).Offset( 0, 7).Value = Date Sheets(Sheetname).Range(DTETMEInsertRange).Offset( 0, 8).Value = time ZeroScan = True Else Setnewtime End If Else ZeroScan = False Setnewtime End If End If Loop End Sub "Thomas Lutz" wrote: A DDE Link should not cause any problems in an Excel spreadsheet as long as the links are implemented correctly. It is more likely that the problem is in whatever code is running every second. You may want to throw in an error handler to record all the errors so that you can track down what is going wrong. A better way to do things might be to use the Excel SetLinkOnData method to monitor the DDE data and trigger the macro that you are currently triggering with a timer. The SetLinkOnData method lets you configure a VBA subroutine to run automatically whenever data from a DDE link changes. Using this approach, you would never miss a single data value. With a timer taking a snapshot of the data every second, you could easily miss data as well as record redundant data. On Wed, 18 Jul 2007 11:58:00 -0700, Elceller in distress wrote: In other words I know what the equipment was doing every second during a 7 hour shift. The program seems to work fine when I test it in the office, without the link and without letting it run 7 hours. When I leave it running on the machine the program crashes, I have not seen the error that comes up. Will a DDE link cause any type of error that can stop the program from running? Is there a way to program around DDE errors? This is the only program running on this computer can windows cause it to crash in any way? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data logging with a DDE link and VBA
Insert a DoEvents statement into your loop so you don't tie down the
operating system. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data logging with a DDE link and VBA
Thanks, what about error handling
" wrote: Insert a DoEvents statement into your loop so you don't tie down the operating system. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data logging with a DDE link and VBA
I think I figured out the problem, the laptop that I was using was set up to
cut off the hard drive after 45 minutes. It seems to work fine now. Thanks for your help. " wrote: Insert a DoEvents statement into your loop so you don't tie down the operating system. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data logging with excel vba | Excel Programming | |||
Logging data using the same textfile over and over again | Excel Programming | |||
DDE Logging Data Loss in Excel | Excel Discussion (Misc queries) | |||
Live DDE Data Logging | Excel Discussion (Misc queries) | |||
Logging data from a web query | Excel Programming |