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

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

[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




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