How do you capture the name of the current worksheet in VBA?
Hello,
Just use
wsht = ActiveSheet.Name
--
Kevin Smith :o)
"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
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
|