How do I specify a worksheet within the excel file path?

[Guest]

I'm using an Output TO macro to export data to a specific worksheet with an
excel file. What is the format for specifiying the worksheet in the File Path.

 

[Nikos Yannacopoulos]

No can do with OutputTo. Use TransferSpreadsheet instead, use the Range
argument to supply the worksheet name. If it exists it will be overwritten,
if not it wil be created (Note: this is not documented inAccess help, where
it says thed Range argument is not used in Export!).

HTH,
Nikos

I'm using an Output TO macro to export data to a specific worksheet with an
excel file. What is the format for specifiying the worksheet in the File

Path.

 

[Jamie Collins]

Use TransferSpreadsheet instead, use the Range
argument to supply the worksheet name. If it exists it will be overwritten,
if not it wil be created

Incorrect. To demo, create a workbook containing a single worksheet
named Sheet1 and try specifying "Sheet99$" for the Range argument. You
will get an error. Now create a sheet named Sheet99, close and save
then repeat using "Sheet99$" for the Range argument. This time no
error. Also, note an existing sheet is not *overwritten*; rather, its
contents are cleared and the new data inserted below the existing
UsedRange i.e. the part that contained data before it was cleared.

Jamie.

--

 

[Nikos Yannacopoulos]

Jamie,

You have taken this to a whole new level! I followed your suggestion and
indeed it behaved just like you said. This got me thinking the dollar sign
may just be a switch to instruct the export to append rather than overwrite
(which is what my original suggestion does), only it's not working properly,
clearing out pre-existing data. The reason why the first run failed is only
the absence of Sheet99 at the time, so trying to append to the cyberspace...
a reasonable deduction?

Then I thought to myself, if this is a switch, maybe there are more! I tried
every special character that comes from Shift+Numeric Key on the main pad,
plus some of the non-accepted ones (/, \, ?, *) and found out that cross-app
transfer (accepting both Excel allowed and non-allowed on the Access
side)won't handle them well, they all get accross as an underscore - with
the exception of $ which behaves like you described. The only non-accepted
ones that Access rejected too were [ and ] , and the error message was the
name doesn't follow Access's naming rules, so nothing to do with Excel
acceptance of them.

Bottomline, the Range argument sems to be doing what I originally said as
long as one refrains from using non alphanumeric characters. It seems there
has been an unsuccessful attempt to build in an "append" functionality
(unless it was just an accidental side effect), and there is definitely a
problem in the handling of non-alphanumeric characters. Overall, an
incomplete interface. I think I know now why Range is undocumented!

Thanks for enhancing my knowledge on this.

Nikos


with the ambersand instead, and guess what, the result was a new sheet
called "Sheet99_"!

 

[Jamie Collins]

Bottomline, the Range argument sems to be doing what I originally said as
long as one refrains from using non alphanumeric characters.

No, you are not quite at the bottomline yet <g>.

If you don't use a $ you aren't specifying a worksheet name at all,
rather you are actually specifying an Excel 'named range' (technically
a workbook-level defined Name object created using a simple range
address formula).

As before, try using a workbook containing a single sheet called
Sheet1 but create a 'named range' called Sheet99 and specify "Sheet99"
(no $) in the TransferSpreadsheet Range argument: it runs without
error and the data appears in the 'named range' on Sheet1.

So why does a sheet get created when you specify a 'named range' that
does not exist. Try this:

SELECT *
INTO [Excel 8.0;Database=C:\NonExistent.xls;].[Sheet99$]
FROM MyTable;

You get the error about Sheet99$ not being a valid name (or should
that be Name <g>?) So you can't specify a sheet name using this syntax
either. The only kind of table you can create this way is a 'named
range', so now try this:

SELECT *
INTO [Excel 8.0;Database=C:\NonExistent.xls;].Sheet99
FROM MyTable;

and examine the file that has been created. It will have a sheet named
Sheet99 and a 'named range' to match. In other words, it did exactly
the same as TransferSpreadsheet except the query left the 'named
'range' intact whereas TransferSpreadsheet removed it.

If you want things to get really weird, try creating a workbook with
one sheet named Sheet1 and a defined Name named Sheet99 but instead of
defining a 'named range' give it another kind of formula e.g. ="Hello"
(in Excel, Insert, Name, Define and type the formula in the Refers To
textbox). Not surprisingly, using TransferSpreadsheet with "Sheet99"
generates a 'could not find the object' error but now take a look at
the workbook: when I tried this with a one column query it created a
sheet named Sheet99, changed my defined Name definition to
=Sheet99!$A$1:BC$1 (why column BC? why not $BC?) but inserted no
column headers and no data. I then deleted the sheet, reinstated the
="Hello" definition and tried the same code again: this time the range
was defined as =Sheet99!$A$1:AW$1 and I got the column header but
again no data.

This inconsistent behavior is probably the reason why it is omitted
from the help. But my point is, when the sheet does not exist and you
supply a name, it is a 'named range' that is created; the worksheet of
the same name is merely a by-product of this process and for some
reason the 'named range' definition is removed when
TransferSpreadsheet is successfully completed. One final 'proof' of my
theory: create a workbook with one sheet named Sheet99 with Hello
entered in cell A1 but no 'named ranges' at all. Try
TransferSpreadsheet with "Sheet99" as usual and it runs without error.
Examine the workbook and you'll see a new sheet named Sheet991 with a
'named range' named Sheet99 containing the data. So what happened? The
Excel driver knows the 'named range' Sheet99 does not exist so it
needs to create it. It knows there is an existing worksheet named
Sheet99 and it would normally create the 'named range' here. However,
worksheet Sheet99 already contains data so the driver cannot use it.
Instead it creates a new sheet which requires a unique name, which the
drive does by appending a 1 to the name. The 'named range' is not
taken so the original name of Sheet99 is used.

Phew! I hope the above convinces you that TransferSpreadsheet cannot
be used to create a worksheet; it can only create a defined name (if
one of the same name does not already exist) and worksheet of the same
name (if one of the same name does not already exist). The reason
alphanumeric characters didn't work for you is that, while they are
legal in a worksheet's name, they are illegal a defined Name's name.

Jamie.

--

 

[Nikos Yannacopoulos]

Well, what can I say? Thanks again!

Nikos

Bottomline, the Range argument sems to be doing what I originally said as
long as one refrains from using non alphanumeric characters.

No, you are not quite at the bottomline yet <g>.

If you don't use a $ you aren't specifying a worksheet name at all,
rather you are actually specifying an Excel 'named range' (technically
a workbook-level defined Name object created using a simple range
address formula).

As before, try using a workbook containing a single sheet called
Sheet1 but create a 'named range' called Sheet99 and specify "Sheet99"
(no $) in the TransferSpreadsheet Range argument: it runs without
error and the data appears in the 'named range' on Sheet1.

So why does a sheet get created when you specify a 'named range' that
does not exist. Try this:

SELECT *
INTO [Excel 8.0;Database=C:\NonExistent.xls;].[Sheet99$]
FROM MyTable;

You get the error about Sheet99$ not being a valid name (or should
that be Name <g>?) So you can't specify a sheet name using this syntax
either. The only kind of table you can create this way is a 'named
range', so now try this:

SELECT *
INTO [Excel 8.0;Database=C:\NonExistent.xls;].Sheet99
FROM MyTable;

and examine the file that has been created. It will have a sheet named
Sheet99 and a 'named range' to match. In other words, it did exactly
the same as TransferSpreadsheet except the query left the 'named
'range' intact whereas TransferSpreadsheet removed it.

If you want things to get really weird, try creating a workbook with
one sheet named Sheet1 and a defined Name named Sheet99 but instead of
defining a 'named range' give it another kind of formula e.g. ="Hello"
(in Excel, Insert, Name, Define and type the formula in the Refers To
textbox). Not surprisingly, using TransferSpreadsheet with "Sheet99"
generates a 'could not find the object' error but now take a look at
the workbook: when I tried this with a one column query it created a
sheet named Sheet99, changed my defined Name definition to
=Sheet99!$A$1:BC$1 (why column BC? why not $BC?) but inserted no
column headers and no data. I then deleted the sheet, reinstated the
="Hello" definition and tried the same code again: this time the range
was defined as =Sheet99!$A$1:AW$1 and I got the column header but
again no data.

This inconsistent behavior is probably the reason why it is omitted
from the help. But my point is, when the sheet does not exist and you
supply a name, it is a 'named range' that is created; the worksheet of
the same name is merely a by-product of this process and for some
reason the 'named range' definition is removed when
TransferSpreadsheet is successfully completed. One final 'proof' of my
theory: create a workbook with one sheet named Sheet99 with Hello
entered in cell A1 but no 'named ranges' at all. Try
TransferSpreadsheet with "Sheet99" as usual and it runs without error.
Examine the workbook and you'll see a new sheet named Sheet991 with a
'named range' named Sheet99 containing the data. So what happened? The
Excel driver knows the 'named range' Sheet99 does not exist so it
needs to create it. It knows there is an existing worksheet named
Sheet99 and it would normally create the 'named range' here. However,
worksheet Sheet99 already contains data so the driver cannot use it.
Instead it creates a new sheet which requires a unique name, which the
drive does by appending a 1 to the name. The 'named range' is not
taken so the original name of Sheet99 is used.

Phew! I hope the above convinces you that TransferSpreadsheet cannot
be used to create a worksheet; it can only create a defined name (if
one of the same name does not already exist) and worksheet of the same
name (if one of the same name does not already exist). The reason
alphanumeric characters didn't work for you is that, while they are
legal in a worksheet's name, they are illegal a defined Name's name.

Jamie.

--