Opening MS Word from Ribbon dropdown

Microsoft Office access 2007 (full produ...
March 23, 2010 at 02:57:12
Specs: Windows XP
Hi all,
Thanks in advance for the help. Here's the dealio. I have used Custom UI Editor for excel and have a dropdown based on the contents of specified celss - this all works fine.

What I want to do is: the cells specified in the code are of names. eg James Brown - When I select the dropdown and click the accompanying button, I want to open "c:\blah\James Brown.doc" or the selected item. The var is MySelectedItem. Doesn't want to work for me!
I include code for the whole thing below, the final section is the part that isnt working..

Option Explicit

Dim ItemCount As Integer
Dim ListItemsRg As Range
Dim MySelectedItem As String

''=========Drop Down Code =========

''Callback for Dropdown getItemCount.
''Tells Excel how many items in the drop down.
Sub DDItemCount(control As IRibbonControl, ByRef returnedVal)
With Sheet1.Range("A7:A100")
Set ListItemsRg = Range(.Cells(1), .Offset(.Rows.Count).End(xlUp))
ItemCount = ListItemsRg.Rows.Count
returnedVal = ItemCount
End With
End Sub

''Callback for dropdown getItemLabel.
''Called once for each item in drop down.
''If DDItemCount tells Excel there are 10 items in the drop down
''Excel calls this sub 10 times with an increased "index" argument each time.
''We use "index" to know which item to return to Excel.
Sub DDListItem(control As IRibbonControl, index As Integer, ByRef returnedVal)
returnedVal = ListItemsRg.Cells(index + 1).Value
''index is 0-based, our list is 1-based so we add 1.
End Sub

''Drop down change handler.
''Called when a drop down item is selected.
Sub DDOnAction(control As IRibbonControl, ID As String, index As Integer)
' Two ways to set the variable MySelectedItem to the dropdown value

'way 1
MySelectedItem = ListItemsRg.Cells(index + 1).Value

''way 2
'Call DDListItem(control, index, MySelectedItem)

End Sub

''Returns index of item to display.
Sub DDItemSelectedIndex(control As IRibbonControl, ByRef returnedVal)
returnedVal = 0
MySelectedItem = ListItemsRg.Cells(1).Value
End Sub

''------- End DD Code --------

''Show the variable MySelectedItem (selected item in the dropdown)
''You can use this variable also in other macros
Sub ValueSelectedItem(control As IRibbonControl)

'Opens a Word Document from Excel

Dim objWord As Object
Dim path As String
Dim MySelectedItem As String

Set objWord = CreateObject("Word.Application")
objWord.Visible = True
path = "Z:\Owen\working model\ & MySelectedItem & .doc"

'Change the directory path and file name to the location
'of your document

objWord.Documents.Open (path)
End Sub

See More: Opening MS Word from Ribbon dropdown

March 23, 2010 at 05:06:30

The 'path =' line is missing two sets of double quotes, and should be:
path = "Z:\Owen\working model\" & MySelectedItem & ".doc"


Report •

March 23, 2010 at 05:26:15
Thanks for that, it is now atleast opening MS Word, but is not actually opening the relevant file. When I use a break and look at the value of MySelectedItem, it is "". The entire code is in a module and everything else works.

Report •

March 23, 2010 at 08:20:26

I can't really recreate your full environment and code to test anything, so this is just a suggestion:

As MySelectedItem is "" then look at this line:

MySelectedItem = ListItemsRg.Cells(index + 1).Value

You are indexing the range of cells with a single index number - this is allowable, but it is different to using .Cells(Row, Column)

As a result you may not be accessing the cell that you think that you are accessing.

Try adding two Watches:
ListItemsRg.Cells(index + 1).Address

Add a break at the above line and see what the worksheet name is and what the cell address is.

These will help identify what cells your range and index are actually pointing to.


Report •
Related Solutions

Ask Question