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
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
    Adding text in a numerical or date column will give you an error.
    LVL 26

    Accepted Solution

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now