Auto-formatting Time with a Query Table

[Geoff ML]

I'm a newcomer to VBA. I need to use query tables to load information from
the net. I record a macro to do this and it works fine EXCEPT that it
auto-formats the times in cells into a time that excel understands. I don't
want it to do this i want it to keep it as text from the webpage.

For example, if on the web the sheet says 28:19 as in 28 minutes, 19
seconds, when i import it i want it to stay exactly as that 28:19 and i want
it to stay as text so i can manipulate it. But excel custom formats it
automatically and when i try to turn it back into text its decimal. And i
don't think those decimals represent the initial times as in 28 minutes 19
seconds.

I've tried lots of stuff but i can find no querytable property or method
that controls this (there is one for auto-formatting date, which i turned to
false and solved that problem). I turn the format of all the cells to text
prior to creating the table and it still changes the format of the cells to
custom. If i copy and paste the webpage to excel, then choose Match
Destination Formatting in the paste options this solves my problems perfectly
but i can find no way to do this with a querytable.

An example webpage:http://www.nhl.com/scores/htmlreports/20072008/GS021218.HTM

they are hockey statistics. Thanks.

 

[Nayab]

I'm a newcomer to VBA. I need to use query tables to load information from
the net. I record a macro to do this and it works fine EXCEPT that it
auto-formats the times in cells into a time that excel understands. I don't
want it to do this i want it to keep it as text from the webpage.

For example, if on the web the sheet says 28:19 as in 28 minutes, 19
seconds, when i import it i want it to stay exactly as that 28:19 and i want
it to stay as text so i can manipulate it. But excel custom formats it
automatically and when i try to turn it back into text its decimal. And i
don't think those decimals represent the initial times as in 28 minutes 19
seconds.

I've tried lots of stuff but i can find no querytable property or method
that controls this (there is one for auto-formatting date, which i turnedto
false and solved that problem). I turn the format of all the cells to text
prior to creating the table and it still changes the format of the cells to
custom. If i copy and paste the webpage to excel, then choose Match
Destination Formatting in the paste options this solves my problems perfectly
but i can find no way to do this with a querytable.

An example webpage:http://www.nhl.com/scores/htmlreports/20072008/GS021218.HTM

they are hockey statistics. Thanks.

Geoff, may be u can try to convert the date in the string on the
database side....sth similar to CONVERT(CHAR(19), CURRENT_TIMESTAMP, 0)

 

[Don Guillett]

Does this help?
=TEXT(E23,"[mm;ss]")/1440

 

[Geoff ML]

DON the TEXT function does not seem to work. Excel has already assigned a
time value to it and from what i understand TEXT only changes the format not
the value. So after i use the TEXT function then i try to find the leftmost
character i.e. LEFT(A1) on the result it doesnt work.

The heart of the problem is i want to keep excel from assigning a time value
to it when i import it i want it to remain as it is, remain as just text.
There's no way to do this? can't i turn something off somehow.

Nayab i tried what you wrote i don't think it'll work. I don't want to
convert from one time measurement to another i want to convert from one
format to another. i think.

HELP

 

[Rick Rothstein \(MVP - VB\)]

You don't say how your "times" are being displayed by Excel... is it like
this 00:28:19? If so, you can custom format those cells to display mm:ss and
suppress the "00:" part; if not, show us how Excel is displaying them. Also,
you don't necessarily need your time values to be text in order to
manipulate them (examples of what you want to do would be helpful if you
have trouble doing this).

Rick