RowSource - using Excel

[DAustin]

Hi there,

Does anyone know how I can get the RowSource for a multicolumn listbox in
Word to be a range in an Excel spreadsheet?? (Bearing in mind this question
comes from someone who hasn't managed to automate Excel from VB in Word yet!)

I also really need to do the same thing with Excel for a listbox in Visio

Thanks!

 

[Doug Robbins - Word MVP on news.microsoft.com]

First off, in the Visual Basic Editor, you need to set a reference to the
Microsoft DAO 3.5 (or 3.6) Object Library

Dim db As DAO.Database
Dim rs As DAO.Recordset
' Open the file containing the Office Locations
Set db = OpenDatabase(ThisDocument.Path & Application.PathSeparator &
"SpreadsheetName.xls", False, False, "Excel 8.0")
' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM `NamedRangeinSpreadsheetName`")
' Determine the number of retrieved records
With rs
.MoveLast
i = .RecordCount
.MoveFirst
End With
' Set the number of Columns = number of Fields in recordset
lstListBoxName.ColumnCount = rs.Fields.Count
' Load the listbox with the retrieved records
lstListBoxName.Column = rs.GetRows(i - 1)
' Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

You will need to supply the actual names for the following in the above

SpreadSheetName
NamedRangeinSpreadSheetName
lstLisBoxName

--
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, originally posted via msnews.microsoft.com

 

[DAustin]

Thanks Doug. Like many of these things, it doesn't seem overly complex, it's
just small things tripping me up!

The only thing is it's showing the columns from my spreadsheet as rows in
the List Box ... am I missing something obvious??

Next step for me is to work out how to add and modify records in the
spreadsheet from the form in Word. I imagine I can, but if you have anything
simple to point me towards doing this would be great.

Thanks again!

 

[Doug Robbins - Word MVP on news.microsoft.com]

Here is a better way of doing it that I came up with yesterday. If requires
a reference to the Excel Object Model be set in the VBE

Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
'Dim xlrange As Excel.Range
Dim Listarray As Variant
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application") ''
If Err Then
ExcelWasNotRunning = True
Set xlApp = New Excel.Application
End If
With xlApp
Set xlbook = .Workbooks.Open("C:\path\Workbook.xls")
'Set xlrange = Range("NamedRangeinWorkBook")
Listarray = Range("DataItems") 'xlrange
'Set xlrange = Nothing
xlbook.Close SaveChanges:=False
Set xlbook = Nothing
End With
If ExcelWasNotRunning Then
xlApp.Quit
End If
Set xlApp = Nothing
With ListBox1
.ColumnCount = UBound(Listarray, 2)
.Clear
.List() = Listarray
End With


--
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, originally posted via msnews.microsoft.com

 

[DAustin]

Been away, just come back to this. Just what I needed thanks, and this is
much simpler. The key thing I'd missed all along was the reference to the
Excel Object Model be set in the VBE ... once that's done, everything becomes
simple!

Cheers