Word 2010 - File list from folder so user can select one from a form? 2

[colinmitton]

I need to produce a 'list' on a small pop up window with-in Word 2010 so a user can select one of the items and it get inserted in to the document. (I'm using it to do some basic merges for letters on to our stand letter template or to put paragraphs in to documents)

All the relevent items are word documents that are held in a folder on one of my servers. I have some basic marco's inplace to do inserts I'm just trying to clean it up and make it look nicer!

In access I would create a combo box and link it to a database for what I need to display. In this case I'm guessing I need to set up a form to display a combo box but I've never done this with-in word and have no idea if it can be linked to a folder to display the contents?

I've tried some google searches but I'm hitting a lot of dead ends and I'm guessing my terminology is wrong.

Any examples / guides are most welcome of course if its possible!

 

[macropod]

Hi col,

Instead of trying to populate a combobox, you could use Word's File dialogue:

With Application.FileDialog(msoFileDialogFilePicker)
  .AllowMultiSelect = False
  If .Show = -1 Then
    MsgBox .SelectedItems(1)
  End If
End With

With this, the user can select any kind of file. You can restrict the kinds of file that are displayed for selection, though, via code like:

With Application.FileDialog(msoFileDialogFilePicker)
  .AllowMultiSelect = False
  .Filters.Add "Images", "*.doc; *.docx; *.docm", 2
  .FilterIndex = 2
  If .Show = -1 Then
    MsgBox .SelectedItems(1)
  End If
End With

Cheers
Paul Edstein
[MS MVP - Word]

 

[colinmitton]

Thanks for that, I've found a different way when i stumbled on a posting while looking in to a different issue with an excel macro! Always the same when your not looking for something its the best time to find it!

Here's the code I'm using:

    DIRECTORY = Dir("" & Str1 & "\" & LetType & "\*.docx", vbNormal)
    Do Until DIRECTORY = ""
        ListLetters.AddItem DIRECTORY
        DIRECTORY = Dir()
    Loop

I set up some variables so the directory / sub folder was flexible. Does what I need but your solution may come in handy for v2!

Thanks

 

[colinmitton]

Hi Paul,

I've had a little play with your solution and I've found a possible use for it to help out one of our departments. I've got myself confused though while looking through the Microsoft help files.

I want to select a folder so I can use it in a macro to save documents. I know it sounds bad! Save As would work... but I need to do multiple 'saves' one as a Docx another as a PDF then attach the selected document (either PDF or Docx) to an email to send out. I have all the relevent workings in place its just selecting the relevent folder to save them too as it will change each time depending on client etc...

Can you select a 'starting' folder so they can start in the same place each time, and then instead of using a selected file out put the selected folder with full path so I can use it further on in the macro?

I'm sure there is I'm just running in circles now!

 

[macropod]

Try something along the lines of:

Sub Demo()
Dim strFolder As String
strFolder = GetFolder
If strFolder = "" Then Exit Sub
MsgBox strFolder
End Sub

Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

Cheers
Paul Edstein
[MS MVP - Word]

 

[PHV]

I've already posted this function:

Function PickFolder(strStartDir As Variant) As String
Dim SA As Object, f As Object
Set SA = CreateObject("Shell.Application")
Set f = SA.BrowseForFolder(0, "Choose a folder", 16 + 32 + 64, strStartDir)
If (Not f Is Nothing) Then
  PickFolder = f.Items.Item.Path
End If
Set f = Nothing
Set SA = Nothing
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[colinmitton]

Thanks, I'll have a play with both of your suggestions.

As a thought I want to direct them to different starting points depending on the macro they are running.
For example the 'savesales' macro would need to go to the 'sales' area (z:\sales team\2012) and they can browse to the client
Then if the used a different macro for 'PurchaseOrder' they can start at the accounts area (Y:\Accounts\purchase orders\2012). Then browse to the relevent subfolder.

I assume I would send a variable to function but how do I deliver that to the code you've provided?

I hope that makes sense!

 

[PHV]

I would send a variable to function
You don't know how to call a parametized function like mine ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[colinmitton]

Phv, sorry just spotted where I was going wrong. Long day with too much code
Will have a go tomorrow when my brain's had a reboot!

Thanks and my apologies...

 

[colinmitton]

Finally my Brains robooted and its exactly what I need!

Thanks so much for your help gentlemen.

 

[colinmitton]

I now have this code working in my macro and it runs like a dream, I do however wonder if I can add an additional set of folders to the view?

I.E at current it goes to our 'sales' directory or 'Client' directory depending on the macro clicked. I want to add 'favourites' to it as well.

So they have the choice of either the prescribed folder I assign to the macro or there own favourites kept in the user profile:
C:\Users\%username%\Links.

Is it possible?