How to make a macro that allows the user to choose and open a word file

Question

I have an Excel file and I want to have a macro in it that allows the user to choose any Word File and then open this selected file. Is this possible?

May I know what is the correct way to achieve my objective?

The correct way is to try something, show us what you've tried, and ask about any specific issues you run into. — Jean-François Corbett, May 21 '14 at 06:36
@Jean-FrançoisCorbett im pretty sure you were able to understand what i meant. — user3489601, May 21 '14 at 07:51

score 1 · Answer 1 · answered May 21 '14 at 06:51

1

You are really not that specific but i guess you would have the names of all documents listed in your Excel Cells. In the OnClick Event of that Cell you could then open a word document like this:

Sub openWordDocument()

    Set wordapp = CreateObject("word.Application")  
    wordapp.documents.Open "C:\Documents\blablabla" & ActiveCell.Value
    wordapp.Visible = True

End Sub

You would have to combine the path where you have saved your word files and the name of that specific file, which I assumed you write in your cell.

If you need something else please specify...

answered May 21 '14 at 06:51

oHoodie

209
4
13

I have a macro on my excel file to paste all tables i have on the excel file to specefic bookmarks on a MS word template. The code I was able to come up with was one where I would choose the file. the problem is that the user may want to update this template. in this case, I`d need the macro code to at the least give the user a dialog box with a list of files within a directory... – user3489601 May 21 '14 at 07:27
I found this little guide which you can use to get all files in a directory: http://excelexperts.com/vba-tips-list-files-in-a-folder – oHoodie May 21 '14 at 07:35
If you want this not directly in your cells then you should create a UserForm. With a control like ListBox or ListView you can create the list of all files in that specific directory. – oHoodie May 21 '14 at 07:39
thanks for your help as well, @oHoodie. The other answer i got solved my problem already :) – user3489601 May 21 '14 at 07:55

score 0 · Accepted Answer · edited May 21 '14 at 08:11

To select the file, you can use Excel Application's .GetOpenFilename method.
The method returns the selected file's full path.

Dim oWFile As String
oWFile = Application.GetOpenFilename("Word File, *.docx")

Now to open the file, you need to get/create an instance of Word.
Meaning you have to bind Word to Excel. Sample below

Dim oWApp As Word.Application
'~~> get instance if already existing
On Error Resume Next
Set oWApp = GetObject(, "Word.Application")
On Error GoTo 0
'~~> create it otherwise
If oWApp Is Nothing Then Set oWApp = CreateObject("Word.Application")
'~~> make sure it is visible
oWApp.Visible = True
'~~> Open the word file using the grabbed/created Word Application Object
'~~> and the full file path we get using the .GetOpenFilename method
oWApp.Documents.Open oWFile

I used Early Binding where in you need to reference the Microsoft Word Object Library.
To do this, in VBE go to Tools > References and select
Microsoft Word 14.0 Object Library (for office 2010).
The number varies depending on the office version.

How to make a macro that allows the user to choose and open a word file

2 Answers2