I've read that single quotes are supposed to allow you to reference
worksheet names with spaces:

'Year 2004_0'

But I'm using automation (from Access) and it's not working:

sn = 'Year 2004_0'

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

This returns "Error Number 9: Subscript out of range"
(works fine if no spaces in worksheet name)

I've also tried:

['Year 2004_0']

but no luck.

How can I reference a worksheet name with spaces when using automation?

Thanks in advance.

Hi Deko,

But I'm using automation (from Access) and it's not working:

sn = 'Year 2004_0'

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

Make that:

sn = "Year 2004_0"

When using the Worksheets collection from VBA, the single quotes are
not needed.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

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

no need for the chr(34).. the doublequotes are used
in VB to assign a hardcoded string to a variable.

dim sn as string
dim ws as worksheet

sn = "Year 2004_0"
on error resume next
set ws=activeworkbook.worksheets(sn)
on error goto 0
if ws is nothing then
msgbox "Sheet " & sn & " does not exist in activeworkbook"
endif





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


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

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

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

[snip]

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.

Yes, you are correct. Now I see what is happening.

What I'm doing is exporting tables in one loop (using JET), logging the
sheet names, then calling another function (basChart) to open the workbook
and create a bunch of charts. In basChart, I loop through the logged sheet
names, assigning each one to 'sn' and then do stuff in Excel like this:

xlapp.Workbooks(strXlsFile).Worksheets(sn)

I've found that it's quicker to do it this way (2 loops) rather than trying
to do it all at once - opening the workbook, inserting the data via
automation, and creating the charts all at once is much slower.

But when I do this:

SELECT * INTO [Excel 8.0;Database=C:\FileName.xls].Sheet Name With
Spaces FROM tblExcelData;

JET changes "Sheet Name With Spaces" to "Sheet_Name_With_Spaces"

So my code barfs because the sheet name is different from what I logged in
the first loop.

I tried looping with the worksheet index number 'i' in place of the sheet
name:

xlapp.Workbooks(strXlsFile).Worksheets(i)

but that gets very complicated - sometimes there are preexisting worksheets
in the workbook (before I export the tables) and the recordset of logged
sheet names gets out-of-sync with the worksheets in the workbook which
causes the code to barf (because chart ranges are different from one sheet
to the next).

So I'm not sure how to handle this. If there was a vba function to replace
any spaces found in a string with underscores, I could correct each sheet
name before exporting it and all would be well. I suppose I could write
code to do this, but that would be expensive in terms of processing - I
would have to loop through each character in the string, check it, and
replace it with "_" if it were " ". So if each sheet name had, say 10
characters, and I had 255 worksheets, that's 2550 iterations. But maybe
that's not so bad.

Other ideas?

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.

Yes, I understand this. In fact there is helpful info about this he

http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/

Thanks for your help!

Hi Deko,

If there was a vba function to replace
any spaces found in a string with underscores,

VBA6 (as from Office 2000) has a Replace function built in.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

In article ,
Jan Karel Pieterse wrote:

If there was a vba function to replace
any spaces found in a string with underscores,

VBA6 (as from Office 2000) has a Replace function built in.

And with VBA5, you can use Application.Substitute()

If there was a vba function to replace
any spaces found in a string with underscores,

VBA6 (as from Office 2000) has a Replace function built in.

And with VBA5, you can use Application.Substitute()

This seems to be working:

strSheetName = Replace(strSheetName, " ", "_")

Hi JE,

And with VBA5, you can use Application.Substitute()

Correct. Since we're talking Access VBA here it is
xlApp.WorksheetFunction.Substitute().

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

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.

Does this mean it's also better to do this:

Dim xlChart As Excel.Chart

rather than:

Dim objChart As Object

?

I'm currently doing this:

Dim objChart As Object
Set objBBChart = xlapp.Workbooks(strXlsFile).Worksheets(sn).ChartOb jects.Add
_
(Left:=12.75, Top:=lngTop, Width:=650, Height:=497.25).Chart
objChart.SetSourceData Source:=xlapp.Workbooks(strXlsFile). _
Worksheets(sn).Range(rst!str2), PlotBy:=xlColumns
objChart.ChartType = xlLineMarkers
objChart .. etc, etc ...


Would it be better to do this:

Dim xlChart As Excel.Chart
Set xlChart = xlapp.Workbooks(strXlsFile).Worksheets(sn).ChartOb jects.Add _
(Left:=12.75, Top:=lngTop, Width:=650, Height:=497.25).Chart
xlChart.SetSourceData Source:=xlapp.Workbooks(strXlsFile). _
Worksheets(sn).Range(rstBB!str2), PlotBy:=xlColumns
xlChart.ChartType = xlLineMarkers
xlChart .. etc, etc ...

??

Hi Deko,

Does this mean it's also better to do this:

Dim xlChart As Excel.Chart

rather than:

Dim objChart As Object


Yes, provided that you are planning to keep a reference to Excel in
your project.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Yes, provided that you are planning to keep a reference to Excel in
your project.

Is the issue about avoiding creation of a global reference that can result
in a lingering instance of Excel when xlapp = Nothing fails to close Excel?

As for using Excel.Chart... how?

I can't seem to get this to work:

Dim xlChart As Excel.Chart
Set xlChart = xlapp.Workbooks(strXlsFile).Worksheets(sn).ChartOb jects.Add _
(Left:=12.75, Top:=lngTop, Width:=650, Height:=497.25).Chart
xlChart.SetSourceData Source:=xlapp.Workbooks(strXlsFile). _
Worksheets(sn).Range(rst!str2), PlotBy:=xlColumns
xlChart.ChartType = xlLineMarkers
xlChart .. etc, etc ...

Is this code correct?

would it not be easier for your code (and my eyes)
to create a worksheet and/or range variable?

afaik:
the choice of early binding (With a referenced library) or late binding
is NOT related to "lingering object references".

It is done to prevent problems with different versions of similar
libraries e.g. Excel 9 or Excel 11





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


deko wrote :

Yes, provided that you are planning to keep a reference to Excel in
your project.

Is the issue about avoiding creation of a global reference that can
result in a lingering instance of Excel when xlapp = Nothing fails to
close Excel?

As for using Excel.Chart... how?

I can't seem to get this to work:

Dim xlChart As Excel.Chart
Set xlChart =
xlapp.Workbooks(strXlsFile).Worksheets(sn).ChartOb jects.Add _
(Left:=12.75, Top:=lngTop, Width:=650, Height:=497.25).Chart
xlChart.SetSourceData Source:=xlapp.Workbooks(strXlsFile). _
Worksheets(sn).Range(rst!str2), PlotBy:=xlColumns
xlChart.ChartType = xlLineMarkers xlChart .. etc, etc ...

Is this code correct?