4 Replies Latest reply on Apr 9, 2018 3:24 PM by spadz01

    Pi Datalink and Excel

    spadz01

      The following are a few lines of Excel VBA code for an application that pulls data from a PI server and uses it
      to produce graphs:

       

      Worksheets("Data").Range("O2:P2").FormulaArray = "=PISampDat(" & Chr(34) & "\\RTISFS-FTSE\" & PItag & Chr(34) & "," & _

                                                                                              Chr(34) & PIDataStartTime & Chr(34) & "," & _

                                                                                              Chr(34) & PIDataStopTime & Chr(34) & "," & _

                                                                                              Chr(34) & "1s" & Chr(34) & ",1," & Chr(34) & _

                                                                                              Chr(34) & ")"

      Range("O2:P2").select

      Call
      dlresize

       

      When the code executes, #NAME? appears in worksheet cell O2 and P2 because it appears that Excel doesn't recognize the string entered into these cells as a PI formula. If I break the code just prior to the execution of the formula assignment (first line) in the code above, and then in the debugger immediately tell it to continue code execution, everything performs as expected and good data populates the array. It seems to be some sort of timing issue.

       

      Has anyone seen this weird type of problem before?

       

      Thanks.

        • Re: Pi Datalink and Excel
          Eugene Lee

          Hi Frank,

           

          I didn't have this problem when trying the code. Maybe you need to clear the range first. Here is my code.

           

          Range("O:P").Clear
          Sheet1.Range("O2:P2").FormulaArray = "=PISampDat(" & Chr(34) & "\\servername\" & "cdt158" & Chr(34) & "," & Chr(34) & "*-10m" & Chr(34) & "," & Chr(34) & "*" & Chr(34) & "," & Chr(34) & "1s" & Chr(34) & ",1," & Chr(34) & Chr(34) & ")"
          Range("O2:P2").Select
          Call dlresize