"External table not in expected format"

[Sean Clayton]

Hi all,

I have an Access database with two tables in it that I'm trying to
export via macro to a particular Excel sheet. The macro uses the
TransferSpreadsheet action. Running the macro brings up the message
"External table not in expected format", and the resulting data in the
spreadsheet is completely mangled. I suspect that there's something
about the Excel sheet that's causing the trouble, but I can't find
what. Would anyone have any ideas?

Thanks,
Sean

 

[Ken Snell]

Post the macro's actions and arguments.

Describe what "completely mangled" means.

Does the desired spreadsheet exist already in the EXCEL file into which you
are exporting the data?

 

[Ken Snell]

Also, what version of ACCESS and what version of EXCEL?

 

[Sean Clayton]

Also, what version of ACCESS and what version of EXCEL?

Thanks for the replies, gentlemen.

'Completely mangled' means that all the formatting has been lost, data
that's supposed to reside in one column is spread seemingly at random
among other columns (name, for example, in column A in one row, column
D in another, column G in another) and some data disappearing
entirely.

Access and Excel are both version 2000.

 

[Ken Snell]

See if this article provides any insight/ideas about what you're seeing:

Using the Range Argument of TransferSpreadsheet when Exporting Data to an
EXCEL File (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#ExpRange

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Also, what version of ACCESS and what version of EXCEL?

Thanks for the replies, gentlemen.

'Completely mangled' means that all the formatting has been lost, data
that's supposed to reside in one column is spread seemingly at random
among other columns (name, for example, in column A in one row, column
D in another, column G in another) and some data disappearing
entirely.

Access and Excel are both version 2000.

 

[Sean Clayton]

See if this article provides any insight/ideas about what you're seeing:

Using the Range Argument of TransferSpreadsheet when Exporting Data to an
EXCEL File (VBA)
 http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#ExpRange

--

        Ken Snellhttp://www.accessmvp.com/KDSnell/





Thanks for the replies, gentlemen.

'Completely mangled' means that all the formatting has been lost, data
that's supposed to reside in one column is spread seemingly at random
among other columns (name, for example, in column A in one row, column
D in another, column G in another) and some data disappearing
entirely.

Access and Excel are both version 2000.

Thank you for the link, but I'm afraid that I can't find anything in
it that helps me. I do know a bit more about the situation, though.
We're trying to export to an existing Excel sheet, created by someone
else and sent to us for additions. Several tabs already exist in the
sheet, but nowhere near enough tabs, or records overall for that
matter, to make issue with Excel size restrictions. Thinking some
rogue formulae might be responsible, we removed all of them from the
sheet and tried the export again, with the same result.

I suspect that the error lies with the spreadsheet itself and not
necessarily the data in it. We're exporting to two new tabs, and
exporting to an entirely new spreadsheet of our own creation works
just fine. What could be present or set wrong in a spreadsheet to make
it unwilling to accept new data? For clarity's sake, the new tabs will
have ~700 records each, and they will be joining a sheet of around 10
preexisting tabs, each with varying data in it, but never more than
~1000 or so rows.

 

[Sean Clayton]

Thank you for the link, but I'm afraid that I can't find anything in
it that helps me. I do know a bit more about the situation, though.
We're trying to export to an existing Excel sheet, created by someone
else and sent to us for additions. Several tabs already exist in the
sheet, but nowhere near enough tabs, or records overall for that
matter, to make issue with Excel size restrictions. Thinking some
rogue formulae might be responsible, we removed all of them from the
sheet and tried the export again, with the same result.

I suspect that the error lies with the spreadsheet itself and not
necessarily the data in it. We're exporting to two new tabs, and
exporting to an entirely new spreadsheet of our own creation works
just fine. What could be present or set wrong in a spreadsheet to make
it unwilling to accept new data? For clarity's sake, the new tabs will
have ~700 records each, and they will be joining a sheet of around 10
preexisting tabs, each with varying data in it, but never more than
~1000 or so rows.

Problem solved. As it turned out, the TransferSpreadsheet action was
pointing to a network location, and it needed the actual full network
path, and not the alias.

Lord only knows why, but we're back on track now. Thanks all.

 

[Ken Snell]

Glad to hear of your success.
--

Ken Snell
http://www.accessmvp.com/KDSnell/



Problem solved. As it turned out, the TransferSpreadsheet action was
pointing to a network location, and it needed the actual full network
path, and not the alias.

Lord only knows why, but we're back on track now. Thanks all.