Downloaded numbers from the web are stored as text. No Good

[Guest]

Greetings Once Again,

I'm downloading data from a website using VBA and for some unknown reason
Excel is storing numbers as text and dates in mm/dd/yy instead of mm/dd/yyyy.
The strange thing is this doesn't happen for all the data that is downloaded.
It appears to be random.

Why is this and How can I fixed this?

I tried using the format cells, but that didn't work. I get that
notification (that green triangle) and I could update each cell as required,
but I have hundreds of cells to go through.

Thanks As Always! Kurt

 

[rdidentity]

all of your dates are in one or random columns?

 

[Guest]

All the numbers are in one column and the dates are all in another column

 

[John Fuller]

For the numbers, just put 1 in a cell, copy that, then select all the
numbers and paste special: multiply. Excel will convert the text to
numbers and then multiply by 1, thus turning them into numbers. As for
the dates, not sure.

 

[haroldj]

For the date fields just use the TEXT function: =TEXT(a3,"mm/dd/yyyy").
That will convert your numbers to a usable format. Of course you can
use whatever date format that you'd like, not just the example that I
gave. HTH.

Harold

 

[Guest]

One question I have is why is Excel doing this during the download in the
first place? Instead of doing something to the cell is there something I
should do in the VBA for the download??
here is the code

With Selection.QueryTable
.Connection = _
URLStr
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With