Skip to main content
avillanueva
23-Emerald I
avillanueva23-Emerald I
23-Emerald I
September 17, 2025
Solved

Sharing improved workflow for boosting AML/AVL completeness

  • September 17, 2025
  • 2 replies
  • 1310 views

I hope to share a method which I think might make filling out AML/AVL entries faster, at least in my company. See, most of my OEM parts are the same as the MFG part so it literally a copy task to fill out. The one by one method is too damn slow and with version 13's expose org preference, a total PITA. I've done most of the big sweeps via excel imports so what's left is small blocks of parts lacking AMLs. Tough to find and fill out. 

avillanueva_0-1758125684857.png

I have a report (QML) above that export the full AML/AVL I care about. I have observed that often similar part numbers are in order from the same MFG but just not entered in. I just need to add in MFG name and as a base, they would be approved to start. What I need is a way to make that notation and generate the import sheets for those notations. 

I have settled on a VBA macro (.xlsm) that will pull in the QML reports of AML/AVL with gaps and all Suppliers. From there, I can just note the MFG or Vendors as drop downs to make notations. Then a macro can spit out formatted part import and AML sheets I can load back into Windchill. I am using VBA and Excel since the input and output is all excel sheets and maybe I am not smart enought to know better. Use what you got. 

I will debug this and post as a reply to this thread that will include my QML reports, Excel sheets and VBA code as text file. You can reassemble on your end as you see fit. 

    Best answer by avillanueva

    Attached 

    Attached is my template sheet and the VBA code that you can assembly and modify. I deleted the connections but I had two linking to the QMLs I posted this morning. Again, if there are better ways or improvements, please send along.

    avillanueva_0-1758207142460.png

    Some notes on the code. I have two libraries for my buy parts that I coded in. General Parts Library and Electrical Parts Library. SUMA is picky about things being in the same context so update as needed for yours. You can change the defaults for the folder, Lifecycle, state etc in code blocks like below. I did add in a rule that if you provided a diffenent part number for a MFG or Vendor part, it will take it but pull name from OEM part. 

     target.Cells(y, 3) = ActiveCell.Offset(0, 1) 'Copy same Name
 target.Cells(y, 4) = "false" 'End Item
 target.Cells(y, 7) = "component" 'Assembly Mode
 target.Cells(y, 8) = "/Default/SUMA" 'Location
 target.Cells(y, 9) = Application.WorksheetFunction.VLookup(ActiveCell.Offset(0, 6), ThisWorkbook.Worksheets("ManufacturerIDs").Range("B:C"), 2, False) ' False for exact match 'Organization ID
 target.Cells(y, 11) = "Design" 'View
 target.Cells(y, 12) = "RELEASED" 'State
 target.Cells(y, 13) = "EOS Basic" 'Lifecycle
 target.Cells(y, 14) = "buy" 'Source
 target.Cells(y, 15) = "ea" 'default unit

    I tested this just before. I was able to spot 120 parts that in a sweep that were missing a supplier, generate sheets and import in less than 20 mins. An improvement for sure.

    2 replies

    avillanueva
    23-Emerald I
    avillanueva23-Emerald IAuthor
    23-Emerald I
    September 18, 2025

    Attached it two QML files (as zips) that you can use as reference. These are my source data I am extracting from Windchill. You can modify any of my unique constraints I added that do not apply for you. 

    Library-Parts-AML-BOM Usage2: This report pulls all buy parts on lastest BOMs (Mfg View which my id is 1447), released since 2022 (recent), where components are not obsolete or superseded. It displays if its classified in PartsLink node, shows any linked MFG parts and and Vendor parts associated with it. 

    All Suppliers: Pretty straight forward where is outputs all MFG and Vendor information along with their IDs for loading to a single table. I used this report in my spreadsheet to create a data validated column drop down list. 

     

    In both cases I use the Excel legacy web data connection to pull down reports to a worksheet for processing. I would love to improve this part so let me know your input here. Running URL below will output report as an HTML table that Excel can feed into a sheet. Always looking for better ways to do this part. 

    https://<myhostname>/Windchill/servlet/WindchillAuthGW/wt.enterprise.URLProcessor/URLTemplateAction?format=formatDelegate&delegateName=HTML&xsl1=&xsl2=&oid=OR%3Awt.query.template.ReportTemplate%3A<REPORTIDHERE>&action=ExecuteReport

    avillanueva_0-1758199948392.png

    Next post will be on the Excel sheet itself. 

      avillanueva
      23-Emerald I
      avillanueva23-Emerald IAuthorAnswer
      23-Emerald I
      September 18, 2025

      Attached 

      Attached is my template sheet and the VBA code that you can assembly and modify. I deleted the connections but I had two linking to the QMLs I posted this morning. Again, if there are better ways or improvements, please send along.

      avillanueva_0-1758207142460.png

      Some notes on the code. I have two libraries for my buy parts that I coded in. General Parts Library and Electrical Parts Library. SUMA is picky about things being in the same context so update as needed for yours. You can change the defaults for the folder, Lifecycle, state etc in code blocks like below. I did add in a rule that if you provided a diffenent part number for a MFG or Vendor part, it will take it but pull name from OEM part. 

       target.Cells(y, 3) = ActiveCell.Offset(0, 1) 'Copy same Name
 target.Cells(y, 4) = "false" 'End Item
 target.Cells(y, 7) = "component" 'Assembly Mode
 target.Cells(y, 8) = "/Default/SUMA" 'Location
 target.Cells(y, 9) = Application.WorksheetFunction.VLookup(ActiveCell.Offset(0, 6), ThisWorkbook.Worksheets("ManufacturerIDs").Range("B:C"), 2, False) ' False for exact match 'Organization ID
 target.Cells(y, 11) = "Design" 'View
 target.Cells(y, 12) = "RELEASED" 'State
 target.Cells(y, 13) = "EOS Basic" 'Lifecycle
 target.Cells(y, 14) = "buy" 'Source
 target.Cells(y, 15) = "ea" 'default unit

      I tested this just before. I was able to spot 120 parts that in a sweep that were missing a supplier, generate sheets and import in less than 20 mins. An improvement for sure.

      avillanueva
      23-Emerald I
      avillanueva23-Emerald IAuthor
      23-Emerald I
      September 24, 2025

      Here is an improvement which saves off the worksheets as separate import files. Modify as needed:

      Sub SaveWorksheetsAsNewFiles()

 Dim ws As Worksheet
 Dim newWorkbook As Workbook
 Dim filePath As String
 Dim fileName As String
 ' Set the worksheet you want to save
 Set ws = ThisWorkbook.Sheets("CreatePart-GenParts")
 ' Define the file path and name for the new workbook
 filePath = ThisWorkbook.Path ' Save to current location
 fileName = ws.Name & "_Import.xlsx" ' The new file name will be based on the sheet name
 ' Copy the worksheet to a new workbook
 ws.Copy
 ' Set the new workbook reference
 Set newWorkbook = ActiveWorkbook
 ' Save the new workbook
 newWorkbook.SaveAs fileName:=filePath & "\" & fileName, FileFormat:=xlOpenXMLWorkbook
 ' Close the new workbook
 newWorkbook.Close SaveChanges:=False
 End Sub

      AI is great

      Terms & ConditionsCookie settingsAccessibility statement