opening excel workbooks from Word using VBA

[nannon8]

I am a relative beginner with VBA, and am trying to set up a template
in word that retieves data from excel and inserts it at a bookmarked
place in a word document. The problem is that the excel workbook will
change each time the template is used, as the data to be retrieved is a
monthly report, which will be saved with a different name from the
previous month, although in the same folder.

Ideally I would like for the user to be able to select the workbook to
be used from a dropdown list.

I have managed to get the following code to work for a workbook with a
static filename and path, but can't work out how to allow the user to
choose a workbook.

Dim myWB As Excel.Workbook
Set myWB = _
GetObject("C:\test")
Selection.GoTo _
What:=wdGoToBookmark, _
Name:="text"
Selection.TypeText _
(myWB.Sheets("Test").Range("text1"))
Set myWB = Nothing

 

[Doug Robbins - Word MVP]

The following code in the intialize event of a userform will allow the user
to browse to a folder and will then load a combobox on the userform with the
names of the files in the folder:

Private Sub UserForm_Initialize()
Dim MyPath As String
Dim MyName As String
Dim maindoc As Document

'let user select a path
With Dialogs(wdDialogCopyFile)
If .Display() <> -1 Then Exit Sub
MyPath = .Directory
End With

'strip quotation marks from path

If Len(MyPath) = 0 Then Exit Sub

If Asc(MyPath) = 34 Then
MyPath = Mid$(MyPath, 2, Len(MyPath) - 2)
End If

'get files from the selected path
'and insert them into the doc
MyName = Dir$(MyPath & "*.*")
Do While MyName <> ""
ComboBox1.AddItem MyPath & "\" & MyName
MyName = Dir
Loop

End Sub


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[nannon8]

Thanks for this.

When I run the code it brings back a run time error and says an object
is required. Also I do not need the user to select the path as this
will not change, just the file.

Once the file has been selected how do I link this back to the
GetObject command to extract the correct values from the workbook?

 

[nannon8]

Thanks for this works perfectly.

Now I can't figure out how to code the GetObject command to use the
document selected by the user.

I thought that I would be able to simply state combobox1.value, but it
doesn't work so I'm obviously doing something wrong! As I said I'm not
very good at VBA and tend to use trial and error a lot to get things to
work - I just can't figure this one out!!!

 

[nannon8]

Sorry figured it out I was just being stupid! Thanks for this my fom
now works brilliantly.