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