Access import not handling ALT+ENTER char from Excel

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

I am posting this as a new question, although it appeared on this site four
years ago. I was excited to see my exact problem posed as a question then,
but the end of the thread isn't quite what I'm looking for.

Alt-Enters in the middle of an Excel cell are being read by MS Access as an
end-of-record marker. By way of example, the spreadsheet I need to import
into Access has rows with 20 columns. The Alt-Enter character occurs in the
4th column of the 10th row. My first nine rows in Access will therefore have
the appropriate 20 columns, but the 10th row STOPS at the Alt-Enter, and the
5th column of row 10 is wrongly treated as the first column of row 11.

I need to be able to replace that Alt-Enter character with something less
impactful on the import, such as a comma. I have tried these variations
(vbScript) without success:

TheVariable=act.readline
TheVariable=replace(TheVariable,chr(10),", ")
TheVariable=replace(TheVariable,chr(13),", ")
TheVariable=replace(TheVariable,chr(138),", ")
TheVariable=replace(TheVariable,chr(141),", ")
TheVariable=replace(TheVariable,chr(166),", ")

The chr(166) was an attempt based on having read that Alt-Enter is part of
the EXTENDED ascii set (achieved by holding Alt and pressing 0166 on the
numeric keypad), but none of these work.

Can anyone tell me what to do to render this linefeed character as something
harmless in MS Access? This is specifically a web app that imports Excel data
into an Access database. That's why the language is vbScript. Any solution
that involves opening the desktop Excel or Access app (like
http://support.microsoft.com/kb/115576) won't solve this for me.

Thanks very much!
 
Back
Top