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

[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