|
bk19@mweb.co.za -> RE: Submarine Reporter for Excel 2010 BETA (10/10/2011 5:13:53 AM)
|
OK, I have spent a fair bit of time on the code for this tool.
With the initial BETA release a number of fault conditions may have presented themselves.
1. As previously reported, construction of date strings using the "." token instead of the "/" token would cause a failure in the GetSunkShipDate and GetDayDate functions when calling the DateValue function. Due to the rather limited facilities of VBA I have coded a utility routine to determine the date separator by implicitly leveraging the not so accessible locale data of Microsoft Excel.
The function is
Function GetDateSeparator() As String
Dim MyStr As String
Dim i As Long
Dim b As Byte
MyStr = Format(Date, "Short Date")
GetDateSeparator = "?"
For i = 1 To Len(MyStr)
c = Mid(MyStr, i, 1)
If Not IsNumeric(c) Then
GetDateSeparator = c
Exit For
End If
Next i
End Function
and the code line in GetSunkShipDate and GetDayDate
s = sDay + "." + sMonth + "." + sYear
has been replaced with
s = sDay + DateSeparator + sMonth + DateSeparator + sYear.
2. The second failure would always be noticed on any platform where WITP AE was not installed on the C drive.
The original code section was:
f.WriteLine("c:")
f.WriteLine("cd + WITPAEPath + "\SCEN")
Now as in my case, suppose WITPAEPath contained the value "E:\Matrix Games\WITPAE".
This would then yield a batch file with the following two lines in it.
c:
cd "E:\Matrix Games\WITPAE\SCEN"
Since the current drive is different to the directory we wish to change to, the change directory command will always fail.
3. Some discussion has taken place earlier surrounding the probable need to use the beta version of witploadAE.exe
Examination of the original code in the spreadsheet shows the call to be "witploadAE.exe" & "/s" & ScenNo & " /e /b,"
In essence setting the column separator to a ',' token which just happens to be the default anyway.
I have now rigorously tested this using the stock version and the '\b' flag removed without any hint of a problem. I am satisfied that either version of this program will satisfy the requirements of this spreadsheet.
4. Two further errors were detected when attempting to execute the Batch file described in point 2 once it was correctly generated. Although they were slightly different errors they had the identical root cause and thus the same solution. The original code did not take into account the possibility of embedded space characters in a file path. This caused the chdir (cd) command in the batch file and also the call to the shell environment to run the batch file to fail.
To resolve this another utility function was cobbled together to wrap the path in an extra set of '"' tokens.
Function PrepareLongPathName(LongName As String) As String
' Chr(34) creates a " token
PrepareLongPathName = Chr(34) + LongName + Chr(34)
End Function
The MakeBat() subroutine is now
Sub MakeBAT()
Dim fs, f
Dim Line As String
Dim p As Long
' Line = "witploadAE.exe" & "/s" & ScenNo & " /e /b,"
Line = "witploadAE.exe" & " /s" & ScenNo & " /e"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile(BATPath, 8, True, 0)
' Determine the disk drive from the default path
' If none defined assume it is drive C
p = InStr(1, WITPAEPath, ":", vbTextCompare)
f.Writeline (IIf(p > 0, Left(WITPAEPath, 2), "c:"))
' This rather weird statement is wrapping an extra
' set of " tokens around the command to ensure
' that the DOS environment can actually find the
' command in the event of embedded spaces existing
' in the path tree
s = "cd " + PrepareLongPathName(WITPAEPath + "\SCEN")
f.Writeline (s)
f.Writeline (Line)
f.Close
End Sub
Once all of the above was complete the program was essentially running albeit with a few boundary error conditions.
I have also updated the code to initialise the Combat Reports and MONTHS pages whenever a fresh Database import was requested. This was an unfortunate omission and a necessary change.
A bit of 'eye candy' has also been added in the form of a non-modal dialogue box showing processing states.
And finally, the code has been formatted and some white space added to make it a bit more readable.
I hope that rcm1616 does not get too upset with the fact that I have climbed into his/her code and given it a bit of a work over. The idea of this tool was so darn good, I wanted a working copy right away. Sadly since I have never programmed with VBA before, it took me a while longer than it needed to.
Attached is a fresh BETA release for everyone to try out.
Good luck all and enjoy rcm1616s tool...
|
|
|
|
