Access vba to activate excel sheet by its name


I am looking for access vba script to activate an excel sheet by its name, not by its index number.

Please have a look
Asatoma SadgamayaAnalystAsked:
Who is Participating?

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

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.

Gustav BrockCIOCommented:
That will be:


Open in new window


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
Fabrice LambertFabrice LambertCommented:
Bad practice ….

What have you done so far ?
Can you post some code ?
Mark EdwardsChief Technology OfficerCommented:
o.k.  I just have to put in my 2-cents worth here since one of my specialties is converting Excel-Hell workbooks into Access applications that use Excel only as data input and reporting output media.

Since you usually don't have Excel visible when working with it in Access, it's a good practice to use Excel object variables instead of "relative" references such as "ActiveWorkbook" or "ActiveWorksheet".  What keeps tripping people up is loosing track of what object is "active".
 (to keep things simple, I'll give early binding examples):
Dim objExcel as Excel.Application
Dim objWorkbook AS Excel.Workbook
Dim objSheet AS Excel.Worksheet

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel..Workbooks.Open(FilePathHere)
Set objObject = objWorkbook.Worksheets(WorksheetNameHere)
objSheet.Range("A1").Value = "Headings:"

For some reason, I have found that I need to constantly "activate" an object because of the way VBA handles working with other application objects.  If you get an error message concerning the code not being able to set a cell value because it couldn't find it, or something to that effect, just "activate" the sheet and try again.

There are several ways to do the same thing using Excel VBA in Access and I'm sure you'll see most of them before you settle on a solution.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Fabrice LambertFabrice LambertCommented:
Erm ...

There must be something wrong (implcit references ?), when automating any application in the background (excel, word or whatever), there is no need to activate anything. Especially with explicit references.
Mark EdwardsChief Technology OfficerCommented:
Let me put it this way.  There have been instances where I have tried to do something with a cell on a worksheet object, only to get an error saying that it couldn't do it for some reason.  What fixed it was putting one line of code ahead of that line and that code line was:
where "objSheet" was the object variable for the worksheet in question.  It wouldn't work without it, but did work with it.

I've had other developers come to me with the same mystery situation... they couldn't figure out what was wrong, so I had them insert this line in front of the one that was failing and it fixed it.

There may be some other reason why the read/write line was failing, but that fixed it, and I don't argue with success.
Next time I encounter it, I'll get the specifics so I can duplicate the error.

Another thing any reader of these forums will soon notice is how many "shouldn't be" assumptions turn out to be "actually, yes it is..."
Asatoma SadgamayaAnalystAuthor Commented:
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.