Embedded Character In Excel Problem

[tatrader]

I am trying to transfer informtion contained in certain cells in an Excel spreadsheet to another program (the Downloader of MetaStock) as part of stock data updating.

 

I can select the cells ok in Excel and perform a clipboard copy operation. I then try to paste the into a selected cell of the Downloader. I get an error generated by the Dowloader stating that the attempted paste did not work due to an unpermitted character.

 

If I do an intermediate step the data is accepted by the Downloader. I can paste the copied cell from Excel into Notepad, and then copy the data in Notepad and paste it into the Downloader with no problem.

 

I assume that some kind of embedded character/attribute is associated with the Excel copied data. The cell in Excel looks like "11,069.74". I store the copied Excel cell data (5 separate cells) as a text variable which I then sequentially copy to the clipboard and attempt to paste into the Downloader.

 

I have tried using the trim and/or strip CR/LF on the text variable before I attempt pasting it into the Downloader, but same reported error.

 

Evidently the intermediate step of using Notepad acts as a filter that eliminates the unwanted character/attribute. However, I would prefer to not hve to go through such an intermediate process.

 

Any ideas on how to filter out whatever characters/attributes are associated with the Excel cell data so I can store it in variables and then paste the variables into the Downloader cell?

 

I have looked at the stored data in the text variables in the debug variable listing, and the data "looks" clean, i.e. only numbers, commas and a decimal point.

[lemming]

You're probably getting some other characters which are not visible. Try this Text Cleaner script. It cleans up the T1 variable; modify as necessary:

 

// Null char

Variable Set %T90% to ASCII Char of 0

// Bell

Variable Set %T91% to ASCII Char of 7

// Backspace

Variable Set %T92% to ASCII Char of 8

// Tab

Variable Set %T93% to ASCII Char of 9

// Escape

Variable Set %T94% to ASCII Char of 27

// Delete

Variable Set %T95% to ASCII Char of 127

// strip CR/FL

Variable Modify String: Strip CR/LF from %T1%

// strip other non-printables

Replace "%T90%" with "" in %T1%

Replace "%T91%" with "" in %T1%

Replace "%T92%" with "" in %T1%

Replace "%T93%" with "" in %T1%

Replace "%T94%" with "" in %T1%

Replace "%T95%" with "" in %T1%

 

I got the ASCII chart from here.

Text_Cleaner.mex

[tatrader]

Thanks Lemming, I will give this a try and post results.

 

One more thing about my problem.

 

The information in Excel is captured from a web site having a table with stock information: symbol, open, high, low, close, volume. It is pasted into Excel, in order to more easily separate the different values for each stock I then want to transfer the data into my stock database contained in files kept by the MetaStock that can be edited by using MetaStock's Downloader program.

 

So there could be characters/attributes embedded that are not visible, but are there to cause the problem in the attempted paste into the Downloader cell.

[tatrader]

Lemming and all,

 

I tried the macro text cleaner macro posted by Lemming (thanks!). Unfortunately it did not delete the hidden character(s).

 

So I concluded that the troublesome character(s) had to be some other character than the ones specifically filtered by text cleaner macro. The data in the Excel cell had been copied from a table of stock values (from Bigcharts.com), and thus could contain ??? in addition to the numeric values for open, high, low, close, volume for each stock symbol.

 

Thinking about the approach used in the text cleaner macro, I wrote the attached macro that cleans ALL ASCIII characters from 0-31 and 127-255 from the text string copied from the Excel cells. The filtered characters are not normally used for typical alphanumeric strings.

 

It worked! I ran a test and determined the unwanted embedded, non-printing, character to be between ascii 127 - 255. Anyway, happy ending. The Excel cell values, after being filtered by the attached macro, now paste into my target application program as numbers.

ASCII_TEXT_FILTER.MEX