exporting to Excel 2003 using VB6

[timot]

Hey guys... i have a question, my company wants me to export the things done for the day from SQL server... i've written all the codes and they work fine, except that when outputting to Excel (2000 or 2003, but not 2007), there are these unknown characters in between the texts. I copied and pasted in Word then use the Show/Hide #symbol# button, it shows that the unknown characters are either Tab or Enter key.

The question is, how do I remove these unknown characters when exporting out of SQL Server 2000 ?


Thanks guys.

 

[Crusty]

Could you show us a sample of the text?

 

[timot]

here we go

 

[timot]

it's that freakin squares that killing me

 

[KB]

Can you export to CSV and see if that improves it. Have you tried replacing the tab and carriage return with empty strings using

Replace(Replace(text, vbTab, ""), vbcrlf, "")

If you need the newline in there, SQL Server has both a carriage return and a newline character. Try replacing CHAR(10) with CHAR(13) in TSQL and vice versa

 

[timot]

i tried

if Val(InStr(Temp(7), Chr(11))) > 0 Or Val(InStr(Temp(7), Chr(12))) > 0 then

But still.... it doesnt detect that character... i tried hovering my mouse to the variable (temp(7)), it still shows the character, but when i tried in the immediate window, the character is not there...... so i m guessing it's how the excel interpret it??? so after i get the entry, should i do it just before it writes to excel or after i've done everything, i reiterate again to replace all those characters? doesnt seem optimal to me...

 

[timot]

anyone?

 

[timot]

nvm, got it, just used the Replace function in vb6 under excel class