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 here:
http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/
Thanks for your help!
