Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming
|
|||
|
|||
How do you capture the name of the current worksheet in VBA?
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 |
#2
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming
|
|||
|
|||
How do you capture the name of the current worksheet in VBA?
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 "Kevin Smith" wrote in message ... 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 |
#4
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming
|
|||
|
|||
How do you capture the name of the current worksheet in VBA?
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 |
#5
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming
|
|||
|
|||
How do you capture the name of the current worksheet in VBA?
No Problem.
-- Kevin Smith :o) "Paul" wrote: 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 |
#6
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming
|
|||
|
|||
How do you capture the name of the current worksheet in VBA?
Sorry, i have just re-read you first post.
dim wsht as String wsht = activesheet.name -- Kevin Smith :o) "Paul" wrote: 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 "Kevin Smith" wrote in message ... 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 |
#7
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming
|
|||
|
|||
How do you capture the name of the current worksheet in VBA?
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 -- KN |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you capture the name of the current worksheet in VBA?
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) On Fri, 25 Sep 2009 00:21:15 -0700, "Paul" wrote: 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 |
#9
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming
|
|||
|
|||
How do you capture the name of the current worksheet in VBA?
Thanks, Krzystof.
I would think the second choice would be better, in case Workbooks(1) isn't the active workbook. Would you agree? "Krzysztof Naworyta" wrote in message Set wsht = appExcel.Workbooks(1).ActiveSheet or: Set wsht = appExcel.ActiveSheet -- KN |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you capture the name of the current worksheet in VBA?
Thanks for the suggestion, Chip.
"Chip Pearson" wrote in message ... 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) On Fri, 25 Sep 2009 00:21:15 -0700, "Paul" wrote: 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 |
#11
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming
|
|||
|
|||
How do you capture the name of the current worksheet in VBA?
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 napisał | Thanks, Krzystof. | | I would think the second choice would be better, in case Workbooks(1) | isn't the active workbook. Would you agree? | | | | "Krzysztof Naworyta" wrote in message | || || Set wsht = appExcel.Workbooks(1).ActiveSheet || or: || Set wsht = appExcel.ActiveSheet || || -- || KN -- KN archiwum grupy: http://groups.google.pl/advanced_search (grupa: pl*msaccess) |
#12
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming
|
|||
|
|||
How do you capture the name of the current worksheet in VBA?
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 "Krzysztof Naworyta" wrote in message ... 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 napisał | Thanks, Krzystof. | | I would think the second choice would be better, in case Workbooks(1) | isn't the active workbook. Would you agree? | | | | "Krzysztof Naworyta" wrote in message | || || Set wsht = appExcel.Workbooks(1).ActiveSheet || or: || Set wsht = appExcel.ActiveSheet || || -- || KN -- KN archiwum grupy: http://groups.google.pl/advanced_search (grupa: pl*msaccess) |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you capture the name of the current worksheet in VBA?
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 "Chip Pearson" wrote in message ... 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) On Fri, 25 Sep 2009 00:21:15 -0700, "Paul" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Capture Worksheet Change Value | Excel Programming | |||
In a range of months can I capture the most current month entry? | Excel Discussion (Misc queries) | |||
Capture current WS Name? | Excel Programming | |||
Capture a worksheet in VBA | Excel Programming | |||
Capture Current Selection in a Combo Box | Excel Programming |