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

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