How to keep cell format while refreshing data from webquery

[sureshsmr]

Hello,

I have created a web query that imports/refreshes data from one of our internal website.

But eventhough I have set the format of all the cells in the spreadsheet to "text" it always shows some of the new items in scientific notation for values with E followed by number.

The formats in those cells have somehow changed to "scientific".

(When I do a paste special>values, it comes out okay!)
But I want to use the web query which is a lot easier.

Is there any way to stop or avoid this?
Appreciate any help.

Thanks a lot...

 

[sneel3]

If you right click on the query area or go to Data / Data Range Properties (or it may be under parameters) and choose the option that allows you to import without changing the format of the sheet.

 

[sureshsmr]

Dear Sir,

I have checked the box that says "preserve cell formatting" in the data range properties. Even then, when the data is refreshed, it is changing the cell format to scientific. Looks like it is deciding the cell format based on the values that are being imported.
Is there any way to set the datatype as text in the query itself, just like we do when we import any other delimited static file?

Thanks a million,

 

[sneel3]

The only thing that I know it could be is the column width. Make the column wider and see if it still has the same issue.

 

[sureshsmr]

I set the column width and still having same problem. I would appreciate if anybody could throw some light on this problem. If it is a bug in Excel, there must at least be some work around!

Thanks
Suresh S

 

[sureshsmr]

Dear Members,

There must be some kind of solution for this problem. Please help.

Thanks
Suresh