Dynamic file location macro

[Frank Pytel]

Hello;

I posted this in programming, but realize that it was in a relatively old
post so I am reposting. Sorry for any inconvenience.

Gary's Student posted the following macro for reading file names from a
folder.

-------------------------------------

Sub list_um()
Dim F As String
Dim roww As Long
roww = 0
Dim FileLocSpec As String
FileLocSpec = "C:\Temp\*.*"
F = Dir(FileLocSpec)
Do Until F = ""
roww = roww + 1
Cells(roww, 1).Value = F
F = Dir
Loop
End Sub

----------------------------------------------

We print alot of individual CAD drawings and it has been useful in knowing
what prints are completed.

I would like to know if anyone knows of a way to make this more dynamic. I
print to a single folder and move the pdf's to the appropriate job folder.

I would like to paste the path to the job folder I am currently working on
into a cell and have the macro look to the cell to find the folder. Or even
better, some sort of concatenation in the cell based on a selection menu. The
job folders are very consistent (5 digit number, space, open parin, job name,
close parin) with the job name being the only variable data. I can choose
that from a drop down list as the path is always

C:\Documents and Settings\Frank Pytel\My Documents\Jobs\*

* being the actual job folder. I can easily creat a list of these within a
named range.

Am I rambling. Is there any body out there? I really appreciate any help
that you may be able and willing to offer. Thanks in advance.

Have a Blessed Day.

Frank Pytel

 

[Joel]

The code below will open a dialog box to select the Folder
Sub list_um()

Dim oApp As Object
Dim oFolder
Dim roww As Long

Set oApp = CreateObject("Shell.Application")
DefaultFolder = "c:\temp"
'Browse to the folder
Set FileLocSpec = oApp.BrowseForFolder(0, "Select folder", 512,
DefaultFolder)
If Not FileLocSpec Is Nothing Then


FName = Dir(DefaultFolder & "\" & FileLocSpec & "\*.*")
Do Until FName = ""
roww = roww + 1
Cells(roww, 1).Value = FName
FName = Dir
Loop


Else
MsgBox "You did not select a folder"
End If

End Sub

 

[Gary''s Student]

Hi Frank:

Say we would like something more general than:

FileLocSpec = "C:\Temp\*.*"

If we put the path in B9 (say "C:\Frank\" ) then:

FileLocSpec = Range("B9").Value & "*.*"

If we want the path to be the path in which the macro "resides" then:

FileLocSpec =ThisWorkbook.Path & "\*.*"

just be sure a "\" gets put between the path and the filespec.