excel ranges in word

[dedmonds]

Hello,
I am trying to call a dynamic range from excel into a combo box in a word user form.
Can anyone see why the code I am using isn't working?
Private Sub equip_Initialize()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Set xlApp = CreateObject("Excel.Application")
xlBook.xlApp.Open = ("C:\Documents and Settings\44155\My Documents\Health & Safety\RAMS AUTOMATOR\automation tool for RAMS.xls")
'xlBook.Application.Visible = False'
xlBook.Application.WindowState = xlMinimized
With equip
Dim rngequip As Range
Dim ws As Worksheet
Set ws = Worksheets("sheet1")
For Each rngequip In ws.Range("Equipment")
Me.equip.AddItem rngequip.Value
Next rngequip

End With
End Sub

[Frosty]

A couple of issues, I believe.

1) You need to differentiate between your Word.Ranges and your Excel.Ranges, I believe.

Just start fully referencing things a bit better...

Dim rngequip As Excel.Range
Dim ws as Excel.Worksheet

When you're working in a multi-application environment, this is a good habit to get into.

Also, learning to use the Locals window helps in this kind of thing, because you can see what your declared objects are doing in the routine.

2. The With Equip block is confusing me. That's the name of your word user form? Or is that the name of your combo box? Why do you have a with block for "equip" and then reference Me.equip.AddItem?

Combo boxes don't have initialize events. User forms do. Since your sub is called equip_initialize, it seems that your form is called "equip"

But since you're trying to add items to me.equip... that suggests a combo box.

All of the above indicates a good reason to always use prefixes which help identify what you're working on. For example:

xlApp
xlBook
frmEquip
cmbEquip
rngEquip
xlWorkSheet

You get the idea... the actual prefixes mean less than consistency.

Without going to test your code to see if your excel objects and use are valid (I'm more immediately familiar with the word object model), that's a good starting off point for troubleshooting.

[dedmonds]

Ok so this is where I am up to now.
There is an excel workbook with 4 ranges in it.
I am trying to open the workbook and refer to its ranges in the word userform.
I get the error - Method or Data Member not found on this line:-

For Each RngEngineers1 In Ws.Range("Engineers1")
Me.first_eng.AddItem RngEngineers1.Value
Next RngEngineers1


Private Sub UserForm_Initialize()

Dim objExcel As New Excel.Application
Dim wb As Excel.Workbook

objExcel.Workbooks.Open ("C:\Documents and Settings\44155\My Documents\Health & Safety\RAMS AUTOMATOR\automation tool for RAMS.xls")


With wb.Sheets(1)
Dim RngEngineers1 As Range
Dim RngEngineers2 As Range
Dim RngEquipment As Range
Dim RngScheme As Range
Dim Ws As Worksheet
Set Ws = Worksheets("Sheet1")

For Each RngEngineers1 In Ws.Range("Engineers1")
Me.first_eng.AddItem RngEngineers1.Value
Next RngEngineers1

For Each RngEngineers2 In Ws.Range("Engineers2")
Me.sec_eng.AddItem RngEngineers2.Value
Next RngEngineers2
For Each RngEquipment In Ws.Range("Equipment")
Me.equip.AddItem RngEquipment.Value
Next RngEquipment
For Each RngScheme In Ws.Range("Scheme")
Me.worktype.AddItem RngScheme.Value
Next RngScheme


End Sub


any ideas why this is happening?

[Frosty]

Why don't you re-read my previous response. No point in typing it again. You still have the same problems and I still have the same questions.

Also, your code missing an End With

[Frosty]

In general, if you don't understand a comment, please indicate that you don't, otherwise I really don't know how to help.

1. How does your code know to treat something like an excel range instead of a word range? (where your dim statements are make zero difference)

2. How does you code know which workbook to work on? All you've done is dimensioned a variable and opened a workbook. You need a Set line to connect the two.

3. Always better to start with a proof of concept before making it more complex. All you've really done since the last bit of code is add a reference to the excel object model in your word project (introducing the concept of late-binding vs early-binding), and add a lot more complexity (3 more form controls to add stuff to).

I will need to play with some excel named ranges to see the proper way to grab data from them... But right now your code is saying "for each word.range in this excel named range..." which is obviously nonsensical. Excel ranges cover cells in a spreadsheet. Word ranges have a start and an end point, generally spanning one or more letters/words/paragraphs.

[Frosty]

All of the responders on this board have a different philosophy in approaching giving an answer to a question. Mine is always to teach first, give the solution second. Ultimately, the more you learn, the more you'll be able to solve the question on your own. To me, the tools to find the answer are far far more important than the answer itself. So I will give you the steps I'm using to solve your problem (since I don't actually know the answer off the top of my head).

You have the following "problems" to solve

1. How to get information from an excel range.

2. How to open an excel workbook using Word vba.

3. How to properly differentiate dimensioned variables in one application (Word) from another (Excel).

So I'm going to break it apart a bit. Just using Excel, it appears Value2 is what you're looking for, as the following seems to work in Excel VBA:
Solving Problem #1

Sub DisplaySomeInfo
Dim rng As Range
Dim i As Integer

Set rng = ActiveSheet.Range("MyRange")
For i = 1 To Ubound(rng.Value2)
MsgBox rng.Value2(i,1)
Next
End Sub
That assumes your range is a single column of cells, the data in which you ultimately want to add to a combo box on a user form in Word. Incidentally, if you declare variables in your procedures (which you should) and have Option Explicit at the top of your module (which you should) and don't regularly rely on module level variables (which you should *not*)... then View > Locals Window in VBA can be hugely useful (in combination with the help file) in trying to find the pieces you need to solve your problems.

Problem #2 -- opening an excel Workbook using Word VBA. There are two approaches to this, both of which you've used. I'm not going to go into a long discussion on early-binding vs. late-binding. But, assuming you have a reference in your Word VBA project to the Excel object library (which makes it easier to develop), you can use something like this. I've left the Object variables in, but commented out... as this code would work without a reference to the Excel object library, simply by commenting the Excel.Application/.Workbook/.Worksheet/.Range stuff and using Object variables instead.

Public Sub OpenAnExcelWorkBookAndDisplaySomeInfo()
' Dim xlApp As Object
' Dim xlWB As Object
' Dim xlWS As Object
' Dim xlRng As Object
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim xlRng As Excel.Range
Dim i As Integer

'CreateObject creates a new process, GetObject finds an existing process
Set xlApp = CreateObject("Excel.Application")
With xlApp
Set xlWB = .Workbooks.Open("C:\Temp\Book1.xlsx")
Set xlWS = xlWB.Sheets(1) 'or xlwb.ActiveSheet?
Set xlRng = xlWS.Range("MyRange")

For i = 1 To UBound(xlRng.Value2)
MsgBox xlRng.Value2(i, 1)
Next
End With
'close the workbook
xlWB.Close
'and the process
xlApp.Quit
'and do garbage collection
Set xlApp = Nothing
Set xlWB = Nothing
Set xlWS = Nothing
Set xlRng = Nothing
End Sub


Solving problem #3 requires a combination of the two pieces above. I'm not sure if your For Each...Next structure can work, but the place to test is in Excel, and modifying the first piece of code. Since it appears the Value2 property of a range is actually an array, you may not even need to loop through the various values using the For i = 1 To Ubound(xlRng.Value2) loop.

In my limited test, it appears that...
Me.ListBox1.List = xlRng.Value2 will serve to work.

Without knowing more, I'd be guessing to give you an actual answer. To get a "real" answer, you should mock up a test project and upload the whole thing (a word template with code in it and a user form, as well as an excel spreadsheet which has some sample data in the same way you have it set up).

But make sure you remove any actual sensitive data. Even the filepath for your excel file in your code may contain information you don't want out on the internet.

Hope this helps. Good luck

[Tinbendr]

Dim rngequip As Excel.Range