4 Replies Latest reply on Aug 27, 2010 12:27 PM by janio

    Auto Update from excel

    janio

      Hi,

       

      I have an excel spreadsheet with the data of an online accompaniment and I need start automaticaly the auto-update of PI in Excel using VB when the Excel initialize. The support of OSIsoft says that it is not possible.

       

      I used a simple solution describe above and i want know if anyone know another solution to help me?

       

      Thank you.

      Sub RealTime()

          recalcula = Now + TimeValue("00:00:10")
          Application.Workbooks("SSC_PI_V1.xls").Application.ontime recalcula, "atualizar"
      End Sub

      Sub atualizar()
          Application.Workbooks("SSC_PI_V1.xls").Worksheets("principal").Cells(7, 5) = Now
          Application.Workbooks("SSC_PI_V1.xls").Application.CalculateFullRebuild
          Call RealTime
      End Sub

        • Re: Auto Update from excel

          Have you looked into the Update function provided in PI DataLink 2010 (that was introduced in PI DataLink 4.1)?

          • Re: Auto Update from excel
            janio

            Hi,

             

            In the datalink 4.1 we have an auto-update function that works well but we need click in the button to start.

             

            I need a feature that starts an update of the data when the excel starts in the sub workbook_open() because i don't want that the user needs to click in the auto-update button. I tried to start the PI auto-update function with the VB but I couldn't and the support tell me that it is not possible.

             

            I think that the solution that i was used is the best solution but I want know if exist another solution.

             

            Thank's

            • Re: Auto Update from excel
              DaveWilson

              I had a similar use for an automated update every 60 seconds, starting from opening the spreadsheet. I solved it with;


              Private Sub Workbook_Open()
                  StartTimer
              End Sub

              Sub RunAll()
                  Application.Workbooks("SinusoidData.xls").Application.CalculateFullRebuild
                  StartTimer
              End Sub

              Sub StartTimer()
                  ' The timer routine is atarted when the sheet is opened, and runs for the 60 seconds, after
                  ' which it runs the subroutine above, which again calls the timer routine and it starts
                  ' all over again.
                  Application.OnTime EarliestTime:=TimeValue(Now() + TimeSerial(0, 0, 60)), procedure:="RunAll", schedule:=True
              End Sub