Find Next Part Number using Vba

[Anonymous]

This one should be easy for all the vba gurus out there. What I'm looking to do is find the next available part number in a certain job folder. the folder structure is as follows:

T:\Jobs\Jobs JA-JZ\JF8\PARTS

When the designers detail out parts, (primarily in AutoCAD, not quite using 3D but that's the direction we are headed..) they need to know the next available number in the Parts folder so they can fill in their title block. As we transition to 3D, we need the same functionality but for saving part files. I have a userform set up to receive the next number but I have no idea as how to gather all the current part numbers into a collection, sort them from lowest part number to highest (ex: the first PN a designer uses above is JF8x101, where the "x" could be "A", "B", "C", etc for the sheet size its drawn on), then exclude any parts that don't begin with the job number string "JF8" (We sometimes use reference parts from other jobs that shouldn't be included). That's the pseudocode. The implementation is beyond my skills at this point.

And one other point - there's times when part numbers are intentionally skipped. A designer may skip using part numbers JF8x150-153 to allow for a future weldment. The program would have to find the next number as JF8x150, not JF8x154. If that makes sense.

Any programs or helps are always appreciated.

Here's what the userform looks like.(see attached)

[rikard.nilsson]

Hi,

I hope I understod you right,, But here is a code that find the gaps and also fins the next free number after all files has been checked..

It's written in iLogic but I will not be any problem for you to convert the code to VBA..

/Rikard

Dim Path As String = "D:\support\JF8\"

Dim files As String() = System.IO.Directory.GetFiles(Path, "JF8*.dwg")

Dim listOfFileNumbers as New List(Of Integer)
Dim justFileName As String
For Each item in files
justFileName = System.IO.Path.GetFileNameWithoutExtension(item)
listOfFileNumbers.add(CInt(Right(justFileName, justFileName.length-4)))
Next

listOfFileNumbers.Sort()

Dim listOfFileNumbersThatNotIsUsed As New List(Of Integer)
For i= 0 To listOfFileNumbers.count-2
    If listOfFileNumbers(i)+1 <> listOfFileNumbers(i+1) Then
        For ii= listOfFileNumbers(i)+1 To listOfFileNumbers(i+1)-1
            ' Add the gaps..
            listOfFileNumbersThatNotIsUsed.add(ii)
        Next
    End If
Next
' Add one last number
listOfFileNumbersThatNotIsUsed.add(listOfFileNumbers(listOfFileNumbers.count-1)+1)


For Each item in listOfFileNumbersThatNotIsUsed
    MessageBox.Show(item, "Title")
Next

[Anonymous]

Rikard,

I'm not sure how to convert from ilogic to straight VB code. Can you help with this? and the code as given produces an error (see attached).

Thanks

Pat

[rikard.nilsson]

Hi,

I haven't done a VBA code for at least 15 years so I'm not the right person for that.
If you google how to change code from vba to vb.net then you should be able to do it from vb.net to vba.
But I recommend to do addins instead of vba.

Can you please send a picture of your files in the folder you check?

In my folder I had placed file with different letters after JFB. And then with numbers
101, 102, 103, 107, 108, 109.
And that did not give any errors.

/Rikard

[Anonymous]

see attached.

When the detailers make the manufactured part drawings, they divide them into subfolders under the main job folder. I have shown what the "Shafts" folder structure looks like but some of the other folders ("Frames" for example) have multiple levels of subfolders under them. Not sure if that affects the code or not.

[rikard.nilsson]

Hi,

Maybe not that easy as you wrote in your first post..  🙂

It is still quite hard to fololow what you want..

Do like this..

Create a zip-file with an example structure.

Then try to explain with text what you want to happen with your files in your zip.. 

I think it will be a lot easier if we have an example to try with and have file with the right naming.

/Rikard