Microsoft Office Forums - View Single Post - [Solved] How to access Excel worksheet data from Word

jules43 · 03-23-2016, 02:34 AM

I a Word template that accesses data from an excel spreadsheet that I am attempting to get working with Office 2013.

The template has a form and my code to access the spreadsheet runs from the _Initialize function of that form.

The code is loosely based on the code here: https://msdn.microsoft.com/en-us/lib.../gg251785.aspx

The code at the moment is as follows:

Code:

Sub readExcelData()
    Dim myXL As Object
    Dim myWS As Object
    Set myXL = GetObject("E:\Test\data.xlsx")
    
    myXL.Application.Visible = True
    myXL.Parent.Windows(1).Visible = True
    Set myWS = myXL.Application.Worksheets("Customers")
    Count% = 0
    Do
        Count% = Count% + 1
        a$ = myWS.Range("a" & Count%)
    Loop While a$ <> ""
    
    For i% = 1 To Count% - 1
        custName$ = myWS.Range("a" & i%)
        custCode$ = myWS.Range("b" & i%)
        custAddress$ = myWS.Range("c" & i%)
        customerBox.AddItem (custName$)
        CustomerCodes(i%) = custCode$
        CustomerAddresses(i%) = reCrLf(custAddress$)
    Next
    Set myWS = myXL.Application.Worksheets("HiddenData")
    Sequence% = myWS.Range("A1")
    myXL.Application.Workbooks(1).Save
    myXL.Application.Quit
    Set myXL = Nothing
End Sub

In previous versions of Office when this code is run, it opens the spreadsheet in excel, then closes it again, and completes successfully.

When I run this code under Office 2013 I get:

Run-time error '9'
Subscript out of range

It turns out that this line is the causing the problem.

Code:

myXL.Parent.Windows(1).Visible = True

In previous versions Windows is an array containing 1 element, under 2013 at this point it has no elements.

If I skip over the line, then the next line:

Code:

Set myWS = myXL.Application.Worksheets("Customers")

Fails with:

Run-time error '1004':
Application-defined or object-defined error