VBA converts exclamation to dollar in TransferSpreadsheet Export

[ragtopcaddy via AccessMonster.com]

I have a problem exporting via transferspreadsheet. Here's my code:

'Get the query used in the TransferSpreadsheet command
Set qdf = dbLocal.QueryDefs("qryMktStudyRpt")

'Update the query's sql to the new sql
qdf.SQL = strSQL

RngName = shtName & "!" & RngName

'Transfer the data to the named range
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryMktStudyRpt",
strRptPath, False, RngName

shtName is "Denver_ATS" and has no spaces. RngName is "DenverATS" and has no
spaces.

I get the following error msg on the export command:

Run-time error '3125':

'Denver_ATS$DenverATS' is not a valid name...etc.

As you can see, the VBE has taken the liberty of substituting a "$" For my "!
". How do I deal with this? The workbook will end up with several worksheets
and named ranges in the same format.

Thanks,

 

[ragtopcaddy via AccessMonster.com]

Here's how I create the named range:

wkbkRpt.Names.Add Name:=RngName, RefersToR1C1:="='" & shtName & "'!R8C1:
R9C15"

At this point the variable RngName is just the name, "DenverATS", I add the
shtName, "Denver_ATS" and the "!" in front of it just before the
TransferSpreadsheet command and exiting the sub.

I'm thinking that the named ranges in the workbook will all be unique. Maybe
if I make the named range a workbook rather than worksheet level name it
might make things go more smoothly.

The question is, how do I do that?

Also, I am well aware that using named ranges for export is undocumented, but
I've used this technique successfully many times before, although each time
it's been like pulling teeth to get it to work.

 

[Paolo]

Hi ragtopcaddy via AccessMonster.com,

here's what access help says about the range in the TransferSpreadsheet method

range A string expression that's a valid range of cells or the name of a
range in the spreadsheet. This argument applies only to importing. Leave this
argument blank to import the entire spreadsheet.
When you export to a spreadsheet, you must leave this argument blank. If
you enter a range, the export will fail.

The last two lines apply to what you are trying to do.

HTH Paolo

 

[Klatuu]

That is not entirely true. You can export using a valid worksheet name, but
you cannot define a range.

 

[ragtopcaddy via AccessMonster.com]

I have successfully exported to a named range, and the range has expanded to
accommodate the exported records. I have done this many times in the past,
but it's always a chore to get it to work.

That is not entirely true. You can export using a valid worksheet name, but
you cannot define a range.

Hi ragtopcaddy via AccessMonster.com,

[quoted text clipped - 38 lines]

 

[Klatuu]

Really?
I never have. If you figure it out, post back. I would really like to know
how to do that.
--
Dave Hargis, Microsoft Access MVP

I have successfully exported to a named range, and the range has expanded to
accommodate the exported records. I have done this many times in the past,
but it's always a chore to get it to work.

That is not entirely true. You can export using a valid worksheet name, but
you cannot define a range.

Hi ragtopcaddy via AccessMonster.com,

[quoted text clipped - 38 lines]

Thanks,

 

[Guest]

As you can see, the VBE has taken the liberty of substituting

a "$" For my "!

Yes, the correct notation on the Access side is $ for ! and
# for ".". You may have to use the correct notation for some
of the Access/DAO/ADO methods, but all the current
versions of Access are good about correcting ! and"." when
using that DoCmd method. However, you should not depend
on that if you want to be backward compatible with Access
version 2.0.

This is not your problem. Access/VBA knows all about
the fact that Excel displays cell addresses differently than
Access/VBA displays field/control values, and although
you may be confused by the notation, that is not a problem
with Access and Excel.

Your problem is that export has only limited support for
the full range of Excel addressing possibilities. This is
because the Export does not talk to an Excel object, it
works at a lower level, and so even though Excel might
be much smarter, that export is still limited to a very basic
set of database/spreadsheet descriptions.

It may be that an append or create-table query will do
what you want. It is certainly the case that Excel automation
will do what you want.Each of the different methods
of import/export has different limitations, options, and
syntax.

(david)

 

[ragtopcaddy via AccessMonster.com]

Thanks David (and Klaatu),

It appears to me that the TransferSpreadsheet export command doesn't find the
named range 'Denver_ATS$DenverATS', which is not at all surprising given that
the actual name of the range is 'Denver_ATS!DenverATS'.

I am hoping that if I make the names (ie, "DenverATS") workbook level names,
I can leave off the sheet designation entirely and just refer to the name.

The question then becomes, how do I do that? How do I create workbook level
names? It seems that I am creating worksheet level names using the following
line:

wkkbRpt.Names.Add Name:=RngName, RefersToR1C1:="='" & shtName & "'!R8C1:
R9C15"

where RngName="DenverATS" and shtName="Denver_ATS"

How should I alter that line to make the names workbook level names?

Thanks,

Bill

As you can see, the VBE has taken the liberty of substituting
a "$" For my "!

Yes, the correct notation on the Access side is $ for ! and
# for ".". You may have to use the correct notation for some
of the Access/DAO/ADO methods, but all the current
versions of Access are good about correcting ! and"." when
using that DoCmd method. However, you should not depend
on that if you want to be backward compatible with Access
version 2.0.

This is not your problem. Access/VBA knows all about
the fact that Excel displays cell addresses differently than
Access/VBA displays field/control values, and although
you may be confused by the notation, that is not a problem
with Access and Excel.

Your problem is that export has only limited support for
the full range of Excel addressing possibilities. This is
because the Export does not talk to an Excel object, it
works at a lower level, and so even though Excel might
be much smarter, that export is still limited to a very basic
set of database/spreadsheet descriptions.

It may be that an append or create-table query will do
what you want. It is certainly the case that Excel automation
will do what you want.Each of the different methods
of import/export has different limitations, options, and
syntax.

(david)

I have a problem exporting via transferspreadsheet. Here's my code:

[quoted text clipped - 24 lines]

Thanks,

 

[Guest]

It appears to me that the TransferSpreadsheet export command doesn't find

named range 'Denver_ATS$DenverATS', which is not at all surprising given
the actual name of the range is 'Denver_ATS!DenverATS'.

The $ only looks different to you. You are not the computer. Get
past your confusion about that, the $ is not the problem.

The actual name of the range is not 'Denver_ATS!DenverATS' any
more than it is 'Denver_ATS$DenverATS'. Internally, Excel uses
XLOPERs to refer to everything.

(david)

Thanks David (and Klaatu),

It appears to me that the TransferSpreadsheet export command doesn't find the
named range 'Denver_ATS$DenverATS', which is not at all surprising given that
the actual name of the range is 'Denver_ATS!DenverATS'.

I am hoping that if I make the names (ie, "DenverATS") workbook level names,
I can leave off the sheet designation entirely and just refer to the name.

The question then becomes, how do I do that? How do I create workbook level
names? It seems that I am creating worksheet level names using the following
line:

wkkbRpt.Names.Add Name:=RngName, RefersToR1C1:="='" & shtName & "'!R8C1:
R9C15"

where RngName="DenverATS" and shtName="Denver_ATS"

How should I alter that line to make the names workbook level names?

Thanks,

Bill

As you can see, the VBE has taken the liberty of substituting
a "$" For my "!

Yes, the correct notation on the Access side is $ for ! and
# for ".". You may have to use the correct notation for some
of the Access/DAO/ADO methods, but all the current
versions of Access are good about correcting ! and"." when
using that DoCmd method. However, you should not depend
on that if you want to be backward compatible with Access
version 2.0.

This is not your problem. Access/VBA knows all about
the fact that Excel displays cell addresses differently than
Access/VBA displays field/control values, and although
you may be confused by the notation, that is not a problem
with Access and Excel.

Your problem is that export has only limited support for
the full range of Excel addressing possibilities. This is
because the Export does not talk to an Excel object, it
works at a lower level, and so even though Excel might
be much smarter, that export is still limited to a very basic
set of database/spreadsheet descriptions.

It may be that an append or create-table query will do
what you want. It is certainly the case that Excel automation
will do what you want.Each of the different methods
of import/export has different limitations, options, and
syntax.

(david)

I have a problem exporting via transferspreadsheet. Here's my code:

[quoted text clipped - 24 lines]

Thanks,