excelvbams-wordoffice-2013

How to retrieve data from Excel and add to Word


I have a Word template file that retrieves data from an Excel file to populate a form.

The code looks something like this:

Dim myXL As Object
Set myXL = Getobject("myfile.xls")
myXL.Application.Visible = True
myXL.Parent.Windows(1).Visible = True

This code works fine in Office 2010 and 2007, but when I try it in 2013, it gives run time error 9 which is an array subscript error. When I check the Windows array it has zero elements, so error is correct.

How do I achieve the same result in 2013?

The next bit of code attempts to access the Worksheets("mysheet") and if I skip the Visible = True line accessing the worksheet gives runtime error 1004.

Any help with fixing this would be greatly appreciated.


Solution

  • To make the code work on Office 2013 I added the line myXL.Activate before trying to make the Window visible. So the code becomes:

    Dim myXL As Object
    Set myXL = Getobject("myfile.xls")
    myXL.Application.Visible = True
    myXL.Activate
    myXL.Parent.Windows(1).Visible = True
    

    This fixed the run-time error, and the code went back to working well.