Andreas posted an example here:
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?
PI DataLink version is 220.127.116.11
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
@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.