Solved: Bringing a list of available templates

Hi,

I've got various folders that belong to various locations that contain Word documents and templates that are used to create other documents.

I'm trying to make all these documents available on a userform that can easily be picked up and a new document be created. I've thought of various techniques to bringing this about, however, I fail to think of a logic to display these files.

I tried one of Word's examples of FileSearch, but the example itself doesn't seem to work.

The example I have tried to use is the following:

[VBA] Sub trial1()
With Application.FileSearch
.FileName = "*.doc"
.LookIn = "C:\Templates\Buffalo"
.Execute
For I = 1 To .FoundFiles.Count
MsgBox .FoundFiles(I)
Next I
End With
End Sub[/VBA]

The problem with this code is that the LookIn variable doesn't change even though it has been set by the example to the one mentioned. I've tried using an alternate variable, but it still uses the system default. Is there a workaround for this problem.

I am using Office 97 with Win 2000.

Any help is appreciated.

Thanks.:think:

Hi,

Tried putting a Word doc in this default folder and the file is found. So there is no problem with the FoundFiles option. The problem lies in changing the default search location. This needs to be changed.

If you want users to have an interface that looks like Windoze Explorer, then use the GetOpenFileName API.

If you want to present users with a list of all, or just particular, templates, then build up a list within your code and present the list in a ListBox control.

I would go with Howard's last solution, create an array in your userform containing the full path/names and you can use this array either to populate a listbox or series of option buttons, or use "friendly" names if preferred.
A file find routine would only be of benefit if the list of files was continuously changing.
MD

Quote:

---

Originally Posted by Howard Kaikow

If you want users to have an interface that looks like Windoze Explorer, then use the GetOpenFileName API.

If you want to present users with a list of all, or just particular, templates, then build up a list within your code and present the list in a ListBox control.

---

Actually, there is a third, more complex solution, that is better than the list building solution.

In general, an interface that looks like windoze explorer is best but, and I do have a big butt, the problem is that users don't know where to navigate.

An alternative is to present two lists.

The first list is a list of the directories in which the templates live.

The second list is a list of the templates in the directory selected in the first list.

Even better, instead of the 2nd list, just present a GetOpenFileName interface for the directory selected in the first list.

In the last case, all the code has to do is maintain a list of the direcories in which the templates live, so it is really the same as the first solution, but offers help in finding the relevant/selected directories.

A flat list of templates is really not a very good interface.

Quote:

---

Originally Posted by mdmackillop

I would go with Howard's last solution, create an array in your userform containing the full path/names and you can use this array either to populate a listbox or series of option buttons, or use "friendly" names if preferred.
A file find routine would only be of benefit if the list of files was continuously changing.
MD

---

The windoze explorer interface is a better interface as it does not RESTRICT the user to choosing only from the listed templates.

Quote:

---

I've got various folders that belong to various locations that contain Word documents and templates that are used to create other documents.

---

Windoze exploer is OK if the end user knows where eveything is and how the folders are organised. What I would set up for myself, or for distribution would not necessarily be the same.

Quote:

---

Originally Posted by mdmackillop

Windoze exploer is OK if the end user knows where eveything is and how the folders are organised. What I would set up for myself, or for distribution would not necessarily be the same.

---

i meant that windoze explorer is the better interface if used in conjuction with the list of directories in my 3rd solution.

this gives the user an opportunity to go elsewhere if the list of directories is not complete.

for example, in the past, i've had code that required the use of a .ini, file.

when installing, i gave the user the choice of where to locate the file.

when executing the installed code, the code looked at specific known locations. if the .ini file was not found, then a windoze explorer interface was presented to allow the user to identify the file.

Hi,

this is what i've got after i had a sudden flash of how to get this. now the templates are in a common folder and the common folder has further subfolder. this can be handled.

the code i gave previously was not working completely becasue i had put the path wrong (duh of me to not realize it before). however, this is how i've gone about it.

[VBA] Sub trial1()
With Application.FileSearch
.NewSearch
.FileType = msoFileTypeAllFiles
.LookIn = "C:\Lincoln\Projects\ZNA Templates (Buffalo)"
.Execute
Set popupbar1 = CommandBars.Add("Template Toolbar", msoBarFloating)
With popupbar1 ' }
.Width = 446 ' }This section needs some input
.Top = 200 ' }
.Left = 200 ' }
End With
If .FoundFiles.Count > 0 Then
For i = 1 To .FoundFiles.Count
FName = Mid(.FoundFiles(i), 45)
Set newButton = popupbar1.Controls.Add(Type:=msoControlButton, ID:=1)
With newButton
.Caption = FName
.Style = msoButtonCaption
.OnAction = "DeleteToolbar"
End With
Next i
popupbar1.Visible = True
Else
MsgBox "There are no files in this location", vbCritical
End If
End With
End Sub[/VBA]

This works superbly, just the way i wanted it. now my problem is this. i want the popup to be a box instead of a straight line. i've seen this can be done and i have a macro that is written for the IDBrowse.dot file, what i can't figure is how this size and shape of the popup can be amended for my usage. any input is appreciated. i've attached the IDBrowse.dot file for your scan and input.:thumb : pray2:

Hi,

This is what the code looks like now. The areas where I need input are marked with the comments:

[VBA] Public FileToOpen, NameOfFile(50) As String
'Public NewButton As Control Need help here on
'how to initialize a commandbutton and commandbar
'Public PopupBar1 As ???????????
Sub trial1()
j = 0
With Application.FileSearch
.NewSearch
.FileType = msoFileTypeAllFiles
.LookIn = "C:\Lincoln\Projects\ZNA Templates (Buffalo)"
.Execute
Set PopupBar1 = CommandBars.Add("Template Toolbar", msoBarFloating)
If .FoundFiles.Count > 0 Then
For i = 1 To .FoundFiles.Count
FileToOpen = .FoundFiles(i)
FName = Left(" | " & Trim(Str(i)) & " " & _
Trim(Mid(.FoundFiles(i), 45)), 30)
Set NewButton = PopupBar1.Controls.Add(Type:=msoControlButton)
With NewButton
.Caption = FName
.Style = msoButtonCaption
.OnAction = "DeleteToolbar"
End With
NameOfFile(j) = .FoundFiles(i)
Next i
With PopupBar1
.Width = 5
.Height = 290
End With
PopupBar1.Visible = True
Else
MsgBox "There are no files in this location", vbCritical
End If
End With
End Sub
Sub DeleteToolbar()
CommandBars("Template Toolbar").Delete
' This part works in the sense that it opens up the
' last file in the collection. I want the button
' selected file to open.
Documents.Open NameOfFile(j)
UserForm2.Show
End Sub
[/VBA]

As you can see, I've worked my brain over this, but since I'm not that good with VB as yet, I am finding it rather difficult in even understanding the help file. I've learned VB only through trial and error, and errors are more that I've encountered with average learning. Please help.:banghead:

Quote:

---

Originally Posted by lynnnow

Hi,

This is what the code looks like now. The areas where I need input are marked with the comments:

[VBA] Public FileToOpen, NameOfFile(50) As String
'Public NewButton As Control Need help here on how to initialize a commandbutton and commandbar
'Public PopupBar1 As ???????????
Sub trial1()
j = 0
With Application.FileSearch
.NewSearch
.FileType = msoFileTypeAllFiles
.LookIn = "C:\Lincoln\Projects\ZNA Templates (Buffalo)"
.Execute
Set PopupBar1 = CommandBars.Add("Template Toolbar", msoBarFloating)
If .FoundFiles.Count > 0 Then
For i = 1 To .FoundFiles.Count
FileToOpen = .FoundFiles(i)
FName = Left(" | " & Trim(Str(i)) & " " & Trim(Mid(.FoundFiles(i), 45)), 30)
Set NewButton = PopupBar1.Controls.Add(Type:=msoControlButton)
With NewButton
.Caption = FName
.Style = msoButtonCaption
.OnAction = "DeleteToolbar"
End With
NameOfFile(j) = .FoundFiles(i)
Next i
With PopupBar1
.Width = 5
.Height = 290
End With
PopupBar1.Visible = True
Else
MsgBox "There are no files in this location", vbCritical
End If
End With
End Sub
Sub DeleteToolbar()
CommandBars("Template Toolbar").Delete
' This part works in the sense that it opens up
' the last file in the collection. I want the
' button selected file to open.
Documents.Open NameOfFile(j)
UserForm2.Show
End Sub
[/VBA]

As you can see, I've worked my brain over this, but since I'm not that good with VB as yet, I am finding it rather difficult in even understanding the help file. I've learned VB only through trial and error, and errors are more that I've encountered with average learning. Please help.:banghead:

---

I believe that you find things easier if you first read a book such as Steve Roman's Writing Word Macros. This will give a better overall feel for things.

Also, see http://www.standards.com/index.html?WordVBABooks

Is this what you are trying to do?

Create a UserForm with two List Boxes and one Command Button. ListBox2 will hold the paths and can be hidden. ListBox1 will show the file names for the user to select. Add this code to the UserForm.
[vba]

Option Explicit

Private Sub CommandButton1_Click()

On Error Resume Next
Documents.Open FileName:=Me.ListBox2.List(Me.ListBox1.ListIndex)
On Error GoTo 0
Unload Me

End Sub
[/vba]

Create a Module and add this code.
[vba]
Option Explicit

Sub ListDocuments()

Dim Col As Collection
Dim StartFld As Folder
Dim FSO As FileSystemObject
Dim i As Long
Dim Path As String
Dim Files As Collection
Dim Paths As Collection
Dim FileName As String

'This is the startup folder
'Change this folder to whatever root folder you want
Path = "C:\MyDir"

Set FSO = New FileSystemObject
Set Col = New Collection
Set Files = New Collection
Set Paths = New Collection
Set StartFld = FSO.GetFolder(Path)

'Create a list of folders
GetFolder Col, StartFld

For i = 1 To Col.Count
Path = Col(i)
FileName = Dir(Path & "\*.doc", vbNormal)
Do Until FileName = ""
Files.Add FileName
Paths.Add Path & "\" & FileName
FileName = Dir()
Loop
FileName = Dir(Path & "\*.dot", vbNormal)
Do Until FileName = ""
Files.Add Replace(FileName, Path & "\", "", 1)
Paths.Add FileName
FileName = Dir()
Loop
Next i
Load UserForm1
With UserForm1
For i = 1 To Files.Count
.ListBox1.AddItem Files(i)
.ListBox2.AddItem Paths(i)
Next i
.Show
End With

End Sub

Sub GetFolder(ByRef Col As Collection, ByVal Fld As Folder)

Dim SubF As Folder

Col.Add Fld.Path & IIf(Fld.IsRootFolder, "", Application.PathSeparator)
For Each SubF In Fld.SubFolders
GetFolder Col, SubF
Next SubF
Set SubF = Nothing

End Sub
[/vba]

Set a Reference to Microsoft Scripting Runtime (Tools | References).

Change this:
[vba]
Path = "C:\MyDir"
[/vba]
To the root folder to be searched. All sub folders will be searched for any .doc or .dot files.

Run the macro ListDocuments. From Word select Tools | Macro | Macros... or you can attach this to a toolbar button or some other control.

Refer to the attachment for more information.

Wow Jacob!

That is really nice. I will definitely save your code as a template for myself of how to use the FileSystemObject and Microsoft Scripting Runtime!!

there are a few little bugs in there somewhere, though. I had to take out the Path & "\" because I was getting "C:\myPath\\myFile"

Also, in the first listbox, I get repetitions, like:

FileA
FileB
FileA
FileB

when ideally the listbox should just contain two entries, you know?

nonetheless, I'm sure you just coded this in about 30 seconds and it really is beautiful.

Did you put in the path as "C:\myPath\" with the path seperator in already?

For the duplicate files were there .doc and .dot files with the same names or was there just a FileA.doc that shows up twice?

Hi Lynnnow!

As you probably already noticed, Jacob has written a perfectly viable solution to your quandary.

I can't offer you much more additional help, because a UserForm really is the way to go.

What I can offer is a small explanation of why your code did not achieve your desired result.

Consider this: In the following excerpt from YOUR code, you will assign EVERY BUTTON to the same OnAction macro. This is the start of the problem. It's really not your fault entirely. Hopefully I can make that clear in a minute. But for now, just understand that EVERY button you make will call the DeleteToolbar subroutine.

[VBA]
With NewButton
.Caption = FName
.Style = msoButtonCaption
.OnAction = "DeleteToolbar"
End With
[/VBA]


So then we look at the DeleteToolbar routine. The mistake here is to try and use j at all. You cannot use j here. "j" was part of a separate Sub, and therefore "j" means NOTHING here in DeleteToolbar. Now, ideally, there would be a way to use code such as "CommandBars("Template Toolbar").VALUE" to get the current value (in my opinion the current value would have to be the value of the active or "clicked" button... but this is all hypothetical... )

Here's the real problem: as far as I know, there is NO WAY TO GET THE INFORMATION WE NEED. What we need to know when we are inside the DeleteToolbar routine is the value of the active button. But there is no "CommandBar(x).activebutton" !!!

Now do you see why I said that this isn't entirely your fault? In my opinion it is a tiny oversight in the way CommandBar was designed.

[VBA]
Sub DeleteToolbar()
CommandBars("Template Toolbar").Delete
Documents.Open NameOfFile(j)
' This part works in the sense that it opens up the
' last file in the collection. I want the button selected file to open.
UserForm2.Show
End Sub
[/VBA]

So your best bet is to study and modify Jacob's suggestion. Make sure you know how to add the Reference in Tools/References like he said.

Just for arguments sake, lets say you assigned a different OnAction macro to each button, as in:

.OnAction = "DeleteToolbar" & "1" (or 2, 3, etc using some counter variable)

Well, then you would have had to have ALREADY WRITTEN those extra 2 or 3 (or 50!) DeleteToolbarX subroutines before you ran your code!!! And how would you know how many extra subroutines to write?

See, so with the CommandBar thing you are really stuck!!! Better to try a UserForm

Quote:

---

Originally Posted by DRJ

Did you put in the path as "C:\myPath\" with the path seperator in already?

---

Nope. I just put "C:\myFolder"

Quote:

---

For the duplicate files were there .doc and .dot files with the same names or was there just a FileA.doc that shows up twice?

---

just one file that showed up twice. Do you think it matters that I have Windows XP with Windows Script Host 5.6 and the .NET Framework installed???

Don't ask me what any of that (5.6, .NET, etc) actually MEANS... I just know I have it!!

Hmm. It worked fine on my PC at home with Windows XP and Office XP. At work I have Windows XP and Office 2003 and I do get the extra path seperator, but not the duplicate file name.

Probably just needs some tweaking here or there.

Hi Kelly,

Just starting to look at this and the routine looks like it needs a bit of tidying up to make sure the file names are properly attached to the buttons, etc., but the thing you say doesn't exist is actually CommandBars.ActionControl and is just what this process needs

Hi Tony and Kelly:

Another way to make the code work is as follows:

[VBA] Public FileToOpen, NameOfFile(400) As String
Sub Buffalo()
Dim PopupBar1 As CommandBar
Dim NewButton As CommandBarButton
Dim i As Integer
Dim FileToOpen As String
Dim FName As String
Dim DirName As String
DirName = "C:\ZNA Templates\Buffalo"
With Application.FileSearch
.NewSearch
.FileType = msoFileTypeAllFiles
.LookIn = DirName
.Execute
Set PopupBar1 = CommandBars.Add("Template Toolbar", msoBarFloating)
If .FoundFiles.Count > 0 Then
For i = 1 To .FoundFiles.Count
FileToOpen = .FoundFiles(i)
FName = Left(" | " & Trim(Str(i)) & " " & Trim(Mid(.FoundFiles(i), Len(DirName) + 2)), 30)
Set NewButton = PopupBar1.Controls.Add(Type:=msoControlButton)
With NewButton
.Caption = FName
.Style = msoButtonCaption
.OnAction = "DeleteToolbar"
End With
NewButton.Parameter = .FoundFiles(i)
Next i
With PopupBar1
.Width = 5
.Height = 290
End With
PopupBar1.Visible = True
Else
MsgBox "There are no files in this location", vbCritical
End If
End With
End Sub

Sub DeleteToolbar()
Documents.Open Application.CommandBars.ActionControl.Parameter
CommandBars("Template Toolbar").Delete
UserForm2.Show
End Sub [/VBA]

As you can see there is an option as Commandbars.actioncontrol.parameter as mentioned by Tony. I got this code correction from another coder who I was referred to by Dreamboat. The code works just as it should.

Kelly, I actually need only one OnAction since it deletes the toolbar that is created and shows the userform. This will be for any file that I open, so it is not a problem. Writing several DeleteToolbar codes will increase the module size. But as you can try executing the above code, you will see that another userform is not needed.

Thanks guys for your input.

lynnnow

Hi lynnnow,

A couple of small points ..

1. You don't appear to be using the public variables at the top any more

2. The width and height values for the toolbar affect each other - you only need set one - in your case the width setting (and 5 is way too small anyway - off the top of my head I think about 27 is the minimum possible) is overridden by the height setting.