Avatar of Shanan212
Flag for Canada asked on

Export Errors/Type Conversion Errors

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
    "TEMP", "C:\Users\" & username & "\Desktop\" & iName, True


I am exporting/appending a table via the above code.

The problem is, whenever I do the appending via manual way (by going through the Access Wizard) it appends fine.

However, whenever I try to append it via the above code, it gives 'Type Conversion' Error on a certain column (column H)

This column H is definted as 'Text' on the database

Also in the excel file, defined as text (and I tried to re-define it via the following vba code)

    Selection.NumberFormat = "@"
    Selection.TextToColumns Destination:=Range("H1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 2), TrailingMinusNumbers:=True

^ Regardless of what I do, when I do the appending manually, it imports fine. When I do it through code, it gives a list of import errors on column H.

Any help is much appreciated!

Microsoft Access

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon

I don't get it?


^ That did not help. The problem is, my rows are already defined as text!
Jeffrey Coachman

A sample of the the Excel file would help greatly...

What's all the TextToColumns stuff for:
Selection.TextToColumns Destination:=Range("H1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 2), TrailingMinusNumbers:=True

This may be the culprit....

The format may be: "@", ...but Access relies on the "Datatype".
Your code above is setting it as: xlFixedWidth
Who know how Access will try to resolve this?
Also unknown is what Access thinks of:  FieldInfo:=Array(0, 2)

In other words, if you just manually inserted the "required" text in the cells (the text that really gets inserted after all the "massaging"), ...does this work?


I am lost in  your last sentance.

Please see the attached sample file.

How I am doing it that, the text to column, etc is done on the excel macro.

Then the importing is done on the Access Macro.

A a unique thing is, this (import error) happens on only certain data (or files of data) which I try to import into Access.

Thanks for the help!
Your help has saved me hundreds of hours of internet surfing.
Jeffrey Coachman

1. My point was that in order to test this, I would have to have your same setup and try to see if I could replicate the issue.

2. My other question was, ...If you inserted the correct data "manually", would it work?

3. There is No data in column H in the sample you posted...?
Is this the way it is normally?..
What type of data is supposed to be in this field?
(perhaps that is the issue, ...That Access does not know if this is Null, or an empty String...)

1. To do this I will have to show you the database. Which I can but I want to see if it can be done without it. THanks!

2. If I manually import it via the Access wizard, it works fine.

3. There is no data...however there is data here and there which is like


The data type should be text. Same field is defined as text in Access.

How would I let access know it an empty string?

Even when there is nothing inside cells (some of course) the error shows. Do you require the database or got any advice for me?

Please see attached sample database.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeffrey Coachman

Still confused...

The error table states an error on row: 32, 141, 156, ...etc
...yet there are no rows (records) in the table "probills"?

What I need is a sample of this *entire* system.
So that I can do exactly as you are doing and replicate the error.

The basic error is that Access does not understand what the data is in those rows, so it fails and throws the error.

But again, the PROBILLS table has no records, so I cant even see what the data is in the rows it should have imported (row 1-31, ...for example)

Please see the attached. This is the file that I am try to append to the table Probill
Jeffrey Coachman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Nope. The table Probill in the database already got a column called "Sub" which corresponds to column H in Excel.

The "Sub" is already defined as text right? So I am appending it to that table

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
    "Probill", "C:\Users\" & username & "\Desktop\" & iName, True

Isnt this already a 'staging' table you as you mentioned?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
Jeffrey Coachman

Again, I really need a way to simulate your entire process... to investigate further.

As I stated, if you loaded column H with dummy text in all the records, does it append?

All coding is inside the Access.

The Sub "ImportExcel (iName as String)" get triggered by a sub in Excel.

What I am realllllly doing here is this

Take the Excel file. Append to Access - TEMP table (hidden). (Lets say the Excel contains the latest data)

Then taken Unique values from Probill table (say there is data), and append them to the temp table.

Delete all contents in Probill table

Then copy Temp table to Probill Table

Clear Temp table

^ all above coding is in Access Database I posted

You may temporarly run the code by creating a sub and calling it with the excel file name (I know you already know this - just suggesting)

Jeffrey Coachman

Nope to what?
Pleas be specific.

Again, if you posted dummy text in the excel file does it Append?
Yes or No?

Your Access code may need some tweaking in the error handling, and the way you are running the SQL.
Try not to use "On Error Resume Next", ...try to prevent or trap the specific error.
(Only use  "On Error Resume Next" if the sub is simple, which yours is not)
Also use code like this to run SQL:
Currentdb.execute "Your SQL", dbfailonerror
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeffrey Coachman

I'll play around with your code tonight...

After reading through your comment, I thought of doing this (since Access looks at top few line) I am assigning ' to those cells.

Never gave me an error!

    For count = 2 To 15
        If (Cells(count, 8).Value = "") Then
            Cells(count, 8).Value = "'"
        End If
    Next count

Thanks for all the help!
Jeffrey Coachman



But you could have accepted your own post as the the actual "solution"


I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck


Yea but still your comment initiated the idea. Thanks still!
Jeffrey Coachman