Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-23-2016, 02:34 AM
jules43 jules43 is offline How to access Excel worksheet data from Word Windows 10 How to access Excel worksheet data from Word Office 2013
Novice
How to access Excel worksheet data from Word
 
Join Date: Mar 2016
Posts: 2
jules43 is on a distinguished road
Default How to access Excel worksheet data from Word

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
Reply With Quote
  #2  
Old 03-23-2016, 02:36 AM
jules43 jules43 is offline How to access Excel worksheet data from Word Windows 10 How to access Excel worksheet data from Word Office 2013
Novice
How to access Excel worksheet data from Word
 
Join Date: Mar 2016
Posts: 2
jules43 is on a distinguished road
Default

I've fixed the problem. Added the line myXL.Activate
Reply With Quote
Reply

Thread Tools
Display Modes


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
How to access Excel worksheet data from Word Input data in Excel and get info from Access zkibz Excel Programming 1 08-14-2014 05:17 PM
How to access Excel worksheet data from Word Appending unique data from one worksheet to existing data on another worksheet EdStockton Excel 1 08-06-2014 11:00 PM
How to access Excel worksheet data from Word Excel or Access Shifting data Aarotech Excel 1 01-27-2013 06:43 AM
How to access Excel worksheet data from Word * Exporting Access Data to Excel djreyrey Excel Programming 1 03-23-2012 10:03 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:07 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft