Type converion failure when importing from spreadsheet

[jan@BRU]

Hello folks,

I have problem, which I can't resolve. I use a the transferspreadsheet command in a form's module to import a certain portion of an excel spreadsheet into a table -- nothing spectacular:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "AC_Import", Me.Filename, False, "Firstsheet!" & "A" & Trim(Val(Me.RowFrom)) & ":X" & Trim(Val(Me.RowTo))


The whole thing works fine, albeit for one exception. If the text to be imported from the spreadsheet starts with an asterix (*), like for example: *25 the command doesn't go through and produces a Type Conversion error, and the field content is not imported.

The table AC_Import is a table, consisting of only text fields, set to maximum length. Actually, i used to create a new table during import, but that created many more of the same problems, as the import procedure would try to determine the kind of data contained in a column by the first data row. So if it read - say 25 - in the first row for a certain field, it would always conclude, that this must be an integer field, and any subsequent entry with - say xxx - in that same column would then produce a type conversion error. So by setting everything to a text field, before I hoped, it would solve the issue... and it did, but not for the asterixes in first position.

Any idea?

Many thanks

 

[Friday]

You should write some code to run prior to the Transfer code that strips the * out of the column, something like:

Columns("A:A").Select
    Selection.Replace What:="~*", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

That's straight out of an Excel macro, you will have to convert it to proper Access vba code. Also note the tilde in the What section. You have to preface the * with the tilde or Excel will wipe out the entire cell contents.

 

[jan@BRU]

Hello,
thanks for the idea. Unfortunately, it's not so much a question of forcing the content of the spreadsheet to become numeric (e;g. by suppressing the asterix), but rather for the import to also accept text values, when the first few entries had been numeric... So in that sense, I want the "*25" to be read as "*25", even if the first hundred values in the same column had been completely numeric "25". For this reason, I also created a text field, into which the corresponding column is transfered. The problem seems to be that the import procedures decides based on the data, whether it's dealing with a text or a numeric column and then it doesn't matter any more, into which kind of field, one imports...

Any ideas?

 

[ajetrumpet]

The problem seems to be that the import procedures decides based on the data, whether it's dealing with a text or a numeric column and then it doesn't matter any more, into which kind of field, one imports...

THAT IS exactly right, according to what i've heard. just out of curiosity, have you tried preceeding everything in your spreadsheet with an apostrophe before you import? that is a "literal" character, and it forces cell values to display in excel. just a suggestion...i'm actually wondering if it would work!