About Us

Jamie Collins

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

--

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
excel worksheet export into access table	H. Chudhary	Excel Worksheet Functions	0	January 23rd 08 02:26 PM
export access to excel. change access & update excel at same time	fastcar	Excel Discussion (Misc queries)	0	June 24th 05 09:27 PM
Finding real values of a function	ruralkansas	Excel Worksheet Functions	1	October 30th 04 09:14 AM
Export Worksheet to Access	R. Choate	Excel Programming	2	August 10th 03 01:13 AM
Export Worksheet to Access	Sigmund[_2_]	Excel Programming	2	August 9th 03 10:10 PM

Menu

About Us