How could you create a formatting error in the attached Excel file so that the DoCmd.TransferSpreadsheet command does not work properly?

Posted on 2011-04-18
Medium Priority
Last Modified: 2012-05-11
I am developing an Access application using Access 2003 with an MDB type file.
My Access application imports an Excel file into an Access table via the DoCmd.TransferSpreadsheet command.

A sample Excel file for my application follows in the File attachment section.

Do you know how I could modify the attached Excel file to cause a format error to occur when the DoCmd.TransferSpreadsheet command executes?

I want to test my application to ensure that it can handle an error in the format of the Excel file to be imported.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Bank Credits (A)", filenm, False, "Bank Credits (A)!A:N"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Bank Debits (B)", filenm, False, "Bank Debits (B)!A:N"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Book Debits (C)", filenm, False, "Book Debits (C)!A:N"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Book Credits (D)", filenm, False, "Book Credits (D)!A:N"
Question by:zimmer9
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35421642
Adding text in a numerical or date column will give you an error.
LVL 26

Accepted Solution

Nick67 earned 2000 total points
ID: 35422019
In 2003 Access looks at the first few rows in a column and 'guesses' at correct data types.
It UTTERLY ignores cell formatting

If you have a column that is mostly numeric--say work order numbers-- 123456--that occasionally shows up as text 123456-1, 123456-2
things will go #Value for all those text items.

The only certain way around that is to link an excel sheet in as a table where dummy data was created to get Access to 'guess' the data type the way you want.
Transfer your data into that sheet first, and then to the tables and you should be fairly bulletproof

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question