Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-19-2020, 09:50 AM
Visor Visor is offline Load Listbox into Word form from an Excel workbook Windows 10 Load Listbox into Word form from an Excel workbook Office 2013
Advanced Beginner
Load Listbox into Word form from an Excel workbook
 
Join Date: Aug 2019
Posts: 38
Visor is on a distinguished road
Default 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
I appreciate your kind support for this issue
Reply With Quote
  #2  
Old 07-19-2020, 03:33 PM
gmaxey gmaxey is offline Load Listbox into Word form from an Excel workbook Windows 10 Load Listbox into Word form from an Excel workbook Office 2016
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 1,076
gmaxey has a spectacular aura aboutgmaxey has a spectacular aura aboutgmaxey has a spectacular aura about
Default

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
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #3  
Old 07-19-2020, 05:17 PM
Visor Visor is offline Load Listbox into Word form from an Excel workbook Windows 10 Load Listbox into Word form from an Excel workbook Office 2013
Advanced Beginner
Load Listbox into Word form from an Excel workbook
 
Join Date: Aug 2019
Posts: 38
Visor is on a distinguished road
Default

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
Attached Files
File Type: docm Load Listbox Word from Excel.docm (23.3 KB, 2 views)
File Type: xlsm GENTEST.xlsm (11.8 KB, 2 views)
Reply With Quote
  #4  
Old 07-20-2020, 08:39 AM
gmaxey gmaxey is offline Load Listbox into Word form from an Excel workbook Windows 10 Load Listbox into Word form from an Excel workbook Office 2016
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 1,076
gmaxey has a spectacular aura aboutgmaxey has a spectacular aura aboutgmaxey has a spectacular aura about
Default

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.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #5  
Old 07-20-2020, 09:58 AM
Visor Visor is offline Load Listbox into Word form from an Excel workbook Windows 10 Load Listbox into Word form from an Excel workbook Office 2013
Advanced Beginner
Load Listbox into Word form from an Excel workbook
 
Join Date: Aug 2019
Posts: 38
Visor is on a distinguished road
Red face

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
... and, In the provided code I do not distinguish from where to where it loads the listbox..

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 !!
Reply With Quote
Reply

Tags
excel 2013, vba, word 2013

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiselect listbox - custom outlook form A_Balt Outlook 0 07-19-2018 09:36 AM
Load Listbox into Word form from an Excel workbook 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

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 02:44 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft