Windows 7 - How do you capture the name of the current worksheet in VBA?

Asked By Paul on 25-Sep-09 01:46 AM
I am using Automation to open and populate fields in an Excel workbook from
Access.  The code I am using is:

Function open_file_in_Excel(strFileSpec As String, intPopulate As Integer)
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wsht As Worksheet
Set appExcel = CreateObject("Excel.Application")
Set wbk = appExcel.Workbooks.Open(strFileSpec)
appExcel.Visible = True

At this point, I have got the workbook open, and I would like to capture the
name of the Active Worksheet.  I have tried various combinations like

Set wsht = Workbooks(strFileSpec).ActiveSheet.name
Set wsht = Application.Workbooks(strFileSpec).ActiveSheet

but none of the expressions I have tried will return the name of the Active
Worksheet.

Previously, I was using the literal name of the worksheet to set the
Worksheet object variable wsht, but I am trying to generalize the code so I
do not have to accumulate multiple procedures for each worksheet, maintain a
Select Case list, or pass the worksheet name as a parameter.

What expression can I use to capture the name of the Active Worksheet in
VBA?

Thanks in advance,

Paul




Kevin Smith replied on 25-Sep-09 02:43 AM
Hello,
Just use

wsht = ActiveSheet.Name

--
Kevin Smith :o)
Paul replied on 25-Sep-09 02:53 AM
I tried that, Kevin.  In fact, as I write this, I have got the code in Break
mode, where I am stepping through it line by line, and after the code
executes

wsht = ActiveSheet.Name

and I hover the cursor over the object variable wsht, the yellow tag says

This is especially puzzling because if I type

?activesheet.name

in the Immediate Window, it displays the name of the Active Worksheet while
the code is suspended in break mode.

Any idea why it would work in the Immediate Window but not as a line of code
in a Function procedure?

Paul
Paul replied on 25-Sep-09 03:21 AM
Ok, I finally got it to work.

I first tried

Set wsht = wbk.Worksheets(ActiveSheet.Name)

and that did not work.

So then I assigned ActiveSheet.Name to a string variable

strActiveWorksheet = ActiveSheet.Name

and then usd the variable in the expression

Set wsht = wbk.Worksheets(strActiveWorksheet)

and it worked.

Thanks for providing me with the solution, Kevin.

Paul
Kevin Smith replied on 25-Sep-09 03:33 AM
No Problem.
--
Kevin Smith :o)
Kevin Smith replied on 25-Sep-09 03:42 AM
Sorry, i have just re-read you first post.

dim wsht as String
wsht = activesheet.name

--
Kevin Smith :o)
Krzysztof Naworyta replied on 25-Sep-09 08:30 AM
| I am using Automation to open and populate fields in an Excel workbook
| from Access.  The code I am using is:
|
| Function open_file_in_Excel(strFileSpec As String, intPopulate As
|    Integer) Dim appExcel As Excel.Application
|    Dim wbk As Excel.Workbook
|    Dim wsht As Worksheet
|    Set appExcel = CreateObject("Excel.Application")
|    Set wbk = appExcel.Workbooks.Open(strFileSpec)
|    appExcel.Visible = True
|
| At this point, I have got the workbook open, and I would like to
| capture the name of the Active Worksheet.  I have tried various
| combinations like
|
|    Set wsht = Workbooks(strFileSpec).ActiveSheet.name
|    Set wsht = Application.Workbooks(strFileSpec).ActiveSheet

Set wsht = appExcel.Workbooks(1).ActiveSheet
or:
Set wsht = appExcel.ActiveSheet

--
KN
Chip Pearson replied on 25-Sep-09 02:47 PM
You can simplify this to

Set wsht = ActiveSheet

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
Paul replied on 28-Sep-09 09:29 AM
Thanks, Krzystof.

I would think the second choice would be better, in case Workbooks(1) is not
the active workbook.  Would you agree?
Paul replied on 28-Sep-09 09:30 AM
Thanks for the suggestion, Chip.
Krzysztof Naworyta replied on 28-Sep-09 01:42 PM
It depends on what you want to do.
Not in this particular situation: you create a blank excel application
instance, then you open some document (workbook). It is only one :) and so
it must be active.

***

You can not use:
Set wsht = Workbooks(strFileSpec).ActiveSheet.name
because you get string, no object

You can not use:
Set wsht = Workbooks(strFileSpec).ActiveSheet
because workbook has his key in workbooks collection equal to the file
name, not to FullFileName (full path)

You can not use:
Set wsht = Application.Workbooks(strFileSpec).ActiveSheet
because application is reference to access.application, not to excel
application
(the reference to access library is the first on the references list!)

Everytime you use automatition try to avoid implicit calling of
objects/methods.
Start with appExcel, write sub-object to variable, and so on, deeper and
deeper...
It is very important when you start using late binding
(Dim wsht as Object, e.g.)

--
KN





| Thanks, Krzystof.
|
| I would think the second choice would be better, in case Workbooks(1)
| is not the active workbook.  Would you agree?
|
|
|
|
||
||    Set wsht = appExcel.Workbooks(1).ActiveSheet
|| or:
||    Set wsht = appExcel.ActiveSheet
||
|| --
|| KN

--
KN

archiwum grupy:
http://groups.google.pl/advanced_search
(grupa: pl*msaccess)
Paul replied on 28-Sep-09 08:36 PM
Krzystzof,

I followed your suggestion, and here is what finally got it working:

Set appExcel = CreateObject("Excel.Application")
Set wbk = appExcel.Workbooks.Open(strFileSpec)
Set wsht = wbk.ActiveSheet

As you said, it is best to start with appExcel and name the Workbook
container before the Worksheet.  Doing it this way, it works every time.
When I tried the shortcuts, it it only worked intermittently.

Thanks for the suggestion and the explanation.

Paul
Paul replied on 28-Sep-09 08:39 PM
Chip,

I found that I also had to include the workbook object to get the desired
results:

Set wsht = wbk.ActiveSheet

otherwise it only worked intermittently.

Please see my reply to Krzysztof Naworyta in another thread in this
discussion for a bit more detail.

Thanks

Paul