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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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
 
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
 
Back
Top