[Sorry for the cross-post, but it may be an issue either group may
have the answer to through experience.]

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. Sometimes there will be multiple
sheets in the same workbook, sometimes only a single tab.

The issue is this: Save query "XLTest - qryExport" to file and the
worksheet name becomes "XLTest___qryExport" (three underscores). 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...".

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.

Is it there, or is Access throwing out this message after interpreting
a message thrown back from trying to save the query object?

Thanks in advance. Again, sorry for the cross-post.

Anthony.

(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.

--

Access gives you a failure message using the term
'XLTest - qryExport' because that is what you tried to
export, not because Excel has a sheet of that name.

(david)


"Anthony Cuttitta Jr." wrote in message
om...
[Sorry for the cross-post, but it may be an issue either group may
have the answer to through experience.]

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. Sometimes there will be multiple
sheets in the same workbook, sometimes only a single tab.

The issue is this: Save query "XLTest - qryExport" to file and the
worksheet name becomes "XLTest___qryExport" (three underscores). 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...".

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.

Is it there, or is Access throwing out this message after interpreting
a message thrown back from trying to save the query object?

Thanks in advance. Again, sorry for the cross-post.

Anthony.

(Jamie Collins) wrote in message . com...
(Anthony Cuttitta Jr.) wrote ...
[snip]
- 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.

--

I've already resigned myself to the first point and am implimenting
it. Thanks for your response!

Gotcha. So it's as I assumed. It made the attempt, received an
error, then sent back a response with using the name IT was trying,
not what it found on the other end.

"david epsom dot com dot au" <david@epsomdotcomdotau wrote in message om.au...
Access gives you a failure message using the term
'XLTest - qryExport' because that is what you tried to
export, not because Excel has a sheet of that name.

(david)


"Anthony Cuttitta Jr." wrote in message
om...
[Sorry for the cross-post, but it may be an issue either group may
have the answer to through experience.]

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. Sometimes there will be multiple
sheets in the same workbook, sometimes only a single tab.

The issue is this: Save query "XLTest - qryExport" to file and the
worksheet name becomes "XLTest___qryExport" (three underscores). 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...".

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.

Is it there, or is Access throwing out this message after interpreting
a message thrown back from trying to save the query object?

Thanks in advance. Again, sorry for the cross-post.

Anthony.