Question for very advanced users/VBA Programmers
I have a CD full of engineering drawings which are stored each in a separate
graphic file and titled each by the name/ID of each drawing.
I need to develop a database to compare which drawings I have in electronic
form and which are on hard copy.
Is it possible somehow to do something - I guess in in VBA - to have Excel
automatically run through the list of all these electronically stored
drawings, copy the name of each of these files, and paste this name into a
database or a list?
Would much appreciate any help on thism, or even an indication of whether
this is possible to accomplish in any MS program.

Nat,

Copy the code below into a code module of an otherwise blank workbook.

Change the drive letter in the .LookIn line, and run it.

HTH,
Bernie
MS Excel MVP

Sub FindFilesAndListInfo()
Dim i As Long

Cells(1, 1).Value = "File Path"
Cells(1, 2).Value = "File Date-Time"
Cells(1, 3).Value = "File Size"

With Application.FileSearch
.NewSearch
.LookIn = "D:\"
.SearchSubFolders = True
.FileType = msoFileTypeAllFiles
If .Execute() 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
Cells(i + 1, 1).Value = .FoundFiles(i)
Cells(i + 1, 2).Value = FileDateTime(.FoundFiles(i))
Cells(i + 1, 3).Value = FileLen(.FoundFiles(i))
Next i
Else
MsgBox "There were no files found."
End If
End With

Columns("A:C").AutoFit
End Sub




"Nat Maxwell" <Nat wrote in message
...
Question for very advanced users/VBA Programmers
I have a CD full of engineering drawings which are stored each in a separate
graphic file and titled each by the name/ID of each drawing.
I need to develop a database to compare which drawings I have in electronic
form and which are on hard copy.
Is it possible somehow to do something - I guess in in VBA - to have Excel
automatically run through the list of all these electronically stored
drawings, copy the name of each of these files, and paste this name into a
database or a list?
Would much appreciate any help on thism, or even an indication of whether
this is possible to accomplish in any MS program.

Bernie,

YOU ARE AWESOME!!!!!
It did *exactly* what I was looking for!
Thank you thank you thank you, and may a million stars light your path!

Sincerely,
Nat Maxwell

"Bernie Deitrick" wrote:

Nat,

Copy the code below into a code module of an otherwise blank workbook.

Change the drive letter in the .LookIn line, and run it.

HTH,
Bernie
MS Excel MVP

Sub FindFilesAndListInfo()
Dim i As Long

Cells(1, 1).Value = "File Path"
Cells(1, 2).Value = "File Date-Time"
Cells(1, 3).Value = "File Size"

With Application.FileSearch
.NewSearch
.LookIn = "D:\"
.SearchSubFolders = True
.FileType = msoFileTypeAllFiles
If .Execute() 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
Cells(i + 1, 1).Value = .FoundFiles(i)
Cells(i + 1, 2).Value = FileDateTime(.FoundFiles(i))
Cells(i + 1, 3).Value = FileLen(.FoundFiles(i))
Next i
Else
MsgBox "There were no files found."
End If
End With

Columns("A:C").AutoFit
End Sub




"Nat Maxwell" <Nat wrote in message
...
Question for very advanced users/VBA Programmers
I have a CD full of engineering drawings which are stored each in a separate
graphic file and titled each by the name/ID of each drawing.
I need to develop a database to compare which drawings I have in electronic
form and which are on hard copy.
Is it possible somehow to do something - I guess in in VBA - to have Excel
automatically run through the list of all these electronically stored
drawings, copy the name of each of these files, and paste this name into a
database or a list?
Would much appreciate any help on thism, or even an indication of whether
this is possible to accomplish in any MS program.

Nat,

Now if we could only do that for the paper copies.....

;-)

Bernie

YOU ARE AWESOME!!!!!
It did *exactly* what I was looking for!
Thank you thank you thank you, and may a million stars light your path!

singing, "If you you believed, if only you believed in miracles, So would I..."
;-)

"Bernie Deitrick" wrote:

Nat,

Now if we could only do that for the paper copies.....

;-)

Bernie

YOU ARE AWESOME!!!!!
It did *exactly* what I was looking for!
Thank you thank you thank you, and may a million stars light your path!