Code to find a specific folder?

[Ed]

I need to open a document via code. The problem is the folder the document
is in will be updated and the folder name changed to reflect that. The
folder name will always be in the format "C:\Folder dd mmm yyyy", with the
date being the day of the last update. How would I set up code to find this
folder, get its name, and store it as a string to use to open my document?

Thank you.
Ed

 

[Jonathan West]

Hi Ed,

You can create a date string for the folder like this

strFolderDate = "C:\Folder " & Format(Date, "dd mmm yyyy")

You can check whether the folder already exists already using the the
following function

Function FolderExists(strFolder as String) As Boolean
FolderExists = Len(Dir(strFolder, vbDirectory)) > 0
End Function

 

[Jay Freedman]

Hi, Ed,

If you know that the folder is always directly under C:\ and that there's
only one folder there that starts with "Folder", then you can adapt this
code:

Dim FolderName As String

FolderName = Dir$("C:\Folder *", vbDirectory)
If FolderName <> "" Then
MsgBox "C:\" & FolderName
Else
MsgBox "Not found"
End If

 

[Ed]

Thanks, Jay. It worked great! You brought up something, though - I run a
series of macros to update things, one of which creates this folder with the
date of the updates. I may not get around to deleting the previous folder
until the next day or two.

The question then would be how to get the list of all matches and select
just the one you want. I've been wondering about how to do this with other
things, too - VBA has to be writing all the matches to a buffer or
something, then listing them out to the message box. Can you access this
buffer? Or maybe return a count of items that matched, then select item
number whatever? If it's the last one you did, and VBA counts 3 total, then
"get item3"? I guess like you would select tables or such? But can you do
that with these kinds of matches? (Folders, instances of a certain style,
section breaks, or about anything else you can count with code?)

Thank you again for all your help.
Ed

 

[Jay Freedman]

Hi, Ed,

I can show you how to get close to what you want. In fact, I'll show you two
different ways to do it. The last little bit is a problem, though. There's
no guarantee that the code will retrieve the folder names in the order they
were created, or in any sorted order. And if you do sort them
alphanumerically, you'll still get them out of date order -- for example,
"Folder 01 Dec 2003" will sort *before* "Folder 23 Nov 2003". You'd have to
put in a lot of effort to sort by date order and figure out which one is the
last.

Anyway, play with the following two macros. The first one is an extension of
the one I showed before. The "trick" is that after calling Dir$() with a
pattern, you can call it again with empty parentheses, and it will return
the next match to the pattern, or "" if there is no match.

Sub foo1()
Dim FolderName As String
Dim FolderList As String
Dim FolderCount As Integer

FolderCount = 0
FolderName = Dir$("C:\Folder *", vbDirectory)
Do While (FolderName <> "")
FolderList = FolderList & FolderName & vbCr
FolderCount = FolderCount + 1
FolderName = Dir$()
Loop

If FolderCount = 0 Then
MsgBox "Not found"
ElseIf FolderCount = 1 Then
FolderName = FolderList
MsgBox "only one: " & FolderName
Else
MsgBox "Delete unused folders from this list:" _
& vbCr & FolderList
End If
End Sub

For the second one, you should first go to the Tools > References dialog in
the VBA editor and put a check next to "Microsoft Scripting Runtime". That
makes available the FileScriptingObject, which has more capabilities than
Dir$() and is usually faster.

Sub foo2()
Dim FolderName As String
Dim oFSO As FileSystemObject
Dim oFldr As Folder, oSubFldr As Folder
Dim SFlist As String, SFcount As Integer

Set oFSO = New FileSystemObject
Set oFldr = oFSO.GetFolder("C:\")
If Not oFldr Is Nothing Then
SFcount = 0
For Each oSubFldr In oFldr.SubFolders
If oSubFldr.Name Like "Folder *" Then
SFlist = SFlist & oSubFldr.Name & vbCr
SFcount = SFcount + 1
End If
Next oSubFldr

If Right(SFlist, 1) = vbCr Then
SFlist = Left(SFlist, Len(SFlist) - 1)
End If

If SFcount = 0 Then
MsgBox "Not found"
ElseIf SFcount = 1 Then
FolderName = SFlist
MsgBox "only one: " & FolderName
Else
MsgBox "Delete unused folders from this list:" _
& vbCr & SFlist
End If
End If
End Sub

 

[Ed]

Thank you, Jay. You've helped me greatly.

Ed

Hi, Ed,

I can show you how to get close to what you want. In fact, I'll show you two
different ways to do it. The last little bit is a problem, though. There's
no guarantee that the code will retrieve the folder names in the order they
were created, or in any sorted order. And if you do sort them
alphanumerically, you'll still get them out of date order -- for example,
"Folder 01 Dec 2003" will sort *before* "Folder 23 Nov 2003". You'd have to
put in a lot of effort to sort by date order and figure out which one is the
last.

Anyway, play with the following two macros. The first one is an extension of
the one I showed before. The "trick" is that after calling Dir$() with a
pattern, you can call it again with empty parentheses, and it will return
the next match to the pattern, or "" if there is no match.

Sub foo1()
Dim FolderName As String
Dim FolderList As String
Dim FolderCount As Integer

FolderCount = 0
FolderName = Dir$("C:\Folder *", vbDirectory)
Do While (FolderName <> "")
FolderList = FolderList & FolderName & vbCr
FolderCount = FolderCount + 1
FolderName = Dir$()
Loop

If FolderCount = 0 Then
MsgBox "Not found"
ElseIf FolderCount = 1 Then
FolderName = FolderList
MsgBox "only one: " & FolderName
Else
MsgBox "Delete unused folders from this list:" _
& vbCr & FolderList
End If
End Sub

For the second one, you should first go to the Tools > References dialog in
the VBA editor and put a check next to "Microsoft Scripting Runtime". That
makes available the FileScriptingObject, which has more capabilities than
Dir$() and is usually faster.

Sub foo2()
Dim FolderName As String
Dim oFSO As FileSystemObject
Dim oFldr As Folder, oSubFldr As Folder
Dim SFlist As String, SFcount As Integer

Set oFSO = New FileSystemObject
Set oFldr = oFSO.GetFolder("C:\")
If Not oFldr Is Nothing Then
SFcount = 0
For Each oSubFldr In oFldr.SubFolders
If oSubFldr.Name Like "Folder *" Then
SFlist = SFlist & oSubFldr.Name & vbCr
SFcount = SFcount + 1
End If
Next oSubFldr

If Right(SFlist, 1) = vbCr Then
SFlist = Left(SFlist, Len(SFlist) - 1)
End If

If SFcount = 0 Then
MsgBox "Not found"
ElseIf SFcount = 1 Then
FolderName = SFlist
MsgBox "only one: " & FolderName
Else
MsgBox "Delete unused folders from this list:" _
& vbCr & SFlist
End If
End If
End Sub

 

[liddlem]

Hi Ed
One way to ensure that filename are sorted correctly would be to chang
the naming structure.

I.E.
MyFileName = "FileName_" & Year(now() & Month(Now()) & Day(Now())

Folders should then be sorted sequentially as

FileName_20030905
FileName_20030906
FileName_20030907
FileName_20031005
FileName_20031006

If you are creating more than one file per day, then include the Hou
and the Minute in the filename