Read Excel Cell from Word VBA

[ScotWW]

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.

[ScotWW]

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

[ScotWW]

I adapted the above answer from https://stackoverflow.com/questions/29269278/vba-code-in-word-to-import-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)

[gmayor]

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

[formulation]

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

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.)


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).

[illogic]

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.



Public Sub UserForm_Initialize()
Dim findingsPreSelectionList As Variant

findingsPreSelectionList = Array("Item1", "Item2", "Item3", "Item4", "Item5")
ListBox5.List = findingsPreSelectionList
End Sub


I created a excel document which i want to use as a database for ListBox6.
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.


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


I stumbled upon this thread while searching the internet for a solution on how to do this.
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.



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


Any help would be appreciated!

best regards
Manuel