Need to extract part / document data in excel

Hi Gurus',



Does anybody know of a macro / code which can be used to populate an
excel with Windchill parts and documents, with hyperlink to them, so
that when I click on the hyperlink, it opens up?

Also, I need to know, if we can integrate ProductView with excel, in the
sense, that, when I click on the link in excel, if it's a part, it
should open the PViewer, and display the same, so also for the documents
having attachment.



Rgds,

Ravin


Please do not print this email unless it is absolutely necessary.

The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.

WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.

www.wipro.com

Ravin,

I wrote two standard reports with the Report Manager, which I can query from an Excel macro into sheets that I then massage into one, with links that I crteate from the macro.

 

I can read a Windchill report into a sheet this way :  

 

 

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;" & URL _
        , Destination:=Range("A1"))
        .Name = "Rapports de problèmes"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingAll
        .WebPreFormattedTextToColumns = False
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

where the URL variable is the URL that opens the report. That is :

 http://<host>/Windchill/servlet/WindchillAuthGW/wt.enterprise.URLProcessor/invokeAction?action=ProduceReport&oid=OR%3Awt.query.template.ReportTemplate%3A4289096&u8=1

 

You only need to replace %3A4289096 with %3A<id of report>   ,The report id can be found with a report on reports (see attached qml file), where the useful information is  the persistance id of the report template.

 

Another solution is to create a microsoft query file (see example.iqy) that you will use as a data source in excel

 

To add links to data on the web site, you need to output the object ids of the parts or document, then in excel you can construct URLs to open them by adding the appropriate heading which you can find in the browser's address bar when such object is displayed. For instance, to display a problem report, you would use :

http://<site>/Windchill/servlet/WindchillAuthGW/wt.enterprise.URLProcessor/URLTemplateAction?action=ObjProps&oid=<object id of report>&u8=1

 

To make it a link in the current cell, you do :

Set link = currentCell.Hyperlinks.Add(currentCell, URL)

 

HTH,

 

Vincent 

 

 

In Reply to Ravin Kayasth:

Hi Gurus',



Does anybody know of a macro / code which can be used to populate an
excel with Windchill parts and documents, with hyperlink to them, so
that when I click on the hyperlink, it opens up?

Also, I need to know, if we can integrate ProductView with excel, in the
sense, that, when I click on the link in excel, if it's a part, it
should open the PViewer, and display the same, so also for the documents
having attachment.



Rgds,

Ravin


Please do not print this email unless it is absolutely necessary.

The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.

WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.

www.wipro.com