Need Workaround for Type Conversion Failure (VBA)

ReaperX

New Member
Joined
Oct 25, 2012
Messages
12
Hi All,

I am getting a Type Conversion Failure. I know WHY I'm getting it, I just think it's stupid and need a convenient work around.

I am running DoCmd.TransferSpreadsheet command in VBA through Access to import an Excel file into an existing database. One of the columns in Excel is a data type General (I would like to leave it this way for various reasons) that can hold blanks, numbers, or text. The accompanying column in the Access database is of type Text, as that is how I want all of the information stored. When the import occurs Access finds the first record with a value in the Excel column and choose that as the data type. In this case the first row with a value it sees has a number and expects all values in that column to be a number - Then I get the Type Conversion Failure when it reaches rows that have Text in this column.

Please help, it is used for automation so I really can't be opening each Excel file up to convert the column to Text (which does work). Is there any other solution you ladies and gents can think of?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Are you importing into the existing table?

Apart from numeric/blank/text data in the column are there any errors?

PS It's not the first cell the data type is based on, it's the first few.
 
Upvote 0
I am importing into a blank table that exists. I do some updates and then clear that table again.

There are no other errors.

My current data set has the following values in that column: 3 empty cells, 2 cells with the number 2, 5 empty cells, and then 2 cells with the value "WI". The first Conversion Type Failure is when it reaches the "WI". If I type the letter "T" into the first empty cell and run my code it works flawlessly.
 
Upvote 0
Bump - I am having this same problem. Does anyone have a legitimate workaround? I can't keep editing these monthly datasets for people - so I would rather build a tool that can adapt to any slightly faulty fields.

I find it completely unacceptable that if I put the import table cell at "Text" that it can't bring in ANY field.
 
Upvote 0
Import into an existing table with all the fields set as Text, then append the data from that table to the destination table.

That's kind of the standard way to deal with this sort of thing where the data is causing problems.
 
Upvote 0
Import into an existing table with all the fields set as Text, then append the data from that table to the destination table.

That's kind of the standard way to deal with this sort of thing where the data is causing problems.

That is the exact problem. I am trying to import into the first table (tblImport). It has all the fields to import F1...F2...F3 (so I don't have to use WithFieldNames) and import the data into it. Everything works fine except for certain ZipCodes that have zero's in them. Excel wants to think they are numbers. Even though I want them imported into the table with Text. If I convert to number, they lose the zero's. So far it's a lose-lose.

Anyone have a general fix for these types of issues?
 
Upvote 0
Everything works fine except for certain ZipCodes that have zero's in them. Excel wants to think they are numbers. Even though I want them imported into the table with Text. If I convert to number, they lose the zero's. So far it's a lose-lose.

Anyone have a general fix for these types of issues?
How is the Zip column formatted in Excel? Is it Text?
When all else fails, export the Excel file to a Text file, and import the Text file into Access (when importing Text files, you designate the data-type of each field; when importing Excel files, you do not have that option as Access tries to "guess" it).
 
Upvote 0
How is the Zip column formatted in Excel? Is it Text?
When all else fails, export the Excel file to a Text file, and import the Text file into Access (when importing Text files, you designate the data-type of each field; when importing Excel files, you do not have that option as Access tries to "guess" it).

Correct, but the issue is that we are building this for user's that aren't savvy enough to do tricks to manipulate the files.

I think I have found my answer by running a loop that will open the Excel doc automatically and add a comma to the data in order to make sure Excel recognizes it as text (even though the zip-codes are numbers).
 
Upvote 0
I think I have found my answer by running a loop that will open the Excel doc automatically and add a comma to the data in order to make sure Excel recognizes it as text (even though the zip-codes are numbers).
A comma? Do you mean an apostrophe (or single-quote)?
 
Upvote 0
A comma? Do you mean an apostrophe (or single-quote)?

Yes, sorry, I meant an apostrophe. However, I'm not sure if I need to run a loop for just the top row of data or all of the data. Doh. I found the code elsewhere and I believe it's putting it infront of all of the data.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top