Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Need Workaround for Type Conversion Failure (VBA)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2012
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry Need Workaround for Type Conversion Failure (VBA)

    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?

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,154
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Need Workaround for Type Conversion Failure (VBA)

    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.
    If posting code please use code tags.

  3. #3
    New Member
    Join Date
    Oct 2012
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Workaround for Type Conversion Failure (VBA)

    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.

  4. #4
    New Member
    Join Date
    May 2013
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Workaround for Type Conversion Failure (VBA)

    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.

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,154
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Need Workaround for Type Conversion Failure (VBA)

    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.
    If posting code please use code tags.

  6. #6
    New Member
    Join Date
    May 2013
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Workaround for Type Conversion Failure (VBA)

    Quote Originally Posted by Norie View Post
    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?

  7. #7
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Need Workaround for Type Conversion Failure (VBA)

    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).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #8
    New Member
    Join Date
    May 2013
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Workaround for Type Conversion Failure (VBA)

    Quote Originally Posted by Joe4 View Post
    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).

  9. #9
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Need Workaround for Type Conversion Failure (VBA)

    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)?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #10
    New Member
    Join Date
    May 2013
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Workaround for Type Conversion Failure (VBA)

    Quote Originally Posted by Joe4 View Post
    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •