Issue with Selecting Sheets

[DefinitelynotMoss]

Hi All
Relatively new to MS Access. Was using 2010 without any issues until work decided to upgrade us to 2013. The main problem I have at the moment is that when the database is starting to output our reports to Excel it seems to get stuck on selecting a sheet in the Excel spreadsheet to paste the information into

The Error is "Subscript out of Range"

For example it errors at the following bit of code
xl.Sheets("Proposed Area Plan").Select

Eg it is selecting the Proposed Area Plan tab in the Excel spreadsheet.
It is an intermittent issue. I paste into literally dozens of tabs on different reports and it doesn't happen on all of them. I can restart the database and it works, sometimes it fails.

Like I said, this was working fine in 2010, now 2013 doesn't like it.

Can anyone please help?

Thanks
(NotMoss)

 

[arnelgp]

Use .Activate instead of .Select

 

[DefinitelynotMoss]

Hi, tried the .activate instead of .select and this does not work either, I get the same
Runtime Error 9
Subscript out of Range

 

[arnelgp]

Check fo extra space on the sheet name.

Better go to that sheet, rename, when got highlighted copy and paste the name to your code.

 

[DefinitelynotMoss]

All that is fine, this used to work perfectly, the only thing that has changed is the version of Access I have to use. I haven't renamed anything.

It is an intermittent problem, so it works some time, it doesn't work sometimes
Thanks

 

[arnelgp]

Sometimes missing reference on libraries can cause this error.

 

[DefinitelynotMoss]

OK thanks, I'll take a look. How do I know what is missing/what is needed?

 

[arnelgp]

Go to vba->tools->reference in the menu. If you find "missing" there try to browse abd replace with new version.

 

[DefinitelynotMoss]

Thanks, I checked another database as well and was missing an Active X 6.1 reference. The database ran without issue for the first time. I'll continue testing it though

Many Thanks

 

[arnelgp]

Ok, comeback if you find other issues.

 

[DefinitelynotMoss]

Nope, issue has cropped up again. It's totally intermittent.

 

[arnelgp]

Did you unselect the missing ref

 

[DefinitelynotMoss]

Yes, unless I'm missing something else, the references look "ok" to me

 

[arnelgp]

Sunce you are in vba you will see what sheet number you are refering.

Will referencing by sheet number will work for you:

Xl.sheets(1).select

 

[JHB]

You say it runs okay sometimes and sometimes not, is it each second time, then I'm sure you're missing a reference to the Excel object you've created in your code, so post the whole code in that Sub/Function!

 

[static]

If there's a chance you have more than one workbook open you need to specify which one you're using.
myworkbook.sheets("Proposed Area Plan").Select

Selecting is a gui action.
You don't need to select an object to do something with it.
Everytime you select something you force the screen to refresh which slows down your code. It also invites other potential errors if objects are hidden because you can't select something you can't see.

 

[arnelgp]

Good eye sight there.