Opening Excel Spreadsheet from Word

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
LVL 2
jdc1944Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
terencinoConnect With a Mentor Commented:
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
 
jdc1944Author Commented:
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
 
jdc1944Author Commented:
Led me to find the issue was with a variable.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.