Access 2013 unable to open Excel 2013 object

I have an Access 2013 database that was originally Access 2010. Under Access 2010, the database would open an Excel 2010 object using VBA as follows:

 

Dim ExcelObject as Object

Set ExcelObject = GetObject(...filepath...)

ExcelObject.Application.Visible = True

ExcelObject.Windows(1).Visible = True

 

However, now under Access 2013, the error "Subscript out of range" is issued when it executes the line "ExcelObject.Windows(1).Visible = True". I checked the documention for Access 2013 / Excel 2013 and the statements are correct. However, they do not work. How do I get this to work with Access 2013 to open an Excel 2013 object? I am using Office 2013 the standalone desktop version, not Office 365.

What is the objective of that line? Comment it out and see how you fare.

 

-Tom.
Microsoft Access MVP
Phoenix, AZ

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

The last line makes the "sheet" window visible. Without that line all you get is the Excel application window visible, but the sheet window is still hidden.

refer to http://msdn.microsoft.com/en-us/library/e9waz863(v=VS.90).aspx

 

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I have exactly the same question. Have you found a solution for this?

Thanks

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

No I have not found a solution. No one seems to have an answer. The only thing I have done is gone back to Office 2010 software. Under Office 2010, Access opens the Excel file properly. Until I find an answer, I am staying with 2010.

 

I regret having spent the money on Office 2013 only to find it is pretty well useless to me.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I was experiencing the same issue and figured out the solution.  Add a line of code as seen in bold below:

ExcelObject.Application.Visible = True

ExcelObject.NewWindow

ExcelObject.Windows(1).Visible = True

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

The "ExcelObject.NewWindow" sort of works. It works, but not properly. Let me explain.

 

The first time the excel file is programmatically opened, there are two windows (one blank and the other good). This I can live with. However, the problem arises if you close the Excel file and then decide later you want to reopen the same Excel file programatically. This time there are 3 windows (two blank and one good). Every time you close and reopen the same file programatically, there is another blank window added. The number of blank windows keeps growing each time you close the file and then reopen the file programmatically.

 

The only way I could reset the number of blank windows back to one is to reboot the computer.

 

So Access 2013, thumbs down. Access 2010, thumbs up. So back to 2010.

 

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I've had a chance to play around with this some more. I was able to finally get the result I was looking for. Instead of using the original code:

Dim ExcelObject as Object

Set ExcelObject = GetObject(...filepath...)

ExcelObject.Application.Visible = True

ExcelObject.Windows(1).Visible = True

I added a "workbooks" activate line, so that the code now reads:

Dim ExcelObject as Object

Set ExcelObject = GetObject(...filepath...)

ExcelObject.Application.Visible = True

ExcelObject.Application.Workbooks(1).Activate

ExcelObject.Windows(1).Visible = True

Adding the "ExcelObject.Application.Workbooks(1).Activate" solved the problem so that it works the same as pre-2013 Access.

3 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I've had a chance to play around with this some more. I was able to finally get the result I was looking for. Instead of using the original code:

Dim ExcelObject as Object

Set ExcelObject = GetObject(...filepath...)

ExcelObject.Application.Visible = True

ExcelObject.Windows(1).Visible = True

I added a "workbooks" activate line, so that the code now reads:

Dim ExcelObject as Object

Set ExcelObject = GetObject(...filepath...)

ExcelObject.Application.Visible = True

ExcelObject.Application.Workbooks(1).Activate

ExcelObject.Windows(1).Visible = True

Adding the "ExcelObject.Application.Workbooks(1).Activate" solved the problem so that it works the same as pre-2013 Access.

6 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I've had a chance to play around with this some more. I was able to finally get the result I was looking for. Instead of using the original code:

Dim ExcelObject as Object

Set ExcelObject = GetObject(...filepath...)

ExcelObject.Application.Visible = True

ExcelObject.Windows(1).Visible = True

I added a "workbooks" activate line, so that the code now reads:

Dim ExcelObject as Object

Set ExcelObject = GetObject(...filepath...)

ExcelObject.Application.Visible = True

ExcelObject.Application.Workbooks(1).Activate

ExcelObject.Windows(1).Visible = True

Adding the "ExcelObject.Application.Workbooks(1).Activate" solved the problem so that it works the same as pre-2013 Access.

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated October 8, 2020 Views 1,663 Applies to: