ac*******@railvan.com (Anthony Cuttitta Jr.) wrote ...
I'm working on some procedures where Access queries are exported to
Excel, and then later on, those same workbooks are openned, and I need
to target a specific original sheet.
Save query "XLTest - qryExport" to file and the
worksheet name becomes "XLTest___qryExport" (three underscores).
Looking at Worksheet.Name in Excel, I can find "XLTest___qryExport".
I've tried browsing the entire object tree but don't see "XLTest -
qryExport" anywhere.
Here's my test:
Export data from Jet .mdb to Excel .xls:
SELECT
MyIntCol
INTO
[Excel 8.0;HDR=YES;Database=C:\Anthony.xls;].[XLTest - qryExport]
FROM
MyTable
;
Open the newly-created workbook in the Excel UI. It contains one sheet
only. Switch to the Visual Basic Editor and in the Immediate Window:
? ThisWorkbook.Worksheets(1).Name
XLTest___qryExport
? ThisWorkbook.Worksheets("XLTest___qryExport").Inde x
1
? ThisWorkbook.Worksheets(1).CodeName
Sheet1
The sheet code module appears in the VBE Project Explorer as
'<<CodeName>> (<<Name>>)', hence I see it as 'Sheet1
(XLTest___qryExport)'.
All the above results are as I would expect, bearing in mind that
Jet's SELECT..INTO syntax creates a new defined Name and that Excel
does not allow space or hyphen characters in the defined Name's name.
I
have a generic procedure which performs the export, so if exported
multiple times, there is no error. However, if saved manually (File |
Save As...), I'll get "The object 'XLTest - qryExport' already
exists...".
I'm not an MS Access user myself but from what I've read in the
newsgroups I'm going to take a guess that you are using
TransferSpreadsheet (or similar) in VBA code. From reading I also
infer that, under the covers, TransferSpreadsheet issues a DROP TABLE
before issuing a SELECT..INTO, which would explain why you are able
re-run the same code without error. You could probably reproduce
'manually' if you did the DROP TABLE yourself before saving.
All that said, I'm not sure what you are looking for as regards a
solution, so here are some suggestions:
- Use an Excel table name that uses only alphanumeric and underscore
characters
- Don't trust MS Access to run hidden code (especially when that code
is a DROP TABLE!); instead, write your own sql code, preferable using
DROP TABLE, CREATE TABLE and INSERT INTO..SELECT syntax (in that
order) or perhaps CREATE TABLE then SELECT..INTO.
Jamie.
--
