Solved

Opening Excel Spreadsheet from Word

Posted on 2013-02-05
3
406 Views
Last Modified: 2013-02-05
I wrote some VB to open up and specific Excel spreadsheet in office 2003.  The code i used to do this was:

Set oExcel = CreateObject("Excel.Application")
Set oWB = oExcel.Workbooks.Open(SheetLocation)

Open in new window


This worked fine.

We have now moved to office 2010 and the above no longer works.  I have added the Excel 14.0 object library reference but the error message i get is:

"Unable to get the Open Property of the workbook class"

It errors on "Set oWB = oExcel.Workbooks.Open(SheetLocation)"

Any ideas how to open up the spreadsheet in the correct way for 2010?

Thanks
0
Comment
Question by:jdc1944
  • 2
3 Comments
 
LVL 16

Accepted Solution

by:
terencino earned 500 total points
ID: 38854368
This works on 2010 and 2013.
Hope this helps
...Terry

Dim oExcel As Object, oWB As Object
SheetLocation = "C:\Test\Book1.xlsx"
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
Set oWB = oExcel.Workbooks.Open(SheetLocation)

Open in new window

0
 
LVL 2

Author Comment

by:jdc1944
ID: 38854408
Thanks for that.  

Using the above I realized that the issue was the SheetLocation variable which was storing the wrong path.  Whereas in word this normally creates an error that the file could not found, in excel it comes with the above error message which to me doesn't relate.
0
 
LVL 2

Author Closing Comment

by:jdc1944
ID: 38854412
Led me to find the issue was with a variable.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Selection object is designed for user interaction. It has a Range property, so it can be used in most places that a Range object can. Recorded macros must use the Selection because they are simply copying what the user is doing. A Range prope…
This is written from a 'VBA for MS Word' perspective, but I am sure it applies to most other MS Office components where VBA is used.  One thing that really bugs me is slow code, ESPECIALLY when it's mine!  In programming there are so many ways to…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now