Auto-formatting Time with a Query Table
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/htmlreport...8/GS021218.HTM they are hockey statistics. Thanks. |
Auto-formatting Time with a Query Table
On Jul 5, 8:07*am, Geoff ML <Geoff
wrote: 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/htmlreport...8/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) |
Auto-formatting Time with a Query Table
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 |
Auto-formatting Time with a Query Table
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 "Geoff ML" <Geoff wrote in message ... 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/htmlreport...8/GS021218.HTM they are hockey statistics. Thanks. |
All times are GMT +1. The time now is 04:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com