troubleshooting Question

Export Errors/Type Conversion Errors

Avatar of Shanan212
Shanan212Flag for Canada asked on
Microsoft Access
18 Comments1 Solution518 ViewsLast Modified:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
    "TEMP", "C:\Users\" & username & "\Desktop\" & iName, True

Hi,

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)

    Columns("H:H").Select
    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!

Thanks!
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 18 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 18 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros