Prev: Excel 2007: "Reference is not valid" when refreshing pivot table
Next: Help with batch production and Solver
From: ppt_puppet on 4 Jan 2010 00:29
Hi,
I have a written a simple macro to convert local time to UTC. The conversion
has no problem but the CellNumberFormat to does not get change to "m/d/yyyy
h:mm". It remains "General". Here is the code of the function. Can someone
please help me in sorting out this issue. This macro would be used both in
Office 2003 and 2007.

Application.Volatile
ActiveCell.NumberFormatLocal = "m/d/yyyy h:mm"
od = dt
Set oShell = CreateObject("WScript.Shell")
atb =
"HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias"
offsetMin = oShell.RegRead(atb)
nd = DateAdd("n", offsetMin, od)
LU = nd
From: Gary Keramidas on 4 Jan 2010 01:30
i changed the format to just the date and ran your code and it changed the
formatting.

did you try ActiveCell.NumberFormat = "m/d/yyyy h:mm"?

not sure why your's isn't changing. are you on the same sheet?
--


Gary Keramidas
Excel 2003


"ppt_puppet" <pptpuppet(a)discussions.microsoft.com> wrote in message
news:C9E6D9EC-F8A0-4B5F-9557-A55B660C43DF(a)microsoft.com...
> Hi,
> I have a written a simple macro to convert local time to UTC. The conversion
> has no problem but the CellNumberFormat to does not get change to "m/d/yyyy
> h:mm". It remains "General". Here is the code of the function. Can someone
> please help me in sorting out this issue. This macro would be used both in
> Office 2003 and 2007.
>
> Application.Volatile
> ActiveCell.NumberFormatLocal = "m/d/yyyy h:mm"
> od = dt
> Set oShell = CreateObject("WScript.Shell")
> atb =
> "HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias"
> offsetMin = oShell.RegRead(atb)
> nd = DateAdd("n", offsetMin, od)
> LU = nd

 | 
Pages: 1
Prev: Excel 2007: "Reference is not valid" when refreshing pivot table
Next: Help with batch production and Solver