Hi.
I'm working in a Word Userform, and need to read specific cells in specific sheets of an excel file. Say I want to put these values into a textbox.
I have the code to open the excel file but haven't got further.
Any help would be appreciated.
Hi.
I'm working in a Word Userform, and need to read specific cells in specific sheets of an excel file. Say I want to put these values into a textbox.
I have the code to open the excel file but haven't got further.
Any help would be appreciated.
Solution is:
Sub test()
Dim myexcel As Object
Dim myWB As Object
Set myexcel = CreateObject("Excel.Application")
Set myWB = myexcel.Workbooks.Open(ActiveDocument.Path & "/ConfigFile.xlsm")
Dim x As String
x = myWB.Sheets("Bookmarks").Cells(1, 1)
myWB.Close False
Set myexcel = Nothing
Set myWB = Nothing
End Sub
I adapted the above answer from https://stackoverflow.com/questions/...ort-excel-data
The object types are defined there as Dim myWB As Excel.Workbook and Dim myexcel As Excel.Application
Some reference needs to exist to use these types, otherwise the "Compile Error User-type not defined" error results (as it did with me)
You would have needed a reference to be set to Excel in VBA > Tools > references in order to use early binding, which is why you saw the errors. The change to late binding however does work. The only real issue is that it is painfully slow, as it has to wait while Excel loads and then loads the workbook and locates the required cell. The following function reads the worksheet into an array without opening Excel and is almost instantaneous in use. Interrogate the array to locate the cell you want - here cell A1
You can make it even faster by setting a named range in Excel to cover only the data you want to search and access that instead, making a small change in the code as shown to do so.
Option Explicit Private Const strWorkbook As String = "E:\Path\ConfigFile.xlsx" 'The path of the workbook Private Const strSheet As String = "Bookmarks" 'The name of the worksheet (or range) Sub Test() Dim Arr() As Variant Dim sFind As String Arr = xlFillArray(strWorkbook, strSheet) sFind = Arr(0, 0) 'cell A1 MsgBox sFind lbl_Exit: Exit Sub End Sub Private Function xlFillArray(strWorkbook As String, _ strRange As String) As Variant 'Graham Mayor - http://www.gmayor.com - 24/09/2016 Dim RS As Object Dim CN As Object Dim iRows As Long strRange = strRange & "$]" 'Use this to work with a named worksheet 'strRange = strRange & "]" 'Use this to work with a named range Set CN = CreateObject("ADODB.Connection") 'Set HDR=YES for a sheet or range with a header row CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & strWorkbook & ";" & _ "Extended Properties=""Excel 12.0 Xml;HDR=NO"";" Set RS = CreateObject("ADODB.Recordset") RS.Open "SELECT * FROM [" & strRange, CN, 2, 1 With RS .MoveLast iRows = .RecordCount .MoveFirst End With xlFillArray = RS.GetRows(iRows) If RS.State = 1 Then RS.Close Set RS = Nothing If CN.State = 1 Then CN.Close Set CN = Nothing lbl_Exit: Exit Function End Function
Graham Mayor - MS MVP (Word) 2002-2019
Visit my web site for more programming tips and ready made processes
http://www.gmayor.com
Change the bits in red to point to an existing workbook - we can change this to a dialog box later, but this will do for now - and a cell in that workbook whose value you know. Go Tools > References in the VBA editor and add a reference to the Microsoft Excel Object Library. (This 'teaches' Word how to handle Excel objects.)Option Explicit Public Sub GetExcelData() Dim objExcel As Excel.Application Dim wkbk As Excel.Workbook Dim ws As Excel.Worksheet Set objExcel = CreateObject("Excel.Application") Set wkbk = objExcel.Workbooks.Open("c:\temp\book1.xls") Set ws = wkbk.Sheets(1) ws.Range("A1").Copy Selection.Paste wkbk.Close savechanges:=False objExcel.Quit End Sub
Return to the Word document and go Developer > Macros > GetExcelData > Run. Does the cell you specified get imported from the workbook to the Word document?
If so, great, that bit works. Now you just need to:-
Get the process error number from your form and pass it to the VBA code
Write a routine to find the process error number in the Excel workbook
Return the contents of the appropriate record back to the form
I can help you with (2) but I've not really done anything with Word, so I don't know enough about it to read data from a form and return data to it. You'd need to find someone else to help with (1) and (3).
Hello,
sorry for resurrecting this two year old thread, but it kind of relates to a problem i am having with my userform.
I have two listboxes in my userform 'frmFindings', 'ListBox5' and 'ListBox6'.
ListBox5 ist populated with a fixed set of items when the userform gets initialized.
I created a excel document which i want to use as a database for ListBox6.Public Sub UserForm_Initialize() Dim findingsPreSelectionList As Variant findingsPreSelectionList = Array("Item1", "Item2", "Item3", "Item4", "Item5") ListBox5.List = findingsPreSelectionList End Sub
The excel document consists of several worksheets relating to the ListBox5 items (item1, item2, item3, item4, item5).
Each sheet has a set of data (long texts) written in Column A (A1, A2, A3...) with a variable range.
The number of rows/cells in Column A that contain information varies from sheet to sheet and may change later on.
I want it to be extensible without making to many adjustments to the code if possible.
When the user selects an item from ListBox5 like Item2 or Item4 and so on, then ListBox6 should show the content from that related excel sheet.
I stumbled upon this thread while searching the internet for a solution on how to do this.Private Sub ListBox5_Click() Dim i As Integer For i = 0 To ListBox5.ListCount - 1 If ListBox5.Selected(i) Then If ListBox5.List(i) = "Item1" Then 'The ListBox6 should show the items from the excel workbook with sheetname "Item1" End If If ListBox5.List(i) = "Item2" Then 'The ListBox6 should show the items from the excel workbook with sheetname "Item2" End If '... End If Next i End Sub
Is it possible with your Code to achieve my goal?
How do i need to set this up to work with multiple sheets, with different names and variable datacount.
Any help would be appreciated!Option Explicit Private Const strWorkbook As String = "E:\Path\ConfigFile.xlsx" 'The path of the workbook Private Const strSheet As String = "Bookmarks" 'The name of the worksheet (or range) ' Sub Test() Dim Arr() As Variant Dim sFind As String Arr = xlFillArray(strWorkbook, strSheet) sFind = Arr(0, 0) 'cell A1 MsgBox sFind lbl_Exit: Exit Sub End Sub Private Function xlFillArray(strWorkbook As String, _ strRange As String) As Variant 'Graham Mayor - http://www.gmayor.com - 24/09/2016 Dim RS As Object Dim CN As Object Dim iRows As Long strRange = strRange & "$]" 'Use this to work with a named worksheet 'strRange = strRange & "]" 'Use this to work with a named range Set CN = CreateObject("ADODB.Connection") 'Set HDR=YES for a sheet or range with a header row CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & strWorkbook & ";" & _ "Extended Properties=""Excel 12.0 Xml;HDR=NO"";" Set RS = CreateObject("ADODB.Recordset") RS.Open "SELECT * FROM [" & strRange, CN, 2, 1 With RS .MoveLast iRows = .RecordCount .MoveFirst End With xlFillArray = RS.GetRows(iRows) If RS.State = 1 Then RS.Close Set RS = Nothing If CN.State = 1 Then CN.Close Set CN = Nothing lbl_Exit: Exit Function End Function
best regards
Manuel