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

[Paul]

I'm using Automation to open and populate fields in an Excel workbook from
Access. The code I'm 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've got the workbook open, and I would like to capture the
name of the Active Worksheet. I've tried various combinations like

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

but none of the expressions I've 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'm trying to generalize the code so I
don't 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]

Hello,
Just use

wsht = ActiveSheet.Name

 

[Paul]

I tried that, Kevin. In fact, as I write this, I've got the code in Break
mode, where I'm 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
"wsht = Nothing"

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]

Ok, I finally got it to work.

I first tried

Set wsht = wbk.Worksheets(ActiveSheet.Name)

and that didn't 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]

No Problem.

 

[Kevin Smith]

Sorry, i have just re-read you first post.

dim wsht as String
wsht = activesheet.name

 

[Krzysztof Naworyta]

Paul wrote:
| I'm using Automation to open and populate fields in an Excel workbook
| from Access. The code I'm 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've got the workbook open, and I would like to
| capture the name of the Active Worksheet. I've 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

 

[Chip Pearson]

Set wsht = wbk.Worksheets(strActiveWorksheet)

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]

Thanks, Krzystof.

I would think the second choice would be better, in case Workbooks(1) isn't
the active workbook. Would you agree?

 

[Paul]

Thanks for the suggestion, Chip.

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)

 

[Krzysztof Naworyta]

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





Juzer Paul <[email protected]> napisa³
| Thanks, Krzystof.
|
| I would think the second choice would be better, in case Workbooks(1)
| isn't 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]

Krzystzof,

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

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

As you said, it's 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]

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