Hi all,
I am "usig get external data" to fatch data from another
ecxel file to my current excel file. Everything works fine
except one thing. One of the column containd alfa numeric
data (i. e. text numbers and any combination of text and
numbers like 235521, AK558, ATT, BLD, 85455 etc). The data
in this columns shows blank cells at some places dont know
why. Has anybody came across this problem? Or is there any
solution to this?

Any help appreciated.
Thanks,
Kanti

Missing Data
Mixed

This article discusses this anomoly

http://support.microsoft.com/default...b;en-us;257819
HOWTO: Use ADO with Excel Data from Visual Basic or VBA

see "A Caution about Mixed Data Types"

It basically says the workaround would be to convert your numeric data so it
is stored as text.

--
Regards,
Tom Ogilvy

"Kanti" wrote in message
...
Hi all,
I am "usig get external data" to fatch data from another
ecxel file to my current excel file. Everything works fine
except one thing. One of the column containd alfa numeric
data (i. e. text numbers and any combination of text and
numbers like 235521, AK558, ATT, BLD, 85455 etc). The data
in this columns shows blank cells at some places dont know
why. Has anybody came across this problem? Or is there any
solution to this?

Any help appreciated.
Thanks,
Kanti

I have tried to save the mixed data as text but failed.
Then I tried to trim the mixed data and this also failed.

Any other way around?
Regards,
Kanti

-----Original Message-----
Missing Data
Mixed

This article discusses this anomoly

http://support.microsoft.com/default.aspx?scid=kb;en-
us;257819
HOWTO: Use ADO with Excel Data from Visual Basic or VBA

see "A Caution about Mixed Data Types"

It basically says the workaround would be to convert your
numeric data so it
is stored as text.

--
Regards,
Tom Ogilvy

"Kanti" wrote in message
...
Hi all,
I am "usig get external data" to fatch data from another
ecxel file to my current excel file. Everything works
fine
except one thing. One of the column containd alfa
numeric
data (i. e. text numbers and any combination of text and
numbers like 235521, AK558, ATT, BLD, 85455 etc). The
data
in this columns shows blank cells at some places dont
know
why. Has anybody came across this problem? Or is there
any
solution to this?

Any help appreciated.
Thanks,
Kanti


.

to save the mixed data as text

Sub makeText()
Dim rng as Range
Dim cell as Range
Set rng = selection.SpecialCells(xlconstants,xlnumbers)
for each cell in rng
cell.Formula = "'" & cell.Value
Next
End Sub

--
Regards,
Tom Ogilvy



"Kanti" wrote in message
...
I have tried to save the mixed data as text but failed.
Then I tried to trim the mixed data and this also failed.

Any other way around?
Regards,
Kanti

-----Original Message-----
Missing Data
Mixed

This article discusses this anomoly

http://support.microsoft.com/default.aspx?scid=kb;en-
us;257819
HOWTO: Use ADO with Excel Data from Visual Basic or VBA

see "A Caution about Mixed Data Types"

It basically says the workaround would be to convert your
numeric data so it
is stored as text.

--
Regards,
Tom Ogilvy

"Kanti" wrote in message
...
Hi all,
I am "usig get external data" to fatch data from another
ecxel file to my current excel file. Everything works
fine
except one thing. One of the column containd alfa
numeric
data (i. e. text numbers and any combination of text and
numbers like 235521, AK558, ATT, BLD, 85455 etc). The
data
in this columns shows blank cells at some places dont
know
why. Has anybody came across this problem? Or is there
any
solution to this?

Any help appreciated.
Thanks,
Kanti


.

Thanks Tom.
I have run your macro but still it gives the same results. All the numbers are not desplayed. I also gone thru the KB artical. It say that the whole column will be evaluated and majority data type will be desplayed and other data type will show null.
Any other clue?
Thanks,
Kanti

----- Tom Ogilvy wrote: -----

to save the mixed data as text

Sub makeText()
Dim rng as Range
Dim cell as Range
Set rng = selection.SpecialCells(xlconstants,xlnumbers)
for each cell in rng
cell.Formula = "'" & cell.Value
Next
End Sub

--
Regards,
Tom Ogilvy



"Kanti" wrote in message
...
I have tried to save the mixed data as text but failed.
Then I tried to trim the mixed data and this also failed.
Any other way around?
Regards,
Kanti
-----Original Message-----
Missing Data
Mixed
This article discusses this anomoly
http://support.microsoft.com/default.aspx?scid=kb;en-
us;257819
HOWTO: Use ADO with Excel Data from Visual Basic or VBA
see "A Caution about Mixed Data Types"
It basically says the workaround would be to convert your
numeric data so it
is stored as text.
--
Regards,
Tom Ogilvy
"Kanti" wrote in message
...
Hi all,
I am "usig get external data" to fatch data from another
ecxel file to my current excel file. Everything works
fine
except one thing. One of the column containd alfa
numeric
data (i. e. text numbers and any combination of text and
numbers like 235521, AK558, ATT, BLD, 85455 etc). The
data
in this columns shows blank cells at some places dont
know
why. Has anybody came across this problem? Or is there
any
solution to this?
Any help appreciated.
Thanks,
Kanti
.