DoCmd.TransferSpreadsheet acExport, problem

[Piri]

Access97
I am using the following to transfer the contents of a table into an
existing spreadsheet (as a new worksheet).
This has worked for me previously.

DoCmd.TransferSpreadsheet acExport, 8, [ReportData], [ReportName]

where ReportData is the linked table name in the local database and
ReportName is the full path and file name of the spreadsheet.

I am getting Error "3011 - The Microsoft Jet database engine could not
find the object 'ReportData'. Make sure the object exists and that you
spell its name and the path name correctly."

The table does exist - the irony is that the worksheet is created in
the spreadsheet but only the field names from the table appear but no
other data? The data definitely exists in the table.
Why would it create the worksheet with the first line only of the
access table, and not include the rest of the data?
Am I doing anything wrong?

Piri

 

[Douglas J. Steele]

As you're written it, I'm not surprised it won't work. The 3rd and 4th
arguments (where you have [ReportData] and [ReportName]) are supposed to be
string expressions.

If ReportData and ReportName are variables that contain the name of the
table and the full path to the spreadsheet to which you're trying to export,
try removing the square brackets from around them.

If ReportData is actually the name of the table, put it in quotes.

DoCmd.TransferSpreadsheet acExport, 8, "ReportData", "C:\ReportName.xls"

Note that if you do not put a full path in front of the spreadsheet name,
you may have problems finding the resulting spreadsheet, as it will be
written to the current directory, which usually is not the directory where
your MDB resides.

 

[Piri]

Hello Doug,
Thanks for the reply. Yes the arguments are variables.
As I mentioned the code is in fact creating the new worksheet in the
spreadsheet and inserting the first line (the headers/field names)
from the data table. So it seems to be able to recognise the table,
find the spreadsheet, create the new worksheet and insert the header
row, then give up and tell me it cannot find the table? There is some
400 records in the table it seems not to want to move. I have checked
references, and compacted the mbd. Bizarre!

I have reverted back to an older version of this front end and it is
working okay. (It has had several revisions of other reports etc
since, but not this particular one). It works okay.
From what I can find searching error 3011 I see references to corrupt
databases etc. I will copy/ recreate this report code into a new
module in a new mbd first and then bring it back in.

Cheers,
Piri

As you're written it, I'm not surprised it won't work. The 3rd and 4th
arguments (where you have [ReportData] and [ReportName]) are supposed to be
string expressions.

If ReportData and ReportName are variables that contain the name of the
table and the full path to the spreadsheet to which you're trying to export,
try removing the square brackets from around them.

If ReportData is actually the name of the table, put it in quotes.

DoCmd.TransferSpreadsheet acExport, 8, "ReportData", "C:\ReportName.xls"

Note that if you do not put a full path in front of the spreadsheet name,
you may have problems finding the resulting spreadsheet, as it will be
written to the current directory, which usually is not the directory where
your MDB resides.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Access97
I am using the following to transfer the contents of a table into an
existing spreadsheet (as a new worksheet).
This has worked for me previously.

DoCmd.TransferSpreadsheet acExport, 8, [ReportData], [ReportName]

where ReportData is the linked table name in the local database and
ReportName is the full path and file name of the spreadsheet.

I am getting Error "3011 - The Microsoft Jet database engine could not
find the object 'ReportData'. Make sure the object exists and that you
spell its name and the path name correctly."

The table does exist - the irony is that the worksheet is created in
the spreadsheet but only the field names from the table appear but no
other data? The data definitely exists in the table.
Why would it create the worksheet with the first line only of the
access table, and not include the rest of the data?
Am I doing anything wrong?

Piri

 

[Alan]

Hello Doug,
Thanks for the reply. Yes the arguments are variables.
As I mentioned the code is in fact creating the new worksheet in the
spreadsheet and inserting the first line (the headers/field names)
from the data table. So it seems to be able to recognise the table,
find the spreadsheet, create the new worksheet and insert the header
row, then give up and tell me it cannot find the table? There is some
400 records in the table it seems not to want to move. I have checked
references, and compacted the mbd.  Bizarre!

I have reverted back to an older version of this front end and it is
working okay. (It has had several revisions of other reports etc
since, but not this particular one). It works okay.
From what I can find searching error 3011 I see references tocorrupt
databases etc. I will copy/ recreate this report code into a new
module in a new mbd first and then bring it back in.

Cheers,
Piri

As you're written it, I'm not surprised it won't work. The 3rd and 4th
arguments (where you have [ReportData] and [ReportName]) are supposed tobe
string expressions.

If ReportData and ReportName are variables that contain the name of the
table and the full path to the spreadsheet to which you're trying to export,
try removing the square brackets from around them.

If ReportData is actually the name of the table, put it in quotes.

DoCmd.TransferSpreadsheet acExport, 8, "ReportData", "C:\ReportName.xls"

Note that if you do not put a full path in front of the spreadsheet name,
you may have problems finding the resulting spreadsheet, as it will be
written to the current directory, which usually is not the directory where
yourMDBresides.

news:fefd99d9-5e21-4076-8606-7dff9eaf36e1@c19g2000prf.googlegroups.com...

Access97
I am using the following to transfer the contents of a table into an
existing spreadsheet (as a new worksheet).
This has worked for me previously.
DoCmd.TransferSpreadsheet acExport, 8, [ReportData], [ReportName]
where ReportData is the linked table name in the local database and
ReportName is the full path and file name of the spreadsheet.
I am getting Error "3011 - The Microsoft Jet database engine could not
find the object 'ReportData'. Make sure the object exists and that you
spell its name and the path name correctly."
The table does exist - the irony is that the worksheet is created in
the spreadsheet but only the field names from the table appear but no
other data? The data definitely exists in the table.
Why would it create the worksheet with the first line only of the
access table, and not include the rest of the data?
Am I doing anything wrong?
Piri- Hide quoted text -

- Show quoted text -

Hope your method works. But if necessary, you can try a utility called
Advanced Access Repair to repair your Access MDB file. It works rather
well for my corrupt Access mdb files. Its web address is
http://www.datanumen.com/aar/

Alan

 

[DubboPete]

Hello Doug,
Thanks for the reply. Yes the arguments are variables.
As I mentioned the code is in fact creating the new worksheet in the
spreadsheet and inserting the first line (the headers/field names)
from the data table. So it seems to be able to recognise the table,
find the spreadsheet, create the new worksheet and insert the header
row, then give up and tell me it cannot find the table? There is some
400 records in the table it seems not to want to move. I have checked
references, and compacted the mbd.  Bizarre!

I have reverted back to an older version of this front end and it is
working okay. (It has had several revisions of other reports etc
since, but not this particular one). It works okay.
From what I can find searching error 3011 I see references tocorrupt
databases etc. I will copy/ recreate this report code into a new
module in a new mbd first and then bring it back in.

Cheers,
Piri

As you're written it, I'm not surprised it won't work. The 3rd and 4th
arguments (where you have [ReportData] and [ReportName]) are supposed to be
string expressions.
If ReportData and ReportName are variables that contain the name of the
table and the full path to the spreadsheet to which you're trying to export,
try removing the square brackets from around them.
If ReportData is actually the name of the table, put it in quotes.
DoCmd.TransferSpreadsheet acExport, 8, "ReportData", "C:\ReportName.xls"
Note that if you do not put a full path in front of the spreadsheet name,
you may have problems finding the resulting spreadsheet, as it will be
written to the current directory, which usually is not the directory where
yourMDBresides.
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Access97
I am using the following to transfer the contents of a table into an
existing spreadsheet (as a new worksheet).
This has worked for me previously.
DoCmd.TransferSpreadsheet acExport, 8, [ReportData], [ReportName]
where ReportData is the linked table name in the local database and
ReportName is the full path and file name of the spreadsheet.
I am getting Error "3011 - The Microsoft Jet database engine could not
find the object 'ReportData'. Make sure the object exists and that you
spell its name and the path name correctly."
The table does exist - the irony is that the worksheet is created in
the spreadsheet but only the field names from the table appear but no
other data? The data definitely exists in the table.
Why would it create the worksheet with the first line only of the
access table, and not include the rest of the data?
Am I doing anything wrong?
Piri- Hide quoted text -

- Show quoted text -

Hope your method works. But if necessary, you can try a utility called
Advanced Access Repair to repair your Access MDB file. It works rather
well for my corrupt Access mdb files. Its web address ishttp://www.datanumen.com/aar/

Alan- Hide quoted text -

- Show quoted text -

Piri,

Are you just trying to create an updated version of a spreadsheet in a
specific location, or trying to create an updated worksheet within an
existing spreadsheet file?

If the first, your problem is a lot easier to understand and fix. If
the second, then perhaps others can help out better...

discuss

DubboPete