3 Replies Latest reply on Apr 4, 2014 2:48 PM by mhamel

    inserttread function vba question

    rsun

      I was trying to insert a tread into a worksheet called "CurrentTread", but all I can do it to inserttread into a new worksheet everytime.

       

       

       

      Can I get an example of what to put in the first arg in the inserttread function

       

       

       

      inserttread(worksheet,range)

       

      I kind of know Range is "$A$1:$H$15" which can be a string

       

      I was trying to put the worksheet argument as "CurrentTread", but it seems like it is a string but not the object of the worksheet

       

       

        • Re: inserttread function vba question
          Sam Pride

          Hi Ricky,

           

          Andreas posted an example here:

           

          vcampus.osisoft.com/.../1290.aspx

           

          the InsertTrend method accepts either a Worksheet Object ( Worksheets("worksheetName") or Worksheets(1) etc.) or the name of the worksheet.

           

          If you Omit the worksheet name altogether, it will use the active sheet.

           

          If this isn't working, what version of DataLink and Excel are you using? Are you able to provide a snippet of your code or the workbook itself?

            • Re: inserttread function vba question
              rsun

              Hi Sam,
              PI DataLink version is 4.1.1.0

               

              Excel version is 2007

               

              If I use InsertTrend without any argument, I can create the trend on a new worksheet.

               

              However, I wanted to put the trend into the worksheet "CurrentTrend"

               

              How can I do that without the two lines of code Sheets("Sheet1").Activate and Sheets("CurrentTrend").Activate, so it doesn't go back and forward to the users.

               
              Sub Button_Click()
              
                  Dim ws As Worksheet
                  Application.DisplayAlerts = False
                  Worksheets("CurrentTrend").delete    'CC: Deletes CurrentTrend worksheet
                  
                  'Using TopLeftCell property to find out the row we are clicking on and storing it in the variable cs
                  Dim b As Object, cs As Integer
                  Set b = ActiveSheet.Buttons(Application.Caller)
                  With b.TopLeftCell
                      cs = .Row
                  End With
                  
                  'Extracting the value of column H which consists of the Tag name and storing it in the variable TagNames
                  Dim TagNames As String 'Declares TagName variable as string
                  
                  TagNames = ActiveSheet.Range("H" & cs).Value
                  
                  Dim xlTrndWiz As PIXLTWIZ.ExcelTrendWizard
                  Dim OLEObj As OLEObject
                  Dim obj As Object
                  Dim strName As String
                  Set OLEObj = ActiveWorkbook.ActiveSheet.OLEObjects.Add("PIXLTWIZ.ExcelTrendWizard")
                  Set xlTrndWiz = OLEObj.Object
                  
                  xlTrndWiz.PIAddQuerySpec "uascuppicoll", TagNames, "-40d" 'Using the variable TagNames instead of physically entering each Tag, CC: You just needed to drop the quotes!
                  Worksheets.Add(After:=Worksheets(1)).Name = "CurrentTrend" 'CC: Adds CurrentTrend worksheet
                  xlTrndWiz.InsertTrend "CurrentTrend" 'CC: Specifies insertion into CurrentTrend worksheet
                  xlTrndWiz.PISetTimeRange False, "*-40d", "*"
              
                  Sheets("Sheet1").Activate
                  Sheets("CurrentTrend").Activate
              
                  
                  End Sub
               
              

               

               

               

               

               

                • Re: inserttread function vba question
                  mhamel

                  @Ricky: When you delete an Excel sheet you cannot have your "focus" on it otherwise you will get an exception. For having a good workflow for this process, it makes sense to put back the focus on the newly created worksheet.

                   

                  I believe that you could get rid of the activation of sheet1.