Passing sheet names to an array

[ExcelMonkey]

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

[Martin]

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

[ExcelMonkey]

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

[Martin]

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