#1
|
|||
|
|||
Load Listbox into Word form from an Excel workbook
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 |
#2
|
|||
|
|||
You say you don't want to open the Excel file? The second line of your code "Opens" the Excel file.
You could use and ADODB connection: Code:
Option Explicit Public Function xlFillList(oListOrComboBox As Object, strWorkbook As String, _ bSuppressHeader As Boolean, strSQL As String, _ bSingleColumn As Boolean) Dim oConn As Object Dim oRecordSet As Object Dim lngNumRecs As Long, lngIndex As Long Dim strWidth As String Dim strConnection As String 'Create connection: Set oConn = CreateObject("ADODB.Connection") If bSuppressHeader Then strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & strWorkbook & ";" & _ "Extended Properties=""Excel 12.0 Xml;HDR=YES"";" Else strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & strWorkbook & ";" & _ "Extended Properties=""Excel 12.0 Xml;HDR=NO"";" End If oConn.Open ConnectionString:=strConnection Set oRecordSet = CreateObject("ADODB.Recordset") 'Read the data from the worksheet. oRecordSet.Open strSQL, oConn, 3, 1 '3: adOpenStatic, 1: adLockReadOnly With oRecordSet 'Find the last record. .MoveLast 'Get count. lngNumRecs = .RecordCount 'Return to the start. .MoveFirst End With With oListOrComboBox .Clear 'Load the records into the columns of the named list/combo box. .ColumnCount = oRecordSet.Fields.Count .Column = oRecordSet.GetRows(lngNumRecs) strWidth = vbNullString If bSingleColumn Then 'Set the widths of the combo/list box columns to display only the first column. strWidth = .Width - 20 & " pt;" For lngIndex = 2 To .ColumnCount strWidth = strWidth & "0 pt" If lngIndex < .ColumnCount Then strWidth = strWidth & ";" End If Next lngIndex Else For lngIndex = 1 To .ColumnCount strWidth = strWidth & Val(.Width \ .ColumnCount) - 10 & " pt;" Next lngIndex .ColumnWidths = strWidth End If End With Cleanup: If oRecordSet.State = 1 Then oRecordSet.Close Set oRecordSet = Nothing If oConn.State = 1 Then oConn.Close Set oConn = Nothing lbl_Exit: Exit Function End Function Call from form like this: Code:
Private Sub UserForm_Initialize() Dim DataSourcePath As String Dim strSQL As String DataSourcePath = ThisDocument.Path & "\Basic Fill.xlsx" 'Get all data from sheet named "BasicI", exclude heading row, single Column strSQL = "SELECT * FROM [BasicI$];" mod_ExcelInteropSA.xlFillList lstBasicI, DataSourcePath, "True", strSQL, "True" 'Get all data from sheet named "BasicII", including heading row, show all columns strSQL = "SELECT * FROM [BasicII$];" mod_ExcelInteropSA.xlFillList lstBasicII, DataSourcePath, "False", strSQL, "False" 'Get data from columns headed "Name" and "Amount" from sheet named "BasicIII", exclude heading row, show all columns strSQL = "SELECT Name, Amount From [BasicIII$];" mod_ExcelInteropSA.xlFillList lstBasicIII, DataSourcePath, "True", strSQL, "False" lbl_Exit: Exit Sub End Sub |
#3
|
|||
|
|||
Thanks for your support, I can't make it work, I don't understand the code well.
Also, I don't know why it converts the Excel file into Read Only, which would not be good to continue working. I prefer to upload the files so you can review them. I would like to see in the listbox the Bibliography sheet, columns A and B Thanks again |
#4
|
|||
|
|||
You certainly didn't try very hard. It would help if the name of the listbox in your form "Listbox1" was the same as the name of the listbox you are trying to pass to the function (lstBiligraphia).
Put the function in a standard module called mod_ExcelInteropSA Not in the form. |
#5
|
|||
|
|||
Greetings, you are very right
I did not try much. The code became confusing to me, and I have already lost several files for misinterpreting the codes. I've been used to seeing and using it like this: HTML Code:
''ListBox1.RowSource = "'" & GENTEST.Sheets("Hoja5").Name & "'!A1:B" & wbLibroOrigen.Sheets("Hoja5").Range("A" & Rows.Count).End(xlUp).Row But I understood I passed the Function to a module and it was not the name of the sheet, but the Listbox .... sorry! with these fixes .. It works!!! Great, and the Excel file no longer becomes Read Only ... I am really very grateful !! |
Tags |
excel 2013, vba, word 2013 |
|
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 |