Access vba to activate excel sheet by its name

Asatoma Sadgamaya
Asatoma Sadgamaya used Ask the Experts™
on
Hi

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

Please have a look
Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
That will be:

ActiveWorkbook.Worksheets("YourSheetName").Activate

Open in new window

Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
@Gustav:
Bad practice ….

@Asatoma:
What have you done so far ?
Can you post some code ?
Mark EdwardsChief Technology Officer

Commented:
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.Activate
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.
Ensure you’re charging the right price for your IT

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

Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
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 Officer

Commented:
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:
objSheet.Activate
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..."

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial