Jet Database can not find my object when exporting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am using the TransferSpreadsheet function in a macro to export my table to a specific Excel worksheet such that I can update the same spreadsheet periodically. However, when I make changes to the worksheet to perform calculations or add other worksheets to the file, then Access no longer updates the sheet. And I get an error saying "Microsoft Jet Database engine could not find the object 'pcu...' Make sure the object exists and you spelled its name correctly".

Well I'm pretty sure everything has been spelled correctly. I have gone and also made sure that the named range still references the same named range of the exported data. So, what can I do. Is it that Access doesn't like exporting to the same worksheet if anything in the file changes? How can I get around this?
 
Aja,

If you check MS-Access help on TrabsferSpreadsheet, it says that the Range
argument is used only for Import, not for Export; I suppose your problem
probably has to do with that.
Although it (apparently) doesn't work with named ranges, it does work just
fine with sheet names (undocumented in Access help). That is to say, if you
forget the named range and use the name of a sheet in the Range argument,
the TransferSpreadsheet export will overwrite the sheet with the exported
data each time you run it (or create a sheet with that name, if it does not
exist).

Would this work for you?

HTH,
Nikos


Aja said:
Hello,

I am using the TransferSpreadsheet function in a macro to export my table
Click to expand...
to a specific Excel worksheet such that I can update the same spreadsheet
periodically. However, when I make changes to the worksheet to perform
calculations or add other worksheets to the file, then Access no longer
updates the sheet. And I get an error saying "Microsoft Jet Database engine
could not find the object 'pcu...' Make sure the object exists and you
spelled its name correctly".
Well I'm pretty sure everything has been spelled correctly. I have gone
Click to expand...
and also made sure that the named range still references the same named
range of the exported data. So, what can I do. Is it that Access doesn't
like exporting to the same worksheet if anything in the file changes? How
can I get around this?
 
That would be great for Access to overwrite my worksheet and use the same name. However, the Excel worksheet's name is also matched to the exported data but it still writes to new sheets(i.e. it uses the same name as the exported data and appends a 1 at the end because it senses a duplicate) or gives the Jet database error.
 
Aja,

Do you mean is names the worksheet after the name of the table or query that
you export? That is what happens if you so not provide an argument for
Range. If you so, it will name the worksheet after the argument you provide,
and overwrite an existing one by the same name.

HTH,
Nikos

Aja said:
That would be great for Access to overwrite my worksheet and use the same
Click to expand...
name. However, the Excel worksheet's name is also matched to the exported
data but it still writes to new sheets(i.e. it uses the same name as the
exported data and appends a 1 at the end because it senses a duplicate) or
gives the Jet database error.table to a specific Excel worksheet such that I can update the same
spreadsheet periodically. However, when I make changes to the worksheet to
perform calculations or add other worksheets to the file, then Access no
longer updates the sheet. And I get an error saying "Microsoft Jet Database
engine could not find the object 'pcu...' Make sure the object exists and
you spelled its name correctly".and also made sure that the named range still references the same named
range of the exported data. So, what can I do. Is it that Access doesn't
like exporting to the same worksheet if anything in the file changes? How
can I get around this?
 
Back
Top