Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing sheet names to an array
I am passing sheet names to an array. 4 sheets from one open file and 4
sheets from another open file. I open the second file in the routine using the GetOpenFile stmt. I have named the sheets in VBA. What I am trying to do is pass the Name property of the named sheet to the array as seen below. However, the first set of names are causing a "Run Time 424 Error" when I try to pass them to the array called ExportSheetNameArray. Now I know that the names exist by looking at the project properties of each sheet in both files through the properties and project windows. When I check in the immediate window I only get names for the second set of names (seems consistent withthe error I am getting). But why. Why do the second set pass properly to the second array (ImportSheetNameArray) and not the first set (ExportSheetNameArray)? ?SecExpSht.Name ?UnsecExpSht.Name ?CarExpSht.Name ?RetExpSht.Name ?SecImpSht.Name Secured Import Sheet ?UnsecImpSht.Name Unsecured Import Sheet ?CarImpSht.Name Cars Import Sheet Dim ExportSheetNameArray(0 To 3) Dim ImportSheetNameArray(0 To 3) FName = Application.GetOpenFilename("Excel Files (*.xls), *.xls") Workbooks.Open Filename:=FName 'Pass names of 4 export sheets to array ExportSheetNameArray(0) = SecExpSht.Name ExportSheetNameArray(1) = UnsecExpSht.Name ExportSheetNameArray(2) = CarExpSht.Name ExportSheetNameArray(3) = RetExpSht.Name 'Pass names of 4 import sheets to array ImportSheetNameArray(0) = SecImpSht.Name ImportSheetNameArray(1) = UnsecImpSht.Name ImportSheetNameArray(2) = CarImpSht.Name ImportSheetNameArray(3) = RetImpSht.Name |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing sheet names to an array
First of all, thanks for GetOpenFilename (I hadn't come across that). The
problem you're having is in accessing an Excel worksheet's name by way of its class module (what you see in the VB Editor). You'll have better results accessing the Excel worksheet directly, i.e. using Worksheets(1) for the first worksheet and so on. You can also use Worksheets("SecExpSht") to access a sheet. Hope that helps. "ExcelMonkey" wrote: I am passing sheet names to an array. 4 sheets from one open file and 4 sheets from another open file. I open the second file in the routine using the GetOpenFile stmt. I have named the sheets in VBA. What I am trying to do is pass the Name property of the named sheet to the array as seen below. However, the first set of names are causing a "Run Time 424 Error" when I try to pass them to the array called ExportSheetNameArray. Now I know that the names exist by looking at the project properties of each sheet in both files through the properties and project windows. When I check in the immediate window I only get names for the second set of names (seems consistent withthe error I am getting). But why. Why do the second set pass properly to the second array (ImportSheetNameArray) and not the first set (ExportSheetNameArray)? ?SecExpSht.Name ?UnsecExpSht.Name ?CarExpSht.Name ?RetExpSht.Name ?SecImpSht.Name Secured Import Sheet ?UnsecImpSht.Name Unsecured Import Sheet ?CarImpSht.Name Cars Import Sheet Dim ExportSheetNameArray(0 To 3) Dim ImportSheetNameArray(0 To 3) FName = Application.GetOpenFilename("Excel Files (*.xls), *.xls") Workbooks.Open Filename:=FName 'Pass names of 4 export sheets to array ExportSheetNameArray(0) = SecExpSht.Name ExportSheetNameArray(1) = UnsecExpSht.Name ExportSheetNameArray(2) = CarExpSht.Name ExportSheetNameArray(3) = RetExpSht.Name 'Pass names of 4 import sheets to array ImportSheetNameArray(0) = SecImpSht.Name ImportSheetNameArray(1) = UnsecImpSht.Name ImportSheetNameArray(2) = CarImpSht.Name ImportSheetNameArray(3) = RetImpSht.Name |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing sheet names to an array
But why is it working on the second set of sheets? To be clear, I have named
each sheet in VBA and they also have names in Excel. The idea being that I do not want my code to fail if the user changes the sheets name in Excel. For example the sheet in Excel which is called "Secured Import Sheet" is named "SecExpSht" in VBA. Hence I am trying to pass the sheet name to an array. I opted for SecExpSht.Name which when you look at the immediate window gives me "Secured Import Sheet". But this does not seem to be working for the first set of sheets. EM "Martin" wrote: First of all, thanks for GetOpenFilename (I hadn't come across that). The problem you're having is in accessing an Excel worksheet's name by way of its class module (what you see in the VB Editor). You'll have better results accessing the Excel worksheet directly, i.e. using Worksheets(1) for the first worksheet and so on. You can also use Worksheets("SecExpSht") to access a sheet. Hope that helps. "ExcelMonkey" wrote: I am passing sheet names to an array. 4 sheets from one open file and 4 sheets from another open file. I open the second file in the routine using the GetOpenFile stmt. I have named the sheets in VBA. What I am trying to do is pass the Name property of the named sheet to the array as seen below. However, the first set of names are causing a "Run Time 424 Error" when I try to pass them to the array called ExportSheetNameArray. Now I know that the names exist by looking at the project properties of each sheet in both files through the properties and project windows. When I check in the immediate window I only get names for the second set of names (seems consistent withthe error I am getting). But why. Why do the second set pass properly to the second array (ImportSheetNameArray) and not the first set (ExportSheetNameArray)? ?SecExpSht.Name ?UnsecExpSht.Name ?CarExpSht.Name ?RetExpSht.Name ?SecImpSht.Name Secured Import Sheet ?UnsecImpSht.Name Unsecured Import Sheet ?CarImpSht.Name Cars Import Sheet Dim ExportSheetNameArray(0 To 3) Dim ImportSheetNameArray(0 To 3) FName = Application.GetOpenFilename("Excel Files (*.xls), *.xls") Workbooks.Open Filename:=FName 'Pass names of 4 export sheets to array ExportSheetNameArray(0) = SecExpSht.Name ExportSheetNameArray(1) = UnsecExpSht.Name ExportSheetNameArray(2) = CarExpSht.Name ExportSheetNameArray(3) = RetExpSht.Name 'Pass names of 4 import sheets to array ImportSheetNameArray(0) = SecImpSht.Name ImportSheetNameArray(1) = UnsecImpSht.Name ImportSheetNameArray(2) = CarImpSht.Name ImportSheetNameArray(3) = RetImpSht.Name |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing sheet names to an array
I think it's all to do with which project (file) you're running the macro
from: your code is only recognising modules in the project where the code actually resides. I'd like to think there's a way of accessing another project's module - a sort of VBA anologue to Workbooks("other book").Worksheets("Sheet1") - but I don't know it! Sorry. "ExcelMonkey" wrote: But why is it working on the second set of sheets? To be clear, I have named each sheet in VBA and they also have names in Excel. The idea being that I do not want my code to fail if the user changes the sheets name in Excel. For example the sheet in Excel which is called "Secured Import Sheet" is named "SecExpSht" in VBA. Hence I am trying to pass the sheet name to an array. I opted for SecExpSht.Name which when you look at the immediate window gives me "Secured Import Sheet". But this does not seem to be working for the first set of sheets. EM "Martin" wrote: First of all, thanks for GetOpenFilename (I hadn't come across that). The problem you're having is in accessing an Excel worksheet's name by way of its class module (what you see in the VB Editor). You'll have better results accessing the Excel worksheet directly, i.e. using Worksheets(1) for the first worksheet and so on. You can also use Worksheets("SecExpSht") to access a sheet. Hope that helps. "ExcelMonkey" wrote: I am passing sheet names to an array. 4 sheets from one open file and 4 sheets from another open file. I open the second file in the routine using the GetOpenFile stmt. I have named the sheets in VBA. What I am trying to do is pass the Name property of the named sheet to the array as seen below. However, the first set of names are causing a "Run Time 424 Error" when I try to pass them to the array called ExportSheetNameArray. Now I know that the names exist by looking at the project properties of each sheet in both files through the properties and project windows. When I check in the immediate window I only get names for the second set of names (seems consistent withthe error I am getting). But why. Why do the second set pass properly to the second array (ImportSheetNameArray) and not the first set (ExportSheetNameArray)? ?SecExpSht.Name ?UnsecExpSht.Name ?CarExpSht.Name ?RetExpSht.Name ?SecImpSht.Name Secured Import Sheet ?UnsecImpSht.Name Unsecured Import Sheet ?CarImpSht.Name Cars Import Sheet Dim ExportSheetNameArray(0 To 3) Dim ImportSheetNameArray(0 To 3) FName = Application.GetOpenFilename("Excel Files (*.xls), *.xls") Workbooks.Open Filename:=FName 'Pass names of 4 export sheets to array ExportSheetNameArray(0) = SecExpSht.Name ExportSheetNameArray(1) = UnsecExpSht.Name ExportSheetNameArray(2) = CarExpSht.Name ExportSheetNameArray(3) = RetExpSht.Name 'Pass names of 4 import sheets to array ImportSheetNameArray(0) = SecImpSht.Name ImportSheetNameArray(1) = UnsecImpSht.Name ImportSheetNameArray(2) = CarImpSht.Name ImportSheetNameArray(3) = RetImpSht.Name |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XL2007 and array of sheet names? | Excel Discussion (Misc queries) | |||
Array of all selected sheet names? | Excel Programming | |||
Passing Worksheet Names as Variables | Excel Programming | |||
Load an array with Sheet names | Excel Programming | |||
Sheet Names Array | Excel Programming |