I tried this code (from Access 2000, using reference to Excel 9.0 object
library) and it works fine:

Public Sub Test()

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add

xlSheet.Name = "Year 2004_0"
Debug.Print xlSheet.Name
sn = "Year 2004_0"
lr = xlApp.Workbooks(1).Worksheets(sn) _
.Range("A1").SpecialCells(xlCellTypeLastCell).Row
Debug.Print lr

xlApp.Quit

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

End Sub

Although you suspect the space in the worksheet name is the problem, I am
not so sure. It should not matter. I would suggest going in to debug mode
when you get the error. Make xlApp visible, if it is not (in immediate pane,
type xlApp.Visible = True). Then look to see if the sheet name in your code
matches the actual sheet name.

Also, when using automation it is best to use explicit references to
everything. It is better to use Dim xlApp as Excel.Application than Dim
xlApp as Object, for example.

If none of this helps, post the entire relevant code (including Dim
statements, and how you create xlApp and open your workbook, etc.)

"deko" wrote:

Make that:

sn = "Year 2004_0"


Thanks for the quick reply, but this still generates a "Subscript out of
range" error:

sn = Chr(34) & Year 2004_0 & Chr(34)

lr = xlapp.Workbooks(strXlsFile).Worksheets(sn) _
.Range("A1").SpecialCells(xlCellTypeLastCell).Row