How to keep cell format while refreshing data from webquery

sureshsmr

New Member
Joined
Oct 3, 2006
Messages
4
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...
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

sneel3

Active Member
Joined
Oct 9, 2002
Messages
331
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

New Member
Joined
Oct 3, 2006
Messages
4
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

Active Member
Joined
Oct 9, 2002
Messages
331
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

New Member
Joined
Oct 3, 2006
Messages
4
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

New Member
Joined
Oct 3, 2006
Messages
4
Dear Members,

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

Thanks
Suresh
 

Forum statistics

Threads
1,136,594
Messages
5,676,700
Members
419,644
Latest member
KeelsM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top