Thread: Excel gurus pleeeeeeese

I get to play around with drawings and from my experience your assumptions are correct except for the sheet numbers, I think it could be possible to have sheets numbered 10, 11 etc and if you had to revise the drawings such that you ran out of letters in the alphabet then you have a pretty unstable design. The revision letters assumption will not do any harm but you might run into more than just 9 sheets.

Originally Posted by DiscoStew

]I am not positive but I don't think FileSearch works in 2007. It doesn't for me.

Will the sheet number ever be more than 9? eg 8133-10

Will the version character ever be more than z? eg 8133-1aa or 8133-1a1

Will the sheet number always start at 1 and go up sequentially?

I have attached a solution assuming the answer to the first two questions is no and the third yes.


You don't say how you would like to enter the drawing number so I have you typing it into a cell in the first row. I can modify to prompt you.

You can add as many drawing numbers as you want. I have formatted to allow 11 but there is no limit. The program will just work across from left to right until it finds an empty cell in the first row.

It will then find the latest versions as per your original request and list them under their drawing number. They are hyperlinked to the file but I don't know if that will work for .dwg files, it depends on your settings under FileTypes.

The "my documents/dwgs" is hard-coded but easily modified.

If you want this embedded in an existing spreadsheet then PM me.

I don't know how to do the add-in thing that was mentioned but perhaps someone can explain that.


You might have to enable macros. If you get an error message when you hit the button, check to see if a line has appeared at the top with an options button. Click that and allow macros. I promise I'm not a hacker, I'm not that smart

Have a play and let me know if you want some changes.

Originally Posted by slug_burner

I get to play around with drawings and from my experience your assumptions are correct except for the sheet numbers, I think it could be possible to have sheets numbered 10, 11 etc and if you had to revise the drawings such that you ran out of letters in the alphabet then you have a pretty unstable design. The revision letters assumption will not do any harm but you might run into more than just 9 sheets.

I thought as much but with the naming standard being used it is a bit of a pain to extract the sheet number out. It can be done by parsing the filename looking for a change from numeric to alpha but I couldn't be bothered tonight.

If what I have written is close to what Rosco wanted then I will look at the more complex change to go beyond 9.

Originally Posted by DiscoStew

I thought as much but with the naming standard being used it is a bit of a pain to extract the sheet number out. It can be done by parsing the filename looking for a change from numeric to alpha but I couldn't be bothered tonight.

If what I have written is close to what Rosco wanted then I will look at the more complex change to go beyond 9.

Thanks Paul .... but Slug is correct (>9 possible)
And thanks Slug for your input

PS Paul, I'll have a look over the weekend and get back to you ... thanks again.

Originally Posted by Rosco

Thanks Paul .... but Slug is correct (>9 possible)
And thanks Slug for your input

PS Paul, I'll have a look over the weekend and get back to you ... thanks again.

Actually it was quite easy once I thought about it to extract a variable length numeric as the sheet number and an optional single character as the version letter.

You can also skip sheet numbers now eg 1,2,4,7,10,11.

BTW the drawing number can be any length but don't use leading zeroes anywhere. eg 08133-1a.dwg would be very bad as would 8133-01a.dwg

Version 2 attached. I am heading off to the cape next week so better off sending feedback or requests early in the weekend so I can fit in during breaks from working on the car/trailer.

Of course the big problem with automating this type of thing is that I have followed your requirements, which means you will be locked into those requirements I have put some english in with the code so you can probably work out minor changes yourself eg changing the location of the folder, changing the file extension, size of the results area. Alt_F11 will bring up the editor, the code is in module1. I may have accidentally left in some test code. You only need the first SUB routine and the subsequent FUNCTION. Will make sense when you see it. Ignore anything else.

Originally Posted by DiscoStew

Actually it was quite easy once I thought about it to extract a variable length numeric as the sheet number and an optional single character as the version letter.

You can also skip sheet numbers now eg 1,2,4,7,10,11.

BTW the drawing number can be any length but don't use leading zeroes anywhere. eg 08133-1a.dwg would be very bad as would 8133-01a.dwg

Version 2 attached. I am heading off to the cape next week so better off sending feedback or requests early in the weekend so I can fit in during breaks from working on the car/trailer.

Of course the big problem with automating this type of thing is that I have followed your requirements, which means you will be locked into those requirements I have put some english in with the code so you can probably work out minor changes yourself eg changing the location of the folder, changing the file extension, size of the results area. Alt_F11 will bring up the editor, the code is in module1. I may have accidentally left in some test code. You only need the first SUB routine and the subsequent FUNCTION. Will make sense when you see it. Ignore anything else.

Thanks mate ... but don't bust yer boiler .. concentrate on your upcoming great adventure.

It can wait