Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to a worksheet using variable
Excel 8.0
I am testing a means of identifying / selecting (and then copying) a worksheet within the current workbook. The report sheets use a standard naming format - Report Name - Mmm-YY e.g. "Sales Report - Dec-03", "Orders In Report - Dec-03", "Orders Out Report - Dec-03" etc etc I am asking the code to confirm the sheets existence. Sub CheckReport() Dim ReportSheetMonth As String, ReportSheetType as String, ReportSheetName as String 'Users enter the Month Required ReportSheetMonth = UCase(InputBox("Please enter the Month-Year of the reports you wish to check [mmm-yy]" & Chr$(13) & Chr$(13) & "eg Mar-04", "Report Period")) ' I have other code here that checks the validity of the entry. An example of an entry would be say "Dec-03" ReportSheetType = "Sales Report - " ReportSheetName = ReportSheetType & ReportSheetMonth If WorksheetExists(ReportSheetName) = True Then _ ' see below GoTo ReportExists MsgBox "Report does NOT exist" ' do what is required as a result Exit Sub TatReportExists: MsgBox "Report does exist" ' do what is required as a result Exit Sub To test for the worksheet's existence I use the following Function. Public Function WorksheetExists(WSName As String, Optional WB As Workbook = Nothing) As Boolean On Error Resume Next WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook, WB).Worksheets(WSName).Name)) End Function The above does not work. But if I paste the actual name of the worksheet (eg Sales Report - Dec-03) directly into the code above i.e. If WorksheetExists("Sales Report - Dec-03") = True Then etc etc it does work. What am I doing wrong here? Any help gratefully appreciated. WSF |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to a worksheet using variable
Hi
The only thing I see is that you use this line GoTo ReportExists But he can't find that because the name = TatReportExists TatReportExists: MsgBox "Report does exist" ' do what is required as a result Exit Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "WSF" wrote in message ... Excel 8.0 I am testing a means of identifying / selecting (and then copying) a worksheet within the current workbook. The report sheets use a standard naming format - Report Name - Mmm-YY e.g. "Sales Report - Dec-03", "Orders In Report - Dec-03", "Orders Out Report - Dec-03" etc etc I am asking the code to confirm the sheets existence. Sub CheckReport() Dim ReportSheetMonth As String, ReportSheetType as String, ReportSheetName as String 'Users enter the Month Required ReportSheetMonth = UCase(InputBox("Please enter the Month-Year of the reports you wish to check [mmm-yy]" & Chr$(13) & Chr$(13) & "eg Mar-04", "Report Period")) ' I have other code here that checks the validity of the entry. An example of an entry would be say "Dec-03" ReportSheetType = "Sales Report - " ReportSheetName = ReportSheetType & ReportSheetMonth If WorksheetExists(ReportSheetName) = True Then _ ' see below GoTo ReportExists MsgBox "Report does NOT exist" ' do what is required as a result Exit Sub TatReportExists: MsgBox "Report does exist" ' do what is required as a result Exit Sub To test for the worksheet's existence I use the following Function. Public Function WorksheetExists(WSName As String, Optional WB As Workbook = Nothing) As Boolean On Error Resume Next WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook, WB).Worksheets(WSName).Name)) End Function The above does not work. But if I paste the actual name of the worksheet (eg Sales Report - Dec-03) directly into the code above i.e. If WorksheetExists("Sales Report - Dec-03") = True Then etc etc it does work. What am I doing wrong here? Any help gratefully appreciated. WSF |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to a worksheet using variable
Hello Ron,
That is a typo on my part. I have correct the error and still no go. The issue is why the code will accept a text string but not my variable. Regards, Bill Fraser "Ron de Bruin" wrote in message ... Hi The only thing I see is that you use this line GoTo ReportExists But he can't find that because the name = TatReportExists TatReportExists: MsgBox "Report does exist" ' do what is required as a result Exit Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "WSF" wrote in message ... Excel 8.0 I am testing a means of identifying / selecting (and then copying) a worksheet within the current workbook. The report sheets use a standard naming format - Report Name - Mmm-YY e.g. "Sales Report - Dec-03", "Orders In Report - Dec-03", "Orders Out Report - Dec-03" etc etc I am asking the code to confirm the sheets existence. Sub CheckReport() Dim ReportSheetMonth As String, ReportSheetType as String, ReportSheetName as String 'Users enter the Month Required ReportSheetMonth = UCase(InputBox("Please enter the Month-Year of the reports you wish to check [mmm-yy]" & Chr$(13) & Chr$(13) & "eg Mar-04", "Report Period")) ' I have other code here that checks the validity of the entry. An example of an entry would be say "Dec-03" ReportSheetType = "Sales Report - " ReportSheetName = ReportSheetType & ReportSheetMonth If WorksheetExists(ReportSheetName) = True Then _ ' see below GoTo ReportExists MsgBox "Report does NOT exist" ' do what is required as a result Exit Sub TatReportExists: MsgBox "Report does exist" ' do what is required as a result Exit Sub To test for the worksheet's existence I use the following Function. Public Function WorksheetExists(WSName As String, Optional WB As Workbook = Nothing) As Boolean On Error Resume Next WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook, WB).Worksheets(WSName).Name)) End Function The above does not work. But if I paste the actual name of the worksheet (eg Sales Report - Dec-03) directly into the code above i.e. If WorksheetExists("Sales Report - Dec-03") = True Then etc etc it does work. What am I doing wrong here? Any help gratefully appreciated. WSF |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to a worksheet using variable
Hi Bill
This is working for me Sub CheckReport() Dim ReportSheetMonth As String, ReportSheetType As String, ReportSheetName As String ReportSheetMonth = UCase(InputBox("Please enter the Month-Year of the reports you wish to check [mmm-yy]" & Chr$(13) & Chr$(13) _ & "eg Mar-04", "Report Period")) ReportSheetType = "Sales Report - " ReportSheetName = ReportSheetType & ReportSheetMonth If WorksheetExists(ReportSheetName) = True Then MsgBox "Report does exist" Else MsgBox "Report does NOT exist" Exit Sub End If End Sub Public Function WorksheetExists(WSName As String, Optional WB As Workbook = Nothing) As Boolean On Error Resume Next WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook, WB).Worksheets(WSName).Name)) End Function -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "WSF" wrote in message ... Hello Ron, That is a typo on my part. I have correct the error and still no go. The issue is why the code will accept a text string but not my variable. Regards, Bill Fraser "Ron de Bruin" wrote in message ... Hi The only thing I see is that you use this line GoTo ReportExists But he can't find that because the name = TatReportExists TatReportExists: MsgBox "Report does exist" ' do what is required as a result Exit Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "WSF" wrote in message ... Excel 8.0 I am testing a means of identifying / selecting (and then copying) a worksheet within the current workbook. The report sheets use a standard naming format - Report Name - Mmm-YY e.g. "Sales Report - Dec-03", "Orders In Report - Dec-03", "Orders Out Report - Dec-03" etc etc I am asking the code to confirm the sheets existence. Sub CheckReport() Dim ReportSheetMonth As String, ReportSheetType as String, ReportSheetName as String 'Users enter the Month Required ReportSheetMonth = UCase(InputBox("Please enter the Month-Year of the reports you wish to check [mmm-yy]" & Chr$(13) & Chr$(13) & "eg Mar-04", "Report Period")) ' I have other code here that checks the validity of the entry. An example of an entry would be say "Dec-03" ReportSheetType = "Sales Report - " ReportSheetName = ReportSheetType & ReportSheetMonth If WorksheetExists(ReportSheetName) = True Then _ ' see below GoTo ReportExists MsgBox "Report does NOT exist" ' do what is required as a result Exit Sub TatReportExists: MsgBox "Report does exist" ' do what is required as a result Exit Sub To test for the worksheet's existence I use the following Function. Public Function WorksheetExists(WSName As String, Optional WB As Workbook = Nothing) As Boolean On Error Resume Next WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook, WB).Worksheets(WSName).Name)) End Function The above does not work. But if I paste the actual name of the worksheet (eg Sales Report - Dec-03) directly into the code above i.e. If WorksheetExists("Sales Report - Dec-03") = True Then etc etc it does work. What am I doing wrong here? Any help gratefully appreciated. WSF |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to a worksheet using variable
Thanks for your help Ron.
Regards, WSF "Ron de Bruin" wrote in message ... Hi Bill This is working for me Sub CheckReport() Dim ReportSheetMonth As String, ReportSheetType As String, ReportSheetName As String ReportSheetMonth = UCase(InputBox("Please enter the Month-Year of the reports you wish to check [mmm-yy]" & Chr$(13) & Chr$(13) _ & "eg Mar-04", "Report Period")) ReportSheetType = "Sales Report - " ReportSheetName = ReportSheetType & ReportSheetMonth If WorksheetExists(ReportSheetName) = True Then MsgBox "Report does exist" Else MsgBox "Report does NOT exist" Exit Sub End If End Sub Public Function WorksheetExists(WSName As String, Optional WB As Workbook = Nothing) As Boolean On Error Resume Next WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook, WB).Worksheets(WSName).Name)) End Function -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "WSF" wrote in message ... Hello Ron, That is a typo on my part. I have correct the error and still no go. The issue is why the code will accept a text string but not my variable. Regards, Bill Fraser "Ron de Bruin" wrote in message ... Hi The only thing I see is that you use this line GoTo ReportExists But he can't find that because the name = TatReportExists TatReportExists: MsgBox "Report does exist" ' do what is required as a result Exit Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "WSF" wrote in message ... Excel 8.0 I am testing a means of identifying / selecting (and then copying) a worksheet within the current workbook. The report sheets use a standard naming format - Report Name - Mmm-YY e.g. "Sales Report - Dec-03", "Orders In Report - Dec-03", "Orders Out Report - Dec-03" etc etc I am asking the code to confirm the sheets existence. Sub CheckReport() Dim ReportSheetMonth As String, ReportSheetType as String, ReportSheetName as String 'Users enter the Month Required ReportSheetMonth = UCase(InputBox("Please enter the Month-Year of the reports you wish to check [mmm-yy]" & Chr$(13) & Chr$(13) & "eg Mar-04", "Report Period")) ' I have other code here that checks the validity of the entry. An example of an entry would be say "Dec-03" ReportSheetType = "Sales Report - " ReportSheetName = ReportSheetType & ReportSheetMonth If WorksheetExists(ReportSheetName) = True Then _ ' see below GoTo ReportExists MsgBox "Report does NOT exist" ' do what is required as a result Exit Sub TatReportExists: MsgBox "Report does exist" ' do what is required as a result Exit Sub To test for the worksheet's existence I use the following Function. Public Function WorksheetExists(WSName As String, Optional WB As Workbook = Nothing) As Boolean On Error Resume Next WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook, WB).Worksheets(WSName).Name)) End Function The above does not work. But if I paste the actual name of the worksheet (eg Sales Report - Dec-03) directly into the code above i.e. If WorksheetExists("Sales Report - Dec-03") = True Then etc etc it does work. What am I doing wrong here? Any help gratefully appreciated. WSF |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to a worksheet using variable
Hi WSF
I see I did not remove the Exit sub in your macro -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "WSF" wrote in message ... Thanks for your help Ron. Regards, WSF "Ron de Bruin" wrote in message ... Hi Bill This is working for me Sub CheckReport() Dim ReportSheetMonth As String, ReportSheetType As String, ReportSheetName As String ReportSheetMonth = UCase(InputBox("Please enter the Month-Year of the reports you wish to check [mmm-yy]" & Chr$(13) & Chr$(13) _ & "eg Mar-04", "Report Period")) ReportSheetType = "Sales Report - " ReportSheetName = ReportSheetType & ReportSheetMonth If WorksheetExists(ReportSheetName) = True Then MsgBox "Report does exist" Else MsgBox "Report does NOT exist" Exit Sub End If End Sub Public Function WorksheetExists(WSName As String, Optional WB As Workbook = Nothing) As Boolean On Error Resume Next WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook, WB).Worksheets(WSName).Name)) End Function -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "WSF" wrote in message ... Hello Ron, That is a typo on my part. I have correct the error and still no go. The issue is why the code will accept a text string but not my variable. Regards, Bill Fraser "Ron de Bruin" wrote in message ... Hi The only thing I see is that you use this line GoTo ReportExists But he can't find that because the name = TatReportExists TatReportExists: MsgBox "Report does exist" ' do what is required as a result Exit Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "WSF" wrote in message ... Excel 8.0 I am testing a means of identifying / selecting (and then copying) a worksheet within the current workbook. The report sheets use a standard naming format - Report Name - Mmm-YY e.g. "Sales Report - Dec-03", "Orders In Report - Dec-03", "Orders Out Report - Dec-03" etc etc I am asking the code to confirm the sheets existence. Sub CheckReport() Dim ReportSheetMonth As String, ReportSheetType as String, ReportSheetName as String 'Users enter the Month Required ReportSheetMonth = UCase(InputBox("Please enter the Month-Year of the reports you wish to check [mmm-yy]" & Chr$(13) & Chr$(13) & "eg Mar-04", "Report Period")) ' I have other code here that checks the validity of the entry. An example of an entry would be say "Dec-03" ReportSheetType = "Sales Report - " ReportSheetName = ReportSheetType & ReportSheetMonth If WorksheetExists(ReportSheetName) = True Then _ ' see below GoTo ReportExists MsgBox "Report does NOT exist" ' do what is required as a result Exit Sub TatReportExists: MsgBox "Report does exist" ' do what is required as a result Exit Sub To test for the worksheet's existence I use the following Function. Public Function WorksheetExists(WSName As String, Optional WB As Workbook = Nothing) As Boolean On Error Resume Next WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook, WB).Worksheets(WSName).Name)) End Function The above does not work. But if I paste the actual name of the worksheet (eg Sales Report - Dec-03) directly into the code above i.e. If WorksheetExists("Sales Report - Dec-03") = True Then etc etc it does work. What am I doing wrong here? Any help gratefully appreciated. WSF |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referring to a variable cell on another sheet | Excel Worksheet Functions | |||
Referring to a variable file name | Excel Discussion (Misc queries) | |||
referring a worksheet | Excel Worksheet Functions | |||
referring to other worksheet | Excel Worksheet Functions | |||
Referring to a local or global variable or constant dynamically? | Excel Programming |