There is, OSI documentation should help (...\PIPC\Help\PITrendAuto.chm). You want to focus on to the AddQuerySpecs method to set the range for a PIDAXL.PIExcelData object and the DataSourceEnvironments object for the actual PITrend.
Dim obj as OLEObject
Set obj = Me.OLEObjects("PIExcelData1_Sheet1")
obj.Object.AddQuerySpecs "$A$1:$C$3", True
Hope the OSI author doesn't take offence, but the documentation for this type of manipulation is not the best - I had to learn a lot of it the hard way...
Thanks Rhys . The above code worked for me.
For removing and adding the tags , this is the code I used.
Sheet1.PIArchiveData1_Sheet1.AddQuerySpec "servername", "newtag"
Eventhough looks simle , such example can be added in OSISoft Documentation.
I wasn't able to modify existing tag (hence the reason for using removequeryspec instead of changing tag name to newtag)