Excel gurus pleeeeeeese

[Rosco]

OK blokes/shielas ... a little help please.

I have a folder in my docs called "dwgs"

This folder contains all of my cad drawing files e.g.

8133-1, 8133-1a, 8133-1b, 8133-2, 8133-3, 8133-3a and so on, where the letter at the end represents a revised dwg, hence in the list above, sheet 1 has revision b, sheet 2 has no revisions and sheet 3 has revision a.

What I would like to be able to do is open a spreadsheet, enter the relevant job number (in this instance 8133) and have Excel look up the folder and create a list of the latest issue of the drawings.

So in the above example I would enter 8133 and it would return ... 8133-1b, 8133-2, 8133-3a (with or without the commas, but spaces please, or maybe in individual cells of a column.

There could be anywhere from 1 to say 30 drawings in a set.

Is this possible ???

Ta in advance :)

P.S. All files have a ".dwg" file extension

[dmdigital]

Yes it can be done with a VBA macro. I can't do it from home as I don't run Excel anymore at home.

[Rosco]

Yes it can be done with a VBA macro. I can't do it from home as I don't run Excel anymore at home.


Thanks mate ................................. what's a VBA :(:D

[dmdigital]

VBA = Visual Basic for Applications

It's the language the macro's are programmed in with Excel. What you can do is build a small macro and form that will tell Excel the drawing number and starting cell of where you want the details to store and then it would run, do a directory search and list the drawings found in the cells starting at the one specified. I've done this a number of times for various reasons. But it sounds like you need a little help to build/run a macro.

[C0L0N3L]

here is another option for you if you just want to know how many revisions in a folder.. ie not add them to your spread sheet

this is the code for a .bat file its a simple windows console.

@echo off
SET Choice=
SET /P p=Folder Name:
tree /F %p%
pause

if you open up notepad copy that in.

Then go file save as.. "revision finder" as an example

AND instead of "revision finder.txt" you replace the "txt" with "bat" so you save your file as a bat file which is executable. so it will look like this "revision finder.bat"

the location you save it too should be to the root directory of all your cad dwg's so for example i have some in D:\Work\CAD\ i will save it there. with the subfolders


then you can make a shortcut to your revision finder if you want.. run it.. type the name of the folder and it will return the file names in that folder.

if you want i can make the file for you and upload it..

finally stuffing around in command prompt has paid off :P

[steveG]

Visual Basic for Applications.
Its a cut down version of the Visual Basic programming language that is used for automating things and doing fancy stuff within Word, Excel etc.

I used to do a bit a few years back but very rusty and try and stay away from it nowdays.

Hopefully someone else that's doing it regularly can help out..

Steve

[Rosco]

VBA = Visual Basic for Applications

.................................................. .................................................. But it sounds like you need a little help to build/run a macro.


Pity there's no emoticon for ... NOW THAT'S AN UNDERSTATEMENT !! :D

[Rosco]

here is another option for you if you just want to know how many revisions in a folder.. ie not add them to your spread sheet

> ... It would need to return a list of the latest files i.e. the file with the last letter, or the file with no letter (if there were no revisions)

I'm totally in the dark here .. :(

[C0L0N3L]

> ... It would need to return a list of the latest files i.e. the file with the last letter, or the file with no letter (if there were no revisions)

I'm totally in the dark here .. :(


this is an example of what it does.. i could probably get it to export to a text file if needed

https://www.aulro.com/afvb/images/imported/2009/06/760.jpg


reread your op. I assumed they where different projects and each had a different folder.. this way would still be pretty good i think if you just put each sheet in a new folder..

[Dorko]

Mate,

I've done a similar thing for a client. Copy this in VBA. Alt+F11
Create a Module
Then paste this

Sub SeachDwgs()

Dim fs As FileSearch
Dim Lookup As String

On Error GoTo Crash

Lookup = InputBox("What is the Drawing Number?", "Search", "OPP")

Set fs = Application.FileSearch
fs.NewSearch
With fs

With .PropertyTests
.Add _
Name:="Text or Property", _
Condition:=msoConditionIncludesPhrase, _
Value:=Lookup

End With
.LookIn = "C:\Test"
.SearchSubFolders = False
.Filename = "*"
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
End With
If fs.Execute() > 0 Then
MsgBox "There were " & _
fs.FoundFiles.Count & _
" file(s) found."
For i = 1 To fs.FoundFiles.Count
MsgBox fs.FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If

Crash:

End Sub

I would then create this as a Addin so all you have to do is have excel open.

Dorko

[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.

[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 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.

[DiscoStew]

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.

[Rosco]

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 :thumbsup:

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

[DiscoStew]

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

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.

[Rosco]

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 :D

[DiscoStew]

Don't worry I'll only do what I feel like but I can't pack the car at one in the morning, that's why God invented computers.:cool: