Generate a list of files based on BOM and existing files

In the BOM records in the material code, the name of the material, material specifications, and according to the BOM has generated the corresponding documents, such as procurement specifications, inspection specifications, etc., this time need to obtain the title of these files, and generate a list, you can use the following VBA code, the specific code is as follows:

FunctionIsfileexists (ByValstrFileName as String) as Boolean    If Dir(strFileName, -) <> Empty Thenisfileexists=True    Elseisfileexists=False    End IfEnd FunctionSubSetName ()DimI as Integer    DimJ as Integer    DimPspname as String    DimPspnumber as String    DimTstname as String    DimTstnumber as String    DimPath as String    DimSrcpath as String    DimSrcPath2 as String    DimHeadname as String    DimHeadName2 as String    DimTxthead as String        DimWordApp as Object    DimWordDoc as Object    DimWordarange as Object    DimWordselection as Object    DimReplacesign as Boolean        DimSearch1 as String    DimSearch2 as String    DimDocprefix as String    DimDocsuffix as String    DimRangsize as Integer            'docprefix = "-PSP"    'docsuffix = "Purchase spec. doc"    'Search1 = "Wire"    'SEARCH2 = "6000397-PSP"    'rangsize =Docprefix="-"Docsuffix="Admission Inspection report. Doc"Search1="High voltage power supply"SEARCH2="6000000-tst"    'Search1 = "AC-DC switching power supply"    'SEARCH2 = "6000412-TST"Rangsize = -J=1    Dimmyitem'myitem = Array (14, 16, 17, 18, 22, 23, 24, 26, 27, 31, 32, 33, 34, 35, 36, 48, 50, 55, 56, 62, 63, 64, 65, 66, 67, 68, 69, 71, 73, 77, 79, 102, 114, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 1 44, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 167, 168 , 172, 173, 174, 175, 176, 177, 179, and 181)     forI =1  to 187Srcpath="C:\cygwin\tmp\BOM\tst16.doc"        IfActiveSheet.Cells (I,5) =""  ThenheadName2= ActiveSheet.Cells (I,3) &"-"& ActiveSheet.Cells (I,4) &"-"& ActiveSheet.Cells (I,5) Headname= HeadName2 &Docsuffix HeadName3= ActiveSheet.Cells (I,4)        ElseheadName2= ActiveSheet.Cells (I,3) &"-"& ActiveSheet.Cells (I,4) &"-"& ActiveSheet.Cells (I,6) Headname= HeadName2 &Docsuffix HeadName3= ActiveSheet.Cells (I,4) &"("& ActiveSheet.Cells (I,5) &")"        End IfHeadname=Replace(Headname,"/","-") path="D:\bom\"srcPath2= Path &"\aa.doc"        'pspname = path & "\" & ActiveSheet.Cells (I, 3) & Docprefix & ActiveSheet.Cells (i, 4) & docsuffix
    Pspname ="D:\bom\"& ActiveSheet.Cells (I,3) &"-tst-v1.0.doc"Tstname="D:\bom\"& ActiveSheet.Cells (I,3) &"-tst-v1.0.doc"Tstnumber= ActiveSheet.Cells (I,3) &"-tst"                IfIsfileexists (Pspname) =True  Then            'FileCopy Srcpath, SrcPath2            'Name srcPath2 as Tstname                        SetWordApp =CreateObject("Word.Application")'Create a Word instanceWordapp.visible =False                                         'block Word instance forms            SetWordDoc = WordApp.Documents.Open (tstname)'Open the file and give the file instance            SetWordselection = Wordapp.selection'Locating file Instances            SetWordarange = WordApp.ActiveDocument.Range (0, rangsize)'Specify file Edit locationWordarange.select'Activate edit LocationTxthead=Wordarange Txthead=Application.WorksheetFunction.Clean (txthead) Txthead=Trim(Txthead)' Do            'replacesign = WordArange.Find.Execute ("XXX", true,,,,, wdReplaceAll, Wdfindcontinue,, HeadName3, True)            'Loop Until replacesign = False                                                                            'For each rngstory in Worddoc.storyranges            ' Do            'replacesign = RngStory.Find.Execute (Search2, True,,,,, wdReplaceAll, Wdfindcontinue,, Tstnumber, True)            'Set rngstory = Rngstory.nextstoryrange            'Loop Until rngstory is nothing            'NextWorddoc.save Worddoc.closeTruewordapp.quit activesheet.cells (I, A) =tstnumber activesheet.cells (I, -) =txthead activesheet.cells (J, the) =tstnumber activesheet.cells (J, -) =Txthead J= J +1        End If    NextIEnd Sub

Generate a list of files based on BOM and existing files