Import Access into Excel has bad text format

liroberson · Jun 15th 2004

I run huge Access queries for exporting data into Excel worksheets. When I then try to use Excel's Index function (to lookup 3 char text codes (i.e., "ABC") from "Access imported" worksheet column C to return text in column D (i.e., "alpha beta cigma") index fails with error #N/A.

It seems that the imported Access text "ABC" in worksheet Data1 does not match my Excel entered text "ABC" in worksheet Data2. Only work around so far is to copy cell "ABC" from Data1 and paste over current Data2's "ABC" - rather exhaustive work for 64000 rows...then it recognizes it as text "ABC".

There are no leading/following spaces, the column is sorted - it is simple "ABC" text match error.

Tried to reformat column in Data1 to "text"... I've run out of ideas. If it is a reference error, not a visible one. I use the Index function successfully 99% of time...just not today. :?

Will Riley · Jun 15th 2004

There are actually 2 different kinds of printable blank spaces that can bugger
up imported data from some DB applications.... I have come across something
similar before... It was Aaron Blood who alerted me to the existence of the
Char(160) & some code (EG Below) for sorting it out... this MAY be your
problem & if so, the code will sort it out

Normal functions such as TRIM & CLEAN will remove a

Char 32 = Space

but they won't deal with a more nasty hidden variety, i.e.

Char 160 = No Break Space

This may help... try it on a sample import...

Code

Sub trimmer()
Dim rngMydata As Range
Dim rCell As Range


Set Fn = Application.WorksheetFunction
Set rngMydata = Sheets(1).[A1:A2000]
For Each rCell In rngMydata.Cells
rCell.Value = Fn.Substitute(rCell.Value, Chr(160), Chr(32))
rCell.Value = Trim(Application.Clean(rCell.Value))
Next rCell


End Sub

Display More

You'd just need to change this bit

Code

Set rngMydata = Sheets(1).[A1:A2000]

to whatever your range is with your data..

liroberson · Jun 15th 2004

Li response

WillR, good advice for multi-word entries, but not relevant to my issue. My column text has format like "ABC" (no spaces, all entries are three character alpha codes). Issues is that Excel can't correlate the Access generated 'ABC' to the Excel generated 'ABC'... odd.

Anyone else there have any suggestions?

Will Riley · Jun 15th 2004

Quote from liroberson

WillR, good advice for multi-word entries, but not relevant to my issue

How do you know ? Have you tried the code to see if it solves your problem - or have you just discounted it ?

Believe me, whilst it MAY not be relevant or helpful... the Char(160) issue CAN be responsible for this issue - even with single LETTER imports, let alone whole words... not saying that this it IS your problem, but it's a common one... but how you can discount it without even running the code I'm not too sure

charlesa920 · Jan 29th 2007

Re: Import Access into Excel has bad text format

I had a similar problem with dates. The cells in Excell looked like dates but were not because when I chagned the format of the cells to a different date format, the cells not change to the new format.

But if I select the cell and click in it, the format will change when the cell lost focus.

I ran the code above and sure enough, the correct format showed up.
Then just to be sure I pasted the same dates in 5 columns, formated them all different, ran the trimmer(), and they all changed to the correct format

Will Riley · Jan 29th 2007

Re: Import Access into Excel has bad text format

Cool Charles,

Nice to know that the above helped somebody in the end

Import Access into Excel has bad text format

Participate now!

Share