3 Replies Latest reply on Jul 13, 2010 4:21 AM by spilon

    Accessing Excel Trend Control timestamps from OnTimeRangeChange event

    aarloe

      Hello,

       

      I am trying to store the time range in a couple of cells whenever a user changes the time on a trend, such that if they zoom in/zoom out etc, the start time and end time of the PB Trend (in a DataLink Spreadsheet) is stored in two cells. I have tried this and notice that the StartTime and EndTime are passed as Variants in the method, and when I try to convert them to date formats (using VBA's CDate() method) I get an overflow error.

       

      Is there a (better) way to store the Start Time and End Time when a user changes the time range in a PB trend in Excel? I know the trend control in DataLink isn't say, the "greatest" of trend objects, but I think I should be able to programatically extract the Start & End times from it, right? Any help is appreciated!

       

      Here's the code i have so far btw:

      Private Sub PITrend1_Sheet1_OnTimeRangeChange(ByVal Source As PBWebClient.pbwTimeRangeChangeSourceEnum, ByVal Temporary As Boolean, ByVal StartTime As Variant, ByVal EndTime As Variant, ByVal TimeZoneInfo As Variant)

      Dim st, et As Date
      st = CDate(StartTime)
      et = CDate(EndTime)

      Sheet1.Cells(1, 1) = st
      Sheet1.Cells(2, 1) = et

      End Sub

        • Re: Accessing Excel Trend Control timestamps from OnTimeRangeChange event
          hanyong

          Hi Aaron,

           

          I think StartTime and EndTime in the OnTimeRangeChange event is the UTCSeconds from 1-Jan-1970. Instead of casting to a Date, you can add reference to PITimeServer type library and assign UTCSeconds of a PITime object as StartTime (or EndTime) and use PITime.LocalDate property to get the local time.

           

          A sample code snippet will be like this:

          Private Sub PITrend1_Sheet1_OnTimeRangeChange(ByVal Source As PBWebClient.pbwTimeRangeChangeSourceEnum, ByVal Temporary As Boolean, ByVal StartTime As Variant, ByVal EndTime As Variant, ByVal TimeZoneInfo As Variant)
              Dim st As PITime
              Dim et As PITime
              Set st = New PITime
              Set et = New PITime
              st.UTCSeconds = StartTime
              et.UTCSeconds = EndTime
             
              Sheet1.Cells(1, 1) = st.LocalDate
              Sheet1.Cells(2, 1) = et.LocalDate
          End Sub

           

          Hope this helps.