Macro stops when another workbook is open

R

Robert McMahon

I have a workbook that has several sheets in it. The workbook is
running 2 macros that get data from an external program via dde and
insert it into forms that are in said workbook. My problem is that I
need to be able to open other workbooks and use excel but KEEP THE
ORIGINAL WORKBOOK OPEN AND THE MACRO RUNNING. The macros stop when
you open another workbook. They become the active workbook and it
throughs the macro off. Is there a way to stop this from happening.
Thanks in advance

RPM
P.S. Here is my main macro

Public RunWhen As Double
Public Const cRunIntervalSeconds = 59
Public Const cRunWhat = "TurbidityPointer"
Dim RowPointer As Integer
Sub Timer()

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True

'Form reset. If you type yes in cell B1 then the form will clear
'its contents and start logging at the appropriate time stamp
If Worksheets("Sheet1").cells(1, 2) = "yes" Then
Worksheets("turbidity").Select
cells.Range("D3:G98").ClearContents

'If the RowPointer Reset was active when the macro polled then it
would clear itself
Worksheets("Sheet1").cells(1, 2) = ""

End If
End Sub
Sub TurbidityPointer()
RowPointer = 1




'This module moves the Turbidity Pointer Index up by one number
corresponding to the
'time on the spreadsheet.


'Hour 0
If Hour(Now) = 0 And Minute(Now) = 0 Then
RowPointer = RowPointer + 2
Data
End If
If Hour(Now) = 0 And Minute(Now) = 15 Then
RowPointer = RowPointer + 3
Data
End If
If Hour(Now) = 0 And Minute(Now) = 30 Then
RowPointer = RowPointer + 4
Data
End If
If Hour(Now) = 0 And Minute(Now) = 45 Then
RowPointer = RowPointer + 5
Data
End If


'Hour 1
If Hour(Now) = 1 And Minute(Now) = 0 Then
RowPointer = RowPointer + 6
Data
End If
If Hour(Now) = 1 And Minute(Now) = 15 Then
RowPointer = RowPointer + 7
Data
End If
If Hour(Now) = 1 And Minute(Now) = 30 Then
RowPointer = RowPointer + 8
Data
End If
If Hour(Now) = 1 And Minute(Now) = 45 Then
RowPointer = RowPointer + 9
Data
End If


'Hour 2
If Hour(Now) = 2 And Minute(Now) = 0 Then
RowPointer = RowPointer + 10
Data
End If
If Hour(Now) = 2 And Minute(Now) = 15 Then
RowPointer = RowPointer + 11
Data
End If
If Hour(Now) = 2 And Minute(Now) = 30 Then
RowPointer = RowPointer + 12
Data
End If
If Hour(Now) = 2 And Minute(Now) = 45 Then
RowPointer = RowPointer + 13
Data
End If


'Hour 3
If Hour(Now) = 3 And Minute(Now) = 0 Then
RowPointer = RowPointer + 14
Data
End If
If Hour(Now) = 3 And Minute(Now) = 15 Then
RowPointer = RowPointer + 15
Data
End If
If Hour(Now) = 3 And Minute(Now) = 30 Then
RowPointer = RowPointer + 16
Data
End If
If Hour(Now) = 3 And Minute(Now) = 45 Then
RowPointer = RowPointer + 17
Data
End If


'Hour 4
If Hour(Now) = 4 And Minute(Now) = 0 Then
RowPointer = RowPointer + 18
Data
End If
If Hour(Now) = 4 And Minute(Now) = 15 Then
RowPointer = RowPointer + 19
Data
End If
If Hour(Now) = 4 And Minute(Now) = 30 Then
RowPointer = RowPointer + 20
Data
End If
If Hour(Now) = 4 And Minute(Now) = 45 Then
RowPointer = RowPointer + 21
Data
End If


'Hour 5
If Hour(Now) = 5 And Minute(Now) = 0 Then
RowPointer = RowPointer + 22
Data
End If
If Hour(Now) = 5 And Minute(Now) = 15 Then
RowPointer = RowPointer + 23
Data
End If
If Hour(Now) = 5 And Minute(Now) = 30 Then
RowPointer = RowPointer + 24
Data
End If
If Hour(Now) = 5 And Minute(Now) = 45 Then
RowPointer = RowPointer + 25
Data
End If


'Hour 6
If Hour(Now) = 6 And Minute(Now) = 0 Then
RowPointer = RowPointer + 26
Data
End If
If Hour(Now) = 6 And Minute(Now) = 15 Then
RowPointer = RowPointer + 27
Data
End If
If Hour(Now) = 6 And Minute(Now) = 30 Then
RowPointer = RowPointer + 28
Data
End If
If Hour(Now) = 6 And Minute(Now) = 45 Then
RowPointer = RowPointer + 29
Data
End If


'Hour 7
If Hour(Now) = 7 And Minute(Now) = 0 Then
RowPointer = RowPointer + 30
Data
End If
If Hour(Now) = 7 And Minute(Now) = 15 Then
RowPointer = RowPointer + 31
Data
End If
If Hour(Now) = 7 And Minute(Now) = 30 Then
RowPointer = RowPointer + 32
Data
End If
If Hour(Now) = 7 And Minute(Now) = 45 Then
RowPointer = RowPointer + 33
Data
End If


'Hour 8
If Hour(Now) = 8 And Minute(Now) = 0 Then
RowPointer = RowPointer + 34
Data
End If
If Hour(Now) = 8 And Minute(Now) = 15 Then
RowPointer = RowPointer + 35
Data
End If
If Hour(Now) = 8 And Minute(Now) = 30 Then
RowPointer = RowPointer + 36
Data
End If
If Hour(Now) = 8 And Minute(Now) = 45 Then
RowPointer = RowPointer + 37
Data
End If


'Hour 9
If Hour(Now) = 9 And Minute(Now) = 0 Then
RowPointer = RowPointer + 38
Data
End If
If Hour(Now) = 9 And Minute(Now) = 15 Then
RowPointer = RowPointer + 39
Data
End If
If Hour(Now) = 9 And Minute(Now) = 30 Then
RowPointer = RowPointer + 40
Data
End If
If Hour(Now) = 9 And Minute(Now) = 45 Then
RowPointer = RowPointer + 41
Data
End If


'Hour 10
If Hour(Now) = 10 And Minute(Now) = 0 Then
RowPointer = RowPointer + 42
Data
End If
If Hour(Now) = 10 And Minute(Now) = 15 Then
RowPointer = RowPointer + 43
Data
End If
If Hour(Now) = 10 And Minute(Now) = 30 Then
RowPointer = RowPointer + 44
Data
End If
If Hour(Now) = 10 And Minute(Now) = 45 Then
RowPointer = RowPointer + 45
Data
End If


'Hour 11
If Hour(Now) = 11 And Minute(Now) = 0 Then
RowPointer = RowPointer + 46
Data
End If
If Hour(Now) = 11 And Minute(Now) = 15 Then
RowPointer = RowPointer + 47
Data
End If
If Hour(Now) = 11 And Minute(Now) = 30 Then
RowPointer = RowPointer + 48
Data
End If
If Hour(Now) = 11 And Minute(Now) = 45 Then
RowPointer = RowPointer + 49
Data
End If


'Hour 12
If Hour(Now) = 12 And Minute(Now) = 0 Then
RowPointer = RowPointer + 50
Data
End If
If Hour(Now) = 12 And Minute(Now) = 15 Then
RowPointer = RowPointer + 51
Data
End If
If Hour(Now) = 12 And Minute(Now) = 30 Then
RowPointer = RowPointer + 52
Data
End If
If Hour(Now) = 12 And Minute(Now) = 45 Then
RowPointer = RowPointer + 53
Data
End If


'Hour 13
If Hour(Now) = 13 And Minute(Now) = 0 Then
RowPointer = RowPointer + 54
Data
End If
If Hour(Now) = 13 And Minute(Now) = 15 Then
RowPointer = RowPointer + 55
Data
End If
If Hour(Now) = 13 And Minute(Now) = 30 Then
RowPointer = RowPointer + 56
Data
End If
If Hour(Now) = 13 And Minute(Now) = 45 Then
RowPointer = RowPointer + 57
Data
End If


'Hour 14
If Hour(Now) = 14 And Minute(Now) = 0 Then
RowPointer = RowPointer + 58
Data
End If
If Hour(Now) = 14 And Minute(Now) = 15 Then
RowPointer = RowPointer + 59
Data
End If
If Hour(Now) = 14 And Minute(Now) = 30 Then
RowPointer = RowPointer + 60
Data
End If
If Hour(Now) = 14 And Minute(Now) = 45 Then
RowPointer = RowPointer + 61
Data
End If


'Hour 15
If Hour(Now) = 15 And Minute(Now) = 0 Then
RowPointer = RowPointer + 62
Data
End If
If Hour(Now) = 15 And Minute(Now) = 15 Then
RowPointer = RowPointer + 63
Data
End If
If Hour(Now) = 15 And Minute(Now) = 30 Then
RowPointer = RowPointer + 64
Data
End If
If Hour(Now) = 15 And Minute(Now) = 45 Then
RowPointer = RowPointer + 65
Data
End If


'Hour 16
If Hour(Now) = 16 And Minute(Now) = 0 Then
RowPointer = RowPointer + 66
Data
End If
If Hour(Now) = 16 And Minute(Now) = 15 Then
RowPointer = RowPointer + 67
Data
End If
If Hour(Now) = 16 And Minute(Now) = 30 Then
RowPointer = RowPointer + 68
Data
End If
If Hour(Now) = 16 And Minute(Now) = 45 Then
RowPointer = RowPointer + 69
Data
End If


'Hour 17
If Hour(Now) = 17 And Minute(Now) = 0 Then
RowPointer = RowPointer + 70
Data
End If
If Hour(Now) = 17 And Minute(Now) = 15 Then
RowPointer = RowPointer + 71
Data
End If
If Hour(Now) = 17 And Minute(Now) = 30 Then
RowPointer = RowPointer + 72
Data
End If
If Hour(Now) = 17 And Minute(Now) = 45 Then
RowPointer = RowPointer + 73
Data
End If


'Hour 18
If Hour(Now) = 18 And Minute(Now) = 0 Then
RowPointer = RowPointer + 74
Data
End If
If Hour(Now) = 18 And Minute(Now) = 15 Then
RowPointer = RowPointer + 75
Data
End If
If Hour(Now) = 18 And Minute(Now) = 30 Then
RowPointer = RowPointer + 76
Data
End If
If Hour(Now) = 18 And Minute(Now) = 45 Then
RowPointer = RowPointer + 77
Data
End If


'Hour 19
If Hour(Now) = 19 And Minute(Now) = 0 Then
RowPointer = RowPointer + 78
Data
End If
If Hour(Now) = 19 And Minute(Now) = 15 Then
RowPointer = RowPointer + 79
Data
End If
If Hour(Now) = 19 And Minute(Now) = 30 Then
RowPointer = RowPointer + 80
Data
End If
If Hour(Now) = 19 And Minute(Now) = 45 Then
RowPointer = RowPointer + 81
Data
End If


'Hour 20
If Hour(Now) = 20 And Minute(Now) = 0 Then
RowPointer = RowPointer + 82
Data
End If
If Hour(Now) = 20 And Minute(Now) = 15 Then
RowPointer = RowPointer + 83
Data
End If
If Hour(Now) = 20 And Minute(Now) = 30 Then
RowPointer = RowPointer + 84
Data
End If
If Hour(Now) = 20 And Minute(Now) = 45 Then
RowPointer = RowPointer + 85
Data
End If


'Hour 21
If Hour(Now) = 21 And Minute(Now) = 0 Then
RowPointer = RowPointer + 86
Data
End If
If Hour(Now) = 21 And Minute(Now) = 15 Then
RowPointer = RowPointer + 87
Data
End If
If Hour(Now) = 21 And Minute(Now) = 30 Then
RowPointer = RowPointer + 88
Data
End If
If Hour(Now) = 21 And Minute(Now) = 45 Then
RowPointer = RowPointer + 89
Data
End If


'Hour 22
If Hour(Now) = 22 And Minute(Now) = 0 Then
RowPointer = RowPointer + 90
Data
End If
If Hour(Now) = 22 And Minute(Now) = 15 Then
RowPointer = RowPointer + 91
Data
End If
If Hour(Now) = 22 And Minute(Now) = 30 Then
RowPointer = RowPointer + 92
Data
End If
If Hour(Now) = 22 And Minute(Now) = 45 Then
RowPointer = RowPointer + 93
Data
End If


'Hour 23
If Hour(Now) = 23 And Minute(Now) = 0 Then
RowPointer = RowPointer + 94
Data
End If
If Hour(Now) = 23 And Minute(Now) = 15 Then
RowPointer = RowPointer + 95
Data
End If
If Hour(Now) = 23 And Minute(Now) = 30 Then
RowPointer = RowPointer + 96
Data
End If
If Hour(Now) = 23 And Minute(Now) = 45 Then
RowPointer = RowPointer + 97
Data
End If


Timer

End Sub

Sub Data()






'DDE Transfer from Wonderware

channelNumber = Application.DDEInitiate( _
App:="View", _
topic:="Tagname")

ChannelNum = DDEInitiate("View", "Tagname")
F1 = DDERequest(ChannelNum, "Filter1Turbidity")
F2 = DDERequest(ChannelNum, "Filter2Turbidity")
F3 = DDERequest(ChannelNum, "Filter3Turbidity")
F5 = DDERequest(ChannelNum, "Filter4Turbidity")
Worksheets("Turbidity").cells(RowPointer, 4).Value = F1
Worksheets("Turbidity").cells(RowPointer, 5).Value = F2
Worksheets("Turbidity").cells(RowPointer, 6).Value = F3
Worksheets("Turbidity").cells(RowPointer, 7).Value = F5
Application.DDETerminate channelNumber




'checks to see if the spreadsheet is full and saves the data
If RowPointer = 98 Then
SaveData
End If



Timer
End Sub



Sub SaveData()
'Saves Turbidity Data as the Day of the Year in the
c:\Reports\Turbidity folder


Worksheets("turbidity").Activate
cells.Select
Selection.Copy
Range("A1").Select
Workbooks.Open Filename:="C:\Reports\Standby.xls"
Workbooks("standby").Activate
ActiveSheet.Next.Select
ActiveSheet.Paste
Range("A1").Select

Dim Today As Date
Today = Date
ActiveWorkbook.SaveAs Filename:="c:\reports\" & Format(Date,
"mmmmddyyyy") & ".xls"
ActiveWorkbook.Close


Workbooks("EagleOne").Activate
Worksheets("turbidity").Select
cells.Range("D3:G98").ClearContents

RowPointer = 3

End Sub
 
H

Henry

Robert,

I don't think the macro stops. What I think's happening is that the
processor is
tied up with other things when your times come round so it misses the
readings.

Try:

If Hour(Now) = 0 And Minute(Now) => 0 And Minute (Now) <15 Then

I know this won't get a reading *exactly* every 15 minutes,
but you're virtually guaranteed to get a reading within the 15 minute
window.

HTH
Henry
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating Elapsed time in a macro 1
Graph usage for times of day 0
15 minute data to daily data 1
Fastest way to delete rows 12
Ontime problem; variable not passed from Workbook_Open to StartTim 4
exit event 3
Workbook reminder 0
TextBox.Visible = True ... not working 3

Top