Identify Escel Worksheet

[GeorgeDurkee]

I am creating a series of worksheets using VBA. based on the worksheet, I turn on and off action buttons and menu items.

I am using access to populate the first worksheet, then using excel vba to manipulate the remaining sheets.

My problem is that as long as Access has control of the workbook, the ActiveSheet commands gets confused and generates an error 91, object variable or with block variable not set of the Active sheet examination.

It works fine if I run it entirely in excel.

the combination of Access and Excel VBA cause the rror.

My question is - how can i tell what sheet i am on without using ActiveSheet in the Excel code?

Thanks

 

[SkipVought]

Hi,

It's usually much better to PULL data from another database INTO Excel rather than PUSH the data from that other application into Excel.

I'd suggest using MS Query in Excel Data/Get External Data/New Query - Acess files -- your database -- and follow the bouncing ball.

Code the Data/Data Range Properties to refresh on open if you like.

THen it can run entirely from Excel.

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ222-2244

 

[GeorgeDurkee]

I can't do it that way. the calculations and data gathering are too complicated to be handled with a simple query data pull. the output form Access is the result of a large number of calculations from a large number of tables. all i want in Excel is the final result.

I can push the data into excel easily. it is only after the data is there that excel refuses to use relative addressing (activesheet). It requires explicit adressing, but I need to know what sheet I'm on, not select it.

 

[RoyVidar]

Have you qualified your reference to the ActiveSheet using your workbook object variable?

[tt]wrk.ActiveSheet[/tt]

Roy-Vidar

 

[SkipVought]

Please post the code that you are having trouble with.

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ222-2244

 

[GeorgeDurkee]

I figured it out. thanks for the help.

here is the original code:
If ActiveSheet.Range("C3").Value = "Preparer (printed)" Then 'Report Sheet
Application.CommandBars.ActiveMenuBar.Controls("IB-input").Controls("Build Report").Enabled = False
Application.CommandBars.ActiveMenuBar.Controls("IB-input").Controls("Print IB's").Enabled = True
Toolbars("IB Input").ToolbarButtons(4).Enab

I used Access to populate the spreadsheet and it would generate the error on the bolded line. if I ran it from Excel, it would work fine. Only when I populated the spreadsheet did it error out.

I then added this:
If thiswokbook.ActiveSheet.Range("C3").Value = "Preparer (printed)" Then 'Report Sheet

the worksheet is now explicitely identified and it appears, at least, to be working.

thanks for the help

 

[PHV]

When you automate Excel from access VBA, ALWAYS qualify the excel objects to avoid the implicit Excel.Application instantiation.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244