How to access Excel worksheet data from Word

jules43 · #1 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Powerpoint 2013 VBA- Adding pictures and their related data from Excel worksheet	nirnat	PowerPoint	0	09-15-2015 03:27 AM
Input data in Excel and get info from Access	zkibz	Excel Programming	1	08-14-2014 05:17 PM
Appending unique data from one worksheet to existing data on another worksheet	EdStockton	Excel	1	08-06-2014 11:00 PM
Excel or Access Shifting data	Aarotech	Excel	1	01-27-2013 06:43 AM
* Exporting Access Data to Excel	djreyrey	Excel Programming	1	03-23-2012 10:03 PM