Load Listbox into Word form from an Excel workbook

Visor · #1 07-19-2020, 09:50 AM

Dear forum members. This time I would like to solve this problem but I am blocked. I want to make a listbox work in a form made in word but the information is in an excel book, if possible without opening it.
For this I have:
My Word document in a folder
An excel book in the same folder, Name: GENTEST.xlsm
The sheet I require is Sheet5, Name: Bibliography

The columns that I want to be in the Listbox are: A and B until the last row with data

the code i have been testing is this:

HTML Code:

Sub cargarlistbox()
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim cRows As Long
Dim i As Long
  Set xlApp = GetObject(, "Excel.Application")
  Set xlWB = xlApp.Workbooks.Open("C:\Users\PC\Documents\RESPALDO\PROGRAM\WORD\TAXONOMIA\GENTEST.xlsm")

  Set xlWS = xlWB.Worksheets(5)
  cRows = xlWS.Range("Bibliografia").Rows.Count - xlWS.Range("Bibliografia").Row + 1
  ListBox1.ColumnCount = 2
  With Me.ListBox1
    For i = 2 To cRows
      .AddItem xlWS.Range("Bibliografia").Cells(i, 1)
      .List(.ListCount - 1, 1) = xlWS.Range("Bibliografia").Cells(i, 2)
    Next i
  End With
  Set xlWS = Nothing
  Set xlWB = Nothing
  xlApp.Quit
  Set xlApp = Nothing
lbl_Exit:
  Exit Sub
End Sub

I appreciate your kind support for this issue

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Multiselect listbox - custom outlook form	A_Balt	Outlook	0	07-19-2018 09:36 AM
Add additional column to a listbox in form.	14spar15	Excel Programming	2	06-02-2018 06:01 PM
Macro Personal Workbook does not load in Excel 2016	Intruder	Excel Programming	0	01-21-2017 05:44 AM
Personal Macro Workbook Does Not Load	WRowan	Excel	1	06-23-2016 08:26 AM
Data from Excel to Listbox multiselect to Word	marksm33	Word VBA	2	02-18-2014 08:30 AM