Microsoft Office Forums - View Single Post - Data from hidden internal tables feeding listboxes in same Word Document

marksm33 · 02-18-2014, 06:09 AM

Hello there,

I'm currently pulling data for my listboxes in an MS Word document from a separate document in Excel. I'd like to pull the data instead from hidden tables within the same Word Document. I can find some answers in the FAQ sections about pulling data from EXTERNAL sources, but not from within.

Any ideas to point me in my next right direction would be appreciated. This is the code that I am currently using for one of my listboxes. I use Office 2010 on a Windows 7 machine.

Thanks in advance, Mark:

Code:

Option Explicit

Private Sub UserForm_Initialize() 
     'Late binding. No reference to Excel Object required.
    Dim xlApp As Object 
    Dim xlWB As Object 
    Dim xlWS As Object 
    Dim cRows As Long 
    Dim i As Long 
    Dim LR As Long 
    Set xlApp = CreateObject("Excel.Application") 
     'Open the spreadsheet to get data
    Set xlWB = xlApp.Workbooks.Open("C:\Users\mark.miller\Desktop\Te1.xltm") 
    Set xlWS = xlWB.Worksheets("Evidence") 
    On Error Goto 0 
    cRows = xlWS.Range("$A1:$A15").Rows.Count 
    Me.lb1aa.ColumnCount = 1 
     'Populate the listbox.
    With Me.lb1aa 
        For i = 2 To cRows 
             'Use .AddItem property to add a new row for each record and populate column 0
            .AddItem xlWS.Range("Ev1a").Cells(i, 1) 
        Next i 
    End With 
     'Clean up
    Set xlWS = Nothing 
    Set xlWB = Nothing 
    xlApp.Quit 
    Set xlApp = Nothing 
lblaa_Exit: 
    Exit Sub 
End Sub 

Private Sub cb1aa_Click() 
    Dim i As Long 
    Dim strDocs As Variant 
    For i = 0 To lb1aa.ListCount - 1 
        If lb1aa.Selected(i) = True Then 
            strDocs = strDocs & lb1aa.List(i) & vbCr 
        End If 
    Next i 
    strDocs = Left$(strDocs, Len(strDocs) - 1) 
    ActiveDocument.Bookmarks("bm1aa").Range _ 
    .InsertBefore strDocs 
    UF1aa.Hide 
End Sub