Unwanted Scientific Format With Web Query

Audri · Apr 5th, 2002, 04:54 PM

My Excel application does a web query on a html file containing security cusip numbers having an "E" in the 8th position of the data. Excel formats the data as scientific; I need to have a text format. I have tried using a .txt extention on the file and preformatting the cell as text (prior to performing the web query), but nothing has
worked so far. I am able to address this problem on a text import by using .TextFileColumnDataTypes = Array(2). Is there something similar for a Web Query?

daleyman · Apr 5th, 2002, 05:07 PM

have you tried prefixing the value with a '

Audri · Apr 6th, 2002, 10:52 AM

I'm sorry, I don't know how to prefix the data with a ' on a web query. By the time Excel completes loading the sheet, it has already changed the VALUE of the data as well as the format. I cannot add a ' to the html file as it is used for other purposes. Please explain how I can add a ' during the web query operation. Thanks!

daleyman · Apr 10th, 2002, 05:45 PM

oh i see, sorry.

Is the initial data source of this column always eight digits, that is, can you use a formula to convert these values back?

Audri · Apr 10th, 2002, 07:11 PM

No, these are US Treasury Notes, as well as other security CUSIPs, some are 6, some 7 characters in length.

Thanks so much for your reply. I appreciate all suggestions!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 5th, 2002, 04:54 PM	#1
Audri New Member Join Date: Mar 2002 Posts: 5	My Excel application does a web query on a html file containing security cusip numbers having an "E" in the 8th position of the data. Excel formats the data as scientific; I need to have a text format. I have tried using a .txt extention on the file and preformatting the cell as text (prior to performing the web query), but nothing has worked so far. I am able to address this problem on a text import by using .TextFileColumnDataTypes = Array(2). Is there something similar for a Web Query?

Apr 5th, 2002, 05:07 PM	#2
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	have you tried prefixing the value with a '

Apr 6th, 2002, 10:52 AM	#3
Audri New Member Join Date: Mar 2002 Posts: 5	I'm sorry, I don't know how to prefix the data with a ' on a web query. By the time Excel completes loading the sheet, it has already changed the VALUE of the data as well as the format. I cannot add a ' to the html file as it is used for other purposes. Please explain how I can add a ' during the web query operation. Thanks!

Apr 10th, 2002, 05:45 PM	#4
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	oh i see, sorry. Is the initial data source of this column always eight digits, that is, can you use a formula to convert these values back?

Apr 10th, 2002, 07:11 PM	#5
Audri New Member Join Date: Mar 2002 Posts: 5	No, these are US Treasury Notes, as well as other security CUSIPs, some are 6, some 7 characters in length. Thanks so much for your reply. I appreciate all suggestions!