Comp replace by next or previous file in a network folder if reqd file not avble

[Wind_Talker]

Hi All,

I want to do a component replace operation and want to give user a choice of using the next available file or previous available file if reqd file is not available in network folder. To do this my idea is as follows,

1. Create an array list of all part files available in search location (network folder)
2. Display the name of all files in a single msg box. This is just a check to see if the array list has all the files.
3. Compare the given file name (required file) with file names in arraylist.
4. If file name in array list is greater than given file name, add those names to new arraylist.
5. If file name in array list is smaller than given file name, add those names to another new arraylist.

Basically array list in step 4 & 5 are the sub set of array list created in step 1. At this point there should be 3 array lists in total.

 6. Get the file name which has the smallest bolt length (in their name) from array list in step 4 and ask user if they want to use this file to do component replace. If yes, proceed and if No then Step 7.

7. Get the file name which has the highest bolt length (in their name) from array list in step 5 and ask user if they want to use this file to do component replace. If yes, proceed and if No then just display a msg saying "No file Replaced" and exit code.

Can anybody help to write the code to accomplish this?

Given File name format: FN = "ISO 4014 - " & BOLT_DESIG & "ISO.ipt"

Where BOLT_DESIG is an inventor parameter with values like M12 x 210. M12 is the dia of the bolt and 210 is bolt length.

Given file name example. FN = "ISO 4014 – M12 x 210 ISO.ipt"

Example:

Suppose network folder has following files in it.

ISO 4014 – M8 x 100 ISO.ipt

ISO 4014 – M8 x 210 ISO.ipt

ISO 4014 – M12 x 100 ISO.ipt

ISO 4014 – M12 x 150 ISO.ipt

ISO 4014 – M12 x 220 ISO.ipt

ISO 4014 – M12 x 240 ISO.ipt

ISO 4014 – M16 x 200 ISO.ipt

ISO 4014 – M16 x 220 ISO.ipt

Now as you can see the given file name "ISO 4014 – M12 x 210 ISO.ipt" is not available. So the code should ask user if they want to use “ISO 4014 – M12 x 220 ISO.ipt” to do replace component as result of step 6.

If no then code should ask if they want to use “ISO 4014 – M12 x 150 ISO.ipt” to do replace component as result of step 7.

As per this example, the array list in step 4 will have following entries.

ISO 4014 – M12 x 220 ISO.ipt

ISO 4014 – M12 x 240 ISO.ipt

The array list in step 5 will have following entries.

ISO 4014 – M12 x 100 ISO.ipt

ISO 4014 – M12 x 150 ISO.ipt

I hope it makes it clear what the goal is.

I have done step 1 as follows but I am stuck after that.

Dim fullfilelist As New ArrayList
Dim SearchLocation As String = "\\BG-DOC1\am\Vault,D-Comtec\CONTENT CENTRE FILES\en-US\ISO 4014(2)\"
Dim File As String
    Dim filename As String()
        filename = System.IO.Directory.GetFiles(SearchLocation, "*.ipt")
        For Each File In SearchLocation
            fullfilelist.Add(File)
        Next

A very humble thank you in advance for all of your help.

[Owner2229]

Hi, first of, when I look at the “ISO 4014 – M12 x 150 ISO.ipt” a couple of questions rises:

1) Do you want this to work for "ISO 4014..." only or for others as well?

2) If yes, does the lenght of the prefix vary over these norms? Like "ISO 4014", "DIN 909"...

3) Is the surfix always present?

4) Is the surfix always separated with space?

5) Are you using only metric bolts?

6) Do you want to use this only for bolts? Or also for nuts and washers?

I'm trying to determine if the rule should look for the actual value of the bold by cuting the prefix and surfix by static lenght or rather look for the dash (for prefix) and the space (for surfix).

Anyway, this code (so far) uses search for the dash and space:

' 1)
Dim fullfilelist As New ArrayList
Dim SearchLocation As String = "\\BG-DOC1\am\Vault,D-Comtec\CONTENT CENTRE FILES\en-US\ISO 4014(2)\"
Dim File As String
Dim filename As String()
filename = System.IO.Directory.GetFiles(SearchLocation, "*.ipt")
For Each File In SearchLocation
    fullfilelist.Add(File)
Next

' 2)
Dim tText As String
Dim oItem As String
For Each oItem In FullFileList
    tText = tText & vblf & oItem
Next
MsgBox(tText)

' 3)
Dim FilesBigger As New ArrayList
Dim FilesSmaller As New ArrayList
For Each oItem In FullFileList
    Dim FNP As Integer = InStrRev(oItem, "-")
    Dim fVals As String = Mid(oItem, FNP + 3)
    FNP = InStrRev(fVals, " ")
    fVals = Left(fVals, FNP - 1)
    ' ... ****, I have to do something now, I'll finish it later for you, don't worry
Next

[Wind_Talker]

Hi Mike,

Thanks for your reply. Answer to your questions are as follows.

1. At this point, I want it for only ISO 4014 family of bolts.

2. No, the prefix will always be ISO 4014.

3. Yes, the suffix is always present in file name.

4. Yes, there is  a space. This is our standard way to give file names to content centre parts.

5. Yes, metric bolts only.

6. Only for bolts. Nut and washer will never encounter a case of requested file not available.

From one bolt to the next, for a fixed dia (M8, M10, M12 etc.) only the length of bolt changes. The rest of the name of the file ramains unchanged. The user has already selected a dia for the bolt (in another rule) they want to use but there is a possibilty that the required bolt length is not available. So I want to give user an option to use next bolt length available as an alternative (if they choose to do so). The next available option can be either a bolt length bigger than required (i.e. next higher number available in folder) or can be smaller than required (next smaller number available in folder).

I hope that answer your query. If you have any more questions please let me know.

Regards

[Owner2229]

So, I'm back with the rest of the code:

Sub Main()
' 3) Look for the file directly
Dim FN As String = "ISO 4014 – M12 x 210 ISO.ipt"
Dim SearchLocation As String = _
"\\BG-DOC1\am\Vault,D-Comtec\CONTENT CENTRE FILES\en-US\ISO 4014(2)\"
If Dir(SearchLocation & FN) <> 0 Then
    MsgBox("This bolt exists" & vblf & "Congratulations, you've found it !")
    Exit Sub
End If

' 1) Get all the files
Dim fullfilelist As New ArrayList
Dim File As String
Dim filename As String()
filename = System.IO.Directory.GetFiles(SearchLocation, "*.ipt")
For Each File In SearchLocation
    fullfilelist.Add(File)
Next

' 2) Create messagebox with all the bolts
Dim tText As String
Dim oItem As String
For Each oItem In FullFileList
    tText = tText & vblf & oItem
Next
MsgBox(tText)

' 4) And 5) Create arrays with bigger and smaller sizes
Dim sName As String = GetShortName(FN)
Dim FNP As Integer = InStrRev(UCase(sName), "X")
Dim sDia As Integer = Val(Left(fVals, FNP - 2)) ' -2 to cut off " X"
Dim sLen As Integer = Val(Mid(fVals, FNP + 2)) ' +2 to cut off "X "
Dim FilesBigger As New ArrayList
Dim FilesSmaller As New ArrayList
For Each oItem In FullFileList
    Dim fVals As String = GetShortName(oItem)
    FNP = InStrRev(UCase(fVals), "X")
    Dim oDia As Integer = Val(Left(fVals, FNP - 2))
    Dim oLen As Integer = Val(Mid(fVals, FNP + 2))
    If sDia > oDia Then
        ' FilesSmaller.Add(oItem) ' smaller diameter, we don't want it
    ElseIf sDia = oDia
        If sLen > oLen Then
            FilesSmaller.Add(oItem)
        ElseIf sLen = oLen
            ' it's the same size, isn't it? Duno what to do with this info
        Else
            FilesBigger.Add(oItem)
        End If
    Else
        'FilesBigger.Add(oItem) 'bigger dia
    End If
Next

'6) Ask if the user wants to replace it
' For the sake of testing, I'll assume the last item of the FilesSmaller is
' the biggest one and vice versa for FilesBigger
Q1 = MessageBox.Show("Do you want to replace this:" & vblf & FN & vblf & vblf & _
"With this:" & vblf & FilesSmaller.Items.Item(FilesSmaller.Items.Count - 1), _
"MessageBox Title", MessageBoxButtons.YesNo)
' Items - 1, cos first item is 0
If Q1 = vbYes Then
    '  Your component replacing code <here> ?
    '  I can write it for you to, just let me know
Else
    Q2  = MessageBox.Show("Do you want to replace this:" & vblf & FN & vblf & vblf & _
    "With this:" & vblf & FilesBigger.Items.Item(0), _
    "MessageBox Title", MessageBoxButtons.YesNo)
    If Q2 = vbYes Then
        ' Same as above
    End If
End If
End Sub

Function GetShortName(oName As String) As String
    Dim FNP As Integer = InStrRev(oName, "-")
    Dim fVals As String = Mid(oName, FNP + 3) ' +3 to cut off "- M"
    FNP = InStrRev(fVals, " ")
    fVals = Left(fVals, FNP - 1) ' -1 to not include the space itself
End Function

[Wind_Talker]

Hi Mike,

Wow, That is fast. Will I ever be able to do that!!

I just quickly copied the code for the msg box display for step 2. It is not quite displaying what I want to see. The message box is displaying everything in one vertical line rather than one file name per line.

I can't see the file name as the message box is displaying one character per line and my guess is it is displaying full file name including path. I don't want the path to display only file name. You can use the display name property if you wish.

Can you look into it please.

I can't go through the rest of the code right now. I am off now and will be back on Tuesday.

Talk to you then.

One again your help is appreciated.

Regards

[Owner2229]

Hi, try this fixed version, hope it works now:

Sub Main()
' 3) Look for the file directly
Dim FN As String = "ISO 4014 – M12 x 210 ISO.ipt"
Dim SearchLocation As String = _
"\\BG-DOC1\am\Vault,D-Comtec\CONTENT CENTRE FILES\en-US\ISO 4014(2)\"
If Dir(SearchLocation & FN) <> 0 Then
    MsgBox("This bolt exists" & vblf & "Congratulations, you've found it !")
    Exit Sub
End If

' 1) Get all the files
Dim FullFileList As New ArrayList
Dim File As String
Dim Filename As String()
Filename = System.IO.Directory.GetFiles(SearchLocation, "*.ipt")
For Each File In SearchLocation
    Dim FNP As Integer = InStrRev(File, "\", -1)
    File = Mid(File, FNP + 1)
    FullFileList.Add(File)
Next

' 2) Create messagebox with all the bolts
Dim tText As String
Dim oItem As String
For Each oItem In FullFileList
    tText = tText & vblf & oItem
Next
MsgBox(tText)

' 4) And 5) Create arrays with bigger and smaller sizes
Dim sName As String = GetShortName(FN)
Dim FNP As Integer = InStrRev(UCase(sName), "X")
Dim sDia As Integer = Val(Left(fVals, FNP - 2)) ' -2 to cut off " X"
Dim sLen As Integer = Val(Mid(fVals, FNP + 2)) ' +2 to cut off "X "
Dim FilesBigger As New ArrayList
Dim FilesSmaller As New ArrayList
For Each oItem In FullFileList
    Dim fVals As String = GetShortName(oItem)
    FNP = InStrRev(UCase(fVals), "X")
    Dim oDia As Integer = Val(Left(fVals, FNP - 2))
    Dim oLen As Integer = Val(Mid(fVals, FNP + 2))
    If sDia = oDia
        If sLen > oLen Then
            FilesSmaller.Add(oItem)
        ElseIf sLen < oLen
            FilesBigger.Add(oItem)
        End If
    End If
Next

'6) Ask if the user wants to replace it
' For the sake of testing, I'll assume the last item of the FilesSmaller is
' the biggest one and vice versa for FilesBigger
Dim TempItem As String = FilesSmaller.Items.Item(FilesSmaller.Items.Count - 1)
Q1 = MessageBox.Show("Do you want to replace this:" & vblf & FN & vblf & vblf & _
"With this:" & vblf & TempItem, _
"MessageBox Title", MessageBoxButtons.YesNo)
' Items - 1, cos first item is 0
If Q1 = vbYes Then
    ReplaceComponent(FN, SearchLocation & TempItem)
Else
    TempItem = FilesBigger.Items.Item(0)
    Q2  = MessageBox.Show("Do you want to replace this:" & vblf & FN & vblf & vblf & _
    "With this:" & vblf & TempItem, _
    "MessageBox Title", MessageBoxButtons.YesNo)
    If Q2 = vbYes Then
        ReplaceComponent(FN, SearchLocation & TempItem)
    End If
End If
End Sub

Private Function GetShortName(oName As String) As String
    Dim FNP As Integer = InStrRev(oName, "-")
    Dim fVals As String = Mid(oName, FNP + 3) ' +3 to cut off "- M"
    FNP = InStrRev(fVals, " ")
    fVals = Left(fVals, FNP - 1) ' -1 to not include the space itself
End Function

Private Sub ReplaceComponent(FN As String, oName As String)
    FN = Left(FN, Len(FN) - 4) ' To cut off the ".ipt"
    Dim oDoc As Document = ThisApplication.ActiveDocument
    Dim oOcc As ComponentOccurrence
    For Each oOcc In oDoc.ComponentDefinition.Occurrences
        Dim ocName As String = oOcc.Name
        Dim FNP As Integer = InStrRev(ocName, ":" -1)
        ocName = Left(ocName, FNP - 1)
        If ocName = FN Then
            oOcc.Replace(oName, True) ' True to replace all occurrences
            Exit For
        End If
    Next
End Sub

[Owner2229 · Accepted solution]

Hi, just little correction, red is what I've changed. This caused the one character per line issue.

Sub Main()
' 3) Look for the file directly
Dim FN As String = "ISO 4014 – M12 x 210 ISO.ipt"
Dim SearchLocation As String = _
"\\BG-DOC1\am\Vault,D-Comtec\CONTENT CENTRE FILES\en-US\ISO 4014(2)\"
If Dir(SearchLocation & FN) <> 0 Then
    MsgBox("This bolt exists" & vblf & "Congratulations, you've found it !")
    Exit Sub
End If

' 1) Get all the files
Dim FullFileList As New ArrayList
Dim File As String
Dim Files As String()
Files = System.IO.Directory.GetFiles(SearchLocation, "*.ipt")
For Each File In Files
    Dim FNP As Integer = InStrRev(File, "\", -1)
    File = Mid(File, FNP + 1)
    FullFileList.Add(File)
Next

' 2) Create messagebox with all the bolts
Dim tText As String
Dim oItem As String
For Each oItem In FullFileList
    tText = tText & vblf & oItem
Next
MsgBox(tText)

' 4) And 5) Create arrays with bigger and smaller sizes
Dim sName As String = GetShortName(FN)
Dim FNP As Integer = InStrRev(UCase(sName), "X")
Dim sDia As Integer = Val(Left(fVals, FNP - 2)) ' -2 to cut off " X"
Dim sLen As Integer = Val(Mid(fVals, FNP + 2)) ' +2 to cut off "X "
Dim FilesBigger As New ArrayList
Dim FilesSmaller As New ArrayList
For Each oItem In FullFileList
    Dim fVals As String = GetShortName(oItem)
    FNP = InStrRev(UCase(fVals), "X")
    Dim oDia As Integer = Val(Left(fVals, FNP - 2))
    Dim oLen As Integer = Val(Mid(fVals, FNP + 2))
    If sDia = oDia
        If sLen > oLen Then
            FilesSmaller.Add(oItem)
        ElseIf sLen < oLen
            FilesBigger.Add(oItem)
        End If
    End If
Next

'6) Ask if the user wants to replace it
' For the sake of testing, I'll assume the last item of the FilesSmaller is
' the biggest one and vice versa for FilesBigger
Dim TempItem As String = FilesSmaller.Items.Item(FilesSmaller.Items.Count - 1)
Q1 = MessageBox.Show("Do you want to replace this:" & vblf & FN & vblf & vblf & _
"With this:" & vblf & TempItem, _
"MessageBox Title", MessageBoxButtons.YesNo)
' Items - 1, cos first item is 0
If Q1 = vbYes Then
    ReplaceComponent(FN, SearchLocation & TempItem)
Else
    TempItem = FilesBigger.Items.Item(0)
    Q2  = MessageBox.Show("Do you want to replace this:" & vblf & FN & vblf & vblf & _
    "With this:" & vblf & TempItem, _
    "MessageBox Title", MessageBoxButtons.YesNo)
    If Q2 = vbYes Then
        ReplaceComponent(FN, SearchLocation & TempItem)
    End If
End If
End Sub

Private Function GetShortName(oName As String) As String
    Dim FNP As Integer = InStrRev(oName, "-")
    Dim fVals As String = Mid(oName, FNP + 3) ' +3 to cut off "- M"
    FNP = InStrRev(fVals, " ")
    fVals = Left(fVals, FNP - 1) ' -1 to not include the space itself
End Function

Private Sub ReplaceComponent(FN As String, oName As String)
    FN = Left(FN, Len(FN) - 4) ' To cut off the ".ipt"
    Dim oDoc As Document = ThisApplication.ActiveDocument
    Dim oOcc As ComponentOccurrence
    For Each oOcc In oDoc.ComponentDefinition.Occurrences
        Dim ocName As String = oOcc.Name
        Dim FNP As Integer = InStrRev(ocName, ":" -1)
        ocName = Left(ocName, FNP - 1)
        If ocName = FN Then
            oOcc.Replace(oName, True) ' True to replace all occurrences
            Exit For
        End If
    Next
End Sub

[Wind_Talker · Accepted solution]

Hi Mark,

Once again thank you for your help and support.

There are couple of problems with your code and it is not working straight away for me. But i have fixed those problems to suit my need. For your knowledge and benefit of anyone else, please see my code below. Hope it will make it clear.

Dim FN As String = "ISO 4014 – M12 x 210ISO.ipt"
Dim fullfilelist As New ArrayList
Dim SearchLocation As String = "\\BG-DOC1\am\Vault,D-Comtec\CONTENT CENTRE FILES\en-US\ISO 4014(2)\"
Dim File As String
Dim Files As String()
    Files = System.IO.Directory.GetFiles(SearchLocation, "*.ipt")
For Each File In Files
    Dim FPN As Integer = InStrRev(File, "\", -1)
    File = Mid(File, FPN + 1)
    FullFileList.Add(File)
Next

Dim tText As String
Dim oItem As String
For Each oItem In FullFileList
    tText = oItem & vbLf & tText
Next
MsgBox(tText)

Dim sName As String
    Dim RName As String = Mid(FN, 13)
    RNC = InStrRev(RName, "I")
    sName = Left(RName, RNC - 1)

Dim FNP As Integer = InStrRev(UCase(sName), "X")
Dim sDia As Integer = Val(Left(sName, FNP - 2)) 
Dim sLen As Integer = Val(Mid(sName, FNP + 2)) 
Dim FilesBigger As New List(Of String)
Dim FilesSmaller As New List(Of String)
Dim BoltLengthSmaller As New List(Of Integer)
Dim BoltLengthBigger As New List(Of Integer)
For Each oItem In FullFileList
    Dim fnVals As String
        Dim fVals As String = Mid(oItem, 13)
        FVP = InStrRev(fVals, "I")
        fnVals = Left(fVals, FVP - 1)
    FNP = InStrRev(UCase(fnVals), "X")
    Dim oDia As Integer = Val(Left(fnVals, FNP - 2))
    Dim oLen As Integer = Val(Mid(fnVals, FNP + 2))
    If sDia = oDia
        If sLen > oLen Then
            FilesSmaller.Add(oItem)
              BoltLengthSmaller.Add(oLen)
        ElseIf sLen < oLen
            FilesBigger.Add(oItem)
            BoltLengthBigger.Add(oLen)
        End If
    End If
Next

BoltLengthSmaller.Sort()
BoltLengthSmaller.Reverse
BoltLengthBigger.Sort()
BoltLengthBigger.Reverse
FilesSmaller.Sort()
FilesBigger.Sort()

Dim FileNameSmaller As String
If FilesSmaller.count >= 1 Then
    For i As Integer = FilesSmaller.Count -1 To 0 Step -1
        Dim FilesSmallerItem = FilesSmaller(i)
        If FilesSmallerItem.Length > 27 Then
            Dim indexS As Integer = FilesSmaller.IndexOf(FilesSmallerItem)
            FilesSmaller.RemoveAt(indexS)
        End If
    Next
    
    Dim BoltLengthMax As Integer = Integer.MinValue
    For Each element As Integer In BoltLengthSmaller
        BoltLengthMax = Math.Max(BoltLengthMax, element)
    Next
    
    Dim BoltLengthMaxStr As String = CStr(BoltLengthMax)
    For Each Str As String In FilesSmaller
        If Str.Contains(BoltLengthMaxStr) Then
            FileNameSmaller = Str
        End If
    Next
End If

Dim FileNameBigger As String
If FilesBigger.count >= 1 Then
    For j As Integer = FilesBigger.Count -1 To 0 Step -1
        Dim FilesBiggerItem = FilesBigger(j)
        If FilesBiggerItem.Length > 27 Then
            Dim indexB As Integer = FilesBigger.IndexOf(FilesBiggerItem)
            FilesBigger.RemoveAt(indexB)
        End If
    Next
    
    Dim BoltLengthMin As Integer = Integer.MaxValue
    For Each element As Integer In BoltLengthBigger
        BoltLengthMin = Math.Min(BoltLengthMin, element)
    Next
    
    Dim BoltLengthMinStr As String = CStr(BoltLengthMin)
    For Each Str As String In FilesBigger
        If Str.Contains(BoltLengthMinStr) Then
            FileNameBigger = Str
        End If
    Next
End If

If FilesSmaller.count >= 1 Then
    Q1 = MessageBox.Show("Do you want to replace this:" & vbLf & FN & vbLf & vbLf & _
    "With this:" & vbLf & FileNameSmaller, _
    "MessageBox Title", MessageBoxButtons.YesNo)
    If Q1 = vbYes Then
        Component.Replace("BR BOLT 1", SearchLocation & FileNameSmaller, False)
        Component.Replace("BR BOLT 2", SearchLocation & FileNameSmaller, False)
        Component.Replace("BR BOLT 3", SearchLocation & FileNameSmaller, False)
    Else If Q1 = vbNo Then
    End If
End If

If FilesBigger.count >= 1 Then
    Q2 = MessageBox.Show("Do you want to replace this:" & vbLf & FN & vbLf & vbLf & _
    "With this:" & vbLf & FileNameBigger, _
    "MessageBox Title", MessageBoxButtons.YesNo)
    If Q2 = vbYes Then
        Component.Replace("BR BOLT 1", SearchLocation & FileNameBigger, False)
        Component.Replace("BR BOLT 2", SearchLocation & FileNameBigger, False)
        Component.Replace("BR BOLT 3", SearchLocation & FileNameBigger, False)
    Else
        MessageBox.Show("No Bolt replaced", "MessageBox Title", MessageBoxButtons.OK,MessageBoxIcon.Error)
    End If
End If

If Q1 = vbNo Or Q2 = vbNo Then
    MessageBox.Show("No Bolt replaced", "MessageBox Title", MessageBoxButtons.OK,MessageBoxIcon.Error)
End If