access 2013 vba code to select a named worksheet from an opened and activated workbook ?

The following code works fine in Access 2010 but gets a runtime error in 2013.
   xlWorkBookSalesPivot.Activate  ' works fine  in 2010 and 2013
   xlWorkBookSalesPivot.sheets("Pivot").Select

I tried various alternatives to the SELECT, including the below.  They get 'subscript out of range' errors:
   xlWorkBookSalesPivot.Activate  ' works fine  in 2010 and 2013
   xlExcelApp.worksheets("Pivot").Select

Has anyone got ideas on how to properly select that worksheet?  The 2010 code has run for 2 years, but we must update to run in Access 2013.
Thanks!
bcreenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>The following code works fine in Access 2010 but gets a runtime error in 2013.
   xlWorkBookSalesPivot.Activate  ' works fine  in 2010 and 2013
   xlWorkBookSalesPivot.sheets("Pivot").Select

so is these codes working fine in both 2010 and 2013 or just working fine in 2010?
if it got error in 2013, what's that error message received?

I tried various alternatives to the SELECT, including the below.  They get 'subscript out of range' errors:
   xlWorkBookSalesPivot.Activate  ' works fine  in 2010 and 2013
   xlExcelApp.worksheets("Pivot").Select
make sure that's a worksheet named as Pivot in the target workbook...?
0
NorieAnalyst Assistant Commented:
Why are you trying to activate/select the sheet?

You shouldn't need to.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
how is xlWorkBookSalesPivot dimensioned and assigned? Would you please post the whole procedure? thanks
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

bcreenAuthor Commented:
Norie and Ryan,  thanks for your response.  There IS a worksheet existing -- and 2010 code has run flawlessly for 2+years.

The reason I'm trying to activate then select the particular sheet is so that I can then place some verbiage into 2 of the cells of that "Pivot" sheet:
    ' now place the NAME we are processing into A3
    xlExcelApp.worksheets("Pivot").Range("A3").Value = GetpLevel4Name()
    xlExcelApp.worksheets("Pivot").Range("A4").Value = GetpstrLevel()
    DoEvents: DoEvents

Without the SELECT clause (which fails in 2013) I get RTE 9 - subscript out of range trying to place a value into the "A3" cell in the statement above.

After placing some text into the 2 cells above I then run macro that are stored within the workbook itself:
        ' now run the macro in that workbook which clears filters and sets the filter for THIS L3 or L4 !
        xlExcelApp.Run "SALES_SetFilterTo"    ' it also DELETES a tab named "IndividualPivot" if it exists

I may try decompile, compact and repair, and moving all objects into a fresh accdb ..... (which I've done recently trying to get this program to work in AC2010).  But I doubt this will make much difference....

Thanks for your input!
0
bcreenAuthor Commented:
Sorry Crystal -- overlooked your question.  The code is for a major international company and I am unable to publicly publish much of the code or data without being in violation of my confidentiality agreement.   I'll review how I've dimensioned and defined the various objects.  But this code has run extremely well in AC2010, until file/table sizes have reached the point where 2010 is failing -- but 2013 handles the massive data fine.   'Rock and a hard place' scenario.....   :-)

I suppose this could also be related to Tools|References|libraries used and their order.....
0
NorieAnalyst Assistant Commented:
What happens if you don't activate/select and replace xlExcelApp here with the appropriate workbook reference which I believe is xlWorkBookSalesPivot.
xlExcelApp.worksheets("Pivot").Range("A3").Value = GetpLevel4Name()

Open in new window

0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi,

if you are simply changing values in cells, there is no reason to select anything. Sheets only need to be selected/activated if you are doing something like freezing panes or setting the zoom level

In addition to Norie's suggestion, I wish to add that you might think of dimensioning a variable, assigning it the variable the return value of GetpLevel4Name(), setting the cell value to the variable ... and testing somewhere along the way too -- what if there is no group level 4?

... however, would it not be better to put a .formula into the cell (as opposed to .value) in case the pivot table is changed? something like   =MyOtherSheet!A1 ? ... or ...

____________________________
to fix the main issue at hand, though ...

Most important is Ryan's observation of Worksheet vs Sheet.  A pivot table/chart is a sheet (in the Sheets collection), but it may not be in the Worksheets collection.  Same as a chart sheet is not a worksheet (according to object model) ... nor an old-time macro sheet, ....

Best to use the Sheets collection in code since it can refer to any type of sheet, whereas a Worksheet is a specific type of sheet.  Take a look here at the differences between a Sheet object and a Worksheet object:

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/workbook-object-excel

Worksheets Object   (collection)
https://msdn.microsoft.com/en-us/VBA/Excel-VBA/articles/worksheets-object-excel

Sheets Object (collection)
https://msdn.microsoft.com/en-us/VBA/Excel-VBA/articles/sheets-object-excel

have an awesome day,
crystal
0
bcreenAuthor Commented:
Great scoop here Crystal  -- time constraints often cause us to STOP after getting "something" that works, without actually furthering our actual understanding of what, exactly, we are really DOING -- and why.

Thanks you so much for taking your time to help me out here.  I'm beginning to suspect that "something"  elsewhere is causing what we are seeing in this section of the code.  But I'll try your suggestions and review your  links.

Thanks again!
Bill
1
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome, Bill ~

many times, it is easy to just copy and paste from the macro recorded code, this snippet and that, ... so the blocks of logic are there ... then comes the need* for fine-tuning, error handling, rearranging for performance, adding comments, indenting code ...  

* often triggered by problems (which hopefully we find before our users get tossed into left field)

___________________________________
aside:

It is also a good idea to use "With SomeObject ... End With". Do you do more than one thing with xlWorkBookSalesPivot? If you want to see examples, this video tutorial uses With ActiveCell, With ActiveSheet and inside that, With .cells(nRow, nCol) :

https://www.experts-exchange.com/videos/1498/Fix-Code-to-fill-Missing-data-in-Excel-Loop-through-spreadsheet-in-VBA.html
(this is actually the 2nd video in a 3-part series on error handling, but this video can be watched alone too)

With SomeObject ... think of it this way ...
if you are going to throw a ball up in the air 10 times, do you pick it up from the ground once and then throw it up 10x, or do you pick it up, throw it up in the air, catch it, put it down, ... pick it up, ... which is more efficient? 'With' can eliminate need for some object variables too

have an awesome day,
crystal
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.