StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This script:</p> <pre><code>Option Explicit Dim goFS : Set goFS = CreateObject( "Scripting.FileSystemObject" ) Dim sFSpec : sFSpec = "..\data\updsheet\save.xls" Dim oExcel : Set oExcel = CreateObject("Excel.Application") Dim oWBook : Set oWBook = oExcel.Workbooks.Open(goFS.GetAbsolutePathName(sFSpec)) Dim dtA : dtA = #1/3/2013 8:45:43 AM# Dim dtB : dtB = #9/6/2012 8:04:58 AM# Dim dtC : dtC = #11/15/2012 8:45:43 AM# Dim dtD : dtD = #12/7/2012 8:45:43 AM# Dim aTests : aTests = Array( _ dtA, dtB _ , dtC, dtD _ ) Dim i For i = 0 To UBound(aTests) Step 2 WScript.Echo "----", i, aTests(i + 0), "<>", aTests(i + 1), aTests(i + 0) - aTests(i + 1), aTests(i + 1) - aTests(i + 0) On Error Resume Next WScript.Echo "A", oExcel.Application.WorksheetFunction.Text(aTests(i + 0) - aTests(i + 1), "[h]:mm:ss") If Err.Number Then WScript.Echo "A", Err.Description On Error GoTo 0 On Error Resume Next WScript.Echo "B", oExcel.Application.WorksheetFunction.Text(aTests(i + 1) - aTests(i + 0), "[h]:mm:ss") If Err.Number Then WScript.Echo "B", Err.Description On Error GoTo 0 WScript.Echo Next oExcel.Quit </code></pre> <p>and its output (German locale!):</p> <pre><code>---- 0 03.01.2013 08:45:43 <> 06.09.2012 08:04:58 119,028298611112 -119,028298611112 A 2856:40:45 B Die Text-Eigenschaft des WorksheetFunction-Objektes kann nicht zugeordnet werden. ---- 2 15.11.2012 08:45:43 <> 07.12.2012 08:45:43 -22 22 A Die Text-Eigenschaft des WorksheetFunction-Objektes kann nicht zugeordnet werden. B 528:00:00 </code></pre> <p>proves:</p> <ol> <li>@VBSlover's description of the problem can't be trusted. There are about 22 days/528 hours between #11/15/2012 8:45:43 AM# and #12/7/2012 8:45:43 AM#. It's everybody's guess where the 87:45:55 come from. (so the question should be downvoted)</li> <li>Other than @KekuSemau stated, the WorksheetFunction.Text() returns resonable string values, as long as you feed it the correct parameters. (so if the attempt to answer isn't made on topic and useful, it should be downvoted)</li> <li>@VBSlovers code contains an OERN. So if there is a problem in the assignment to TimeSpan (the function name), the return value of that function is undefined. No wonder some of the cells get filled with spurious data, that's what you get, when you hide errors.</li> </ol> <p><strong>Update I: wrt @Sean's proposal:</strong></p> <p>If you run</p> <pre><code>Option Explicit Dim dtA : dtA = #1/3/2013 8:45:43 AM# Dim dtB : dtB = #9/6/2012 8:04:58 AM# Dim Sean : Sean = "2856:09:45" Dim Ekke : Ekke = "2856:40:45" WScript.Echo "!German Locale!" WScript.Echo "dtB", dtB WScript.Echo "dtA", dtA WScript.Echo "Sean", Sean, "=>", addTS(dtB, Sean) WScript.Echo "Ekke", Ekke, "=>", addTS(dtB, Ekke) Function addTS(dtX, sHMS) Dim aParts : aParts = Split(sHMS, ":") addTS = dtX addTS = DateAdd("h", CLng(aParts(0)), addTS) addTS = DateAdd("n", CLng(aParts(1)), addTS) addTS = DateAdd("s", CLng(aParts(2)), addTS) End Function </code></pre> <p>output:</p> <pre><code>!German Locale! dtB 06.09.2012 08:04:58 dtA 03.01.2013 08:45:43 Sean 2856:09:45 => 03.01.2013 08:14:43 Ekke 2856:40:45 => 03.01.2013 08:45:43 </code></pre> <p>you'll realize that there must be a bug in Sean's code.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload