Submarine Reporter for Excel 2010 BETA 1.3 UPDATED 10/28/2011

Post bug reports and ask for help with other issues here.

Moderators: wdolson, MOD_War-in-the-Pacific-Admirals-Edition

User avatar
felix83
Posts: 20
Joined: Thu Aug 13, 2009 7:19 pm
Location: München, Bayern

RE: Submarine Reporter for Excel 2010 BETA

Post by felix83 »

Dear rcm1616,
I highly appreciate Your work, but I am are disappointed we can not use it. [:(]
I like the idea as much as your intention to share it with the rest. Please don't feel disregarded by (at least) my comment: "cannot use it."
As I am mostley  a lurker You are propably in one of the most fair forums I am on.
User avatar
JeffroK
Posts: 6395
Joined: Wed Jan 26, 2005 4:05 am

RE: Submarine Reporter for Excel 2010 BETA

Post by JeffroK »

rcm1616

keep working, excellent tool and there must be a solution

Concurrent with the reflagging within the 101st, the 4th, 5th and 6th Battalions, 502nd Infantry were activated within the BERLIN BRIGADE by reflagging the existing 2nd, 3rd and 4th Battalions, 6th Infantry. This was part of a wider Army plan to regimentally pair units based within the United States with those stationed overseas for battalion rotational purposes. These three battalions, perpetuating the lineages of World War II Companies D, E and F, 502PIR, were neither Airborne nor Air Assault and were inactivated in the drawdown of the early 1990s. The rotation plan was found to be unworkable and was quietly abandoned.


Lost its Parachute rating around the late 50's and became an Air Assault Regiment when helicopters came into vogue
Interdum feror cupidine partium magnarum Europae vincendarum
User avatar
SgtSwanson
Posts: 212
Joined: Sun May 15, 2005 12:36 am
Location: Long Branch, NJ

RE: Submarine Reporter for Excel 2010 BETA

Post by SgtSwanson »

personal question: is 502nd IR in Berlin Brigade same 502nd regiment as in 101st Airborne? I never know, that Screaming Eagles had station in Berlin.

JeffK's answer is absolutely correct. And yes I was. It also helped with personnel transfers. If the parent unit was full for a particular MOS (type of Job), at your current rank, then you would go into general circulation. Being Infantry, my slot at just about any rank up to E-6(SSG)went to the 101st first or the 82nd if you already had jump wings. During the late 70's early 80's the plan was to keep the following units at 90% wartime strength (100% peacetime)at all times because they were the primary QRF (Quick Reaction Force) of the Army.

75th Inf Regt. (Rangers) kept a company on QRF EVERY single day during this time.
82nd Airborne Div. (Parachute type ops)
101st Airborne Div. (Heli ops)
10th Mountian Div. (Light Inf)
24th Inf. Div. (light Inf) Mainly for Pacific crises
7th Inf. Div. (Light Inf.)

The Army also set up almost all personnel rotations, a.k.a. PCS moves in 6 month blocks. So, every 6 months a Regt. / BDE would loose / gain around 25-30%.

One of the reasons it was dropped was because, instead of taking a week to 10 days to get the entire 82nd over to Saudi Arabia for Desert Shield in Aug. 90, it took 32 days before the last man arrived. The first elements of the first Mech Div. started arriving a week later on RO-ROs. The Army never did a full alert cycle (moving an entire Division)in training. So we found out the hard way at a bad time, that it was severly flawed. And the main reason was the Wall fell in Nov. 89. The system was part of the REFORGER plan for a Russian invasion of Germany.

Last note: Our patch was the old SHEAF patch with the sword in flames in the middle with Berlin in the crest instead of SHEAF.

Links for the Berlin Brgade:
http://www.berlinbrigade.com/index.html
http://www.berlin-brigade.de/
Sgt Swanson
87-93 5/502 Inf. Berlin Bde
93-95 2/502 Inf. 101st Airborne Div.
Freedom is never free!!

Patch of the Week: 6th Infantry Division
bk19@mweb.co.za
Posts: 258
Joined: Tue Jul 26, 2011 8:27 pm

RE: Submarine Reporter for Excel 2010 BETA

Post by bk19@mweb.co.za »

ORIGINAL: cantona2

I cant get this to work at all rcm1616

Do i need witpaeload running before I can use it? I use Tracker not witpaeload.
What happens if i have several games running from the same Save folder?

This unlikely to be the problem. It appears that the original code was written for use in east Europe. This guessed from the fact that the comments in the code appears to be Czech.

The following code block changes seem to get it to complete processing a single days operation and combat results files.

Code: Select all

 Function GetSunkShipDate(ShipLine) As Date
 Dim sDate As String
 Dim s, sDay, sMonth, sYear As String
 
     sDate = Right(ShipLine, 12)
     sYear = Right(sDate, 4)
     sDay = Mid(sDate, 5, 2)
     sMonth = GetMonth(Left(sDate, 3))
 '   s = sDay + "." + sMonth + "." + sYear
     s = sDay + "/" + sMonth + "/" + sYear
     GetSunkShipDate = DateValue(s)
 
 End Function
 
and

Code: Select all

Function GetDayDate(DateLine As String) As Date
 Dim LineL As Integer
 Dim s, sMonth, sDay, sYear As String
 
     LineL = Len(DateLine)
     s = Mid(DateLine, LineL - 9, 10)
     sMonth = GetMonth(Mid(s, 1, 3))
     sDay = Mid(s, 5, 2)
     sYear = Mid(s, 9, 2)
 '   s = sDay + "." + sMonth + "." + "19" + sYear
     s = sDay + "/" + sMonth + "/" + "19" + sYear
     GetDayDate = DateValue(s)
 
 End Function
 

The reader will note that the date separator '.' should be the '/' separator in areas such as the UK and USA.
This is also an inelegant hack and thus needs work to do some localisation checks to determine the correct separator.

I have reported this now, mostly because I am still in the process of debugging it.
bk19@mweb.co.za
Posts: 258
Joined: Tue Jul 26, 2011 8:27 pm

RE: Submarine Reporter for Excel 2010 BETA

Post by bk19@mweb.co.za »

ORIGINAL: SgtSwanson

This is NOT a hit on you rcm, just letting everyone else know that Excel Starter will not run the spreadsheet because macros are disabled with the free version that comes on most windows computers now.[:@][:@]

Macros are disabled in the general case as a mechanism to allow the user to protect him/herself from malicious code.

When the spreadsheet is loaded (in the newer versions) a toolbar option becomes available to enable the macro. This is a once off choice and must be immediately enabled before doing anything else. It is also possible to set the directory where this spreadsheet is stored to be a 'safe' source of spreadsheets and then macros will automatically be enabled.

I can't quite remember the mechanism for the older Excel application, but a similar option definately exists to enable macros in the spreadsheet. You will need to hunt around for it a bit.
bk19@mweb.co.za
Posts: 258
Joined: Tue Jul 26, 2011 8:27 pm

RE: Submarine Reporter for Excel 2010 BETA

Post by bk19@mweb.co.za »

ORIGINAL: rcm1616

MY MISTAKE!

I use witploadae version dated 24.7.2011. In this version is new export option /b which enables choosing other delimiter character then ";". Without this new feature I was not able to import data from csv file to excel correctly.

So witploadae.exe from BETA version of GAME must be in \SCEN folder.

Are you sure?

I had absolutely no problem parsing the database files using witploadAE.exe date November 17th, 2009 03:48:42. This I know for sure is the last version available after the last official patch was installed. To be precise... there are no beta installs in the current directory tree where I am testing your software.

EDIT: Belay that question. It seems that the csv files loaded preceded the run date of your tool. I will report back after further investigation... sorry....


User avatar
PaxMondo
Posts: 9798
Joined: Fri Jun 06, 2008 3:23 pm

RE: Submarine Reporter for Excel 2010 BETA

Post by PaxMondo »

ORIGINAL: bk19@mweb.co.za

ORIGINAL: SgtSwanson

This is NOT a hit on you rcm, just letting everyone else know that Excel Starter will not run the spreadsheet because macros are disabled with the free version that comes on most windows computers now.[:@][:@]

Macros are disabled in the general case as a mechanism to allow the user to protect him/herself from malicious code.

When the spreadsheet is loaded (in the newer versions) a toolbar option becomes available to enable the macro. This is a once off choice and must be immediately enabled before doing anything else. It is also possible to set the directory where this spreadsheet is stored to be a 'safe' source of spreadsheets and then macros will automatically be enabled.

I can't quite remember the mechanism for the older Excel application, but a similar option definately exists to enable macros in the spreadsheet. You will need to hunt around for it a bit.
Under options. deep in the developer tab IIRC.
Pax
User avatar
PaxMondo
Posts: 9798
Joined: Fri Jun 06, 2008 3:23 pm

RE: Submarine Reporter for Excel 2010 BETA

Post by PaxMondo »

ORIGINAL: bk19@mweb.co.za
This unlikely to be the problem. It appears that the original code was written for use in east Europe.
Agreed. I had to repair the same two routines as you did to get the date/time standard aligned correctly so it would find the right file name. Initially it was looking for CombatReport2.txt for me.... [:D]
Pax
User avatar
rcm1616
Posts: 30
Joined: Wed Aug 05, 2009 3:51 am
Location: Bratislava, Slovakia, EU

RE: Submarine Reporter for Excel 2010 BETA

Post by rcm1616 »

ORIGINAL: bk19@mweb.co.za
ORIGINAL: rcm1616

MY MISTAKE!

I use witploadae version dated 24.7.2011. In this version is new export option /b which enables choosing other delimiter character then ";". Without this new feature I was not able to import data from csv file to excel correctly.

So witploadae.exe from BETA version of GAME must be in \SCEN folder.

Are you sure?

I had absolutely no problem parsing the database files using witploadAE.exe date November 17th, 2009 03:48:42. This I know for sure is the last version available after the last official patch was installed. To be precise... there are no beta installs in the current directory tree where I am testing your software.

EDIT: Belay that question. It seems that the csv files loaded preceded the run date of your tool. I will report back after further investigation... sorry....


Yes, I WAS sure. Because I've tested it just before. My version of EXCEL, didn't import any data from csv created with official version of witploadae. I have no idea why. May be there are some slight differences between excel version (language versions, or US and NON US version...)
Grimasa
User avatar
rcm1616
Posts: 30
Joined: Wed Aug 05, 2009 3:51 am
Location: Bratislava, Slovakia, EU

RE: Submarine Reporter for Excel 2010 BETA

Post by rcm1616 »

ORIGINAL: PaxMondo

ORIGINAL: bk19@mweb.co.za
This unlikely to be the problem. It appears that the original code was written for use in east Europe.
Agreed. I had to repair the same two routines as you did to get the date/time standard aligned correctly so it would find the right file name. Initially it was looking for CombatReport2.txt for me.... [:D]

Mhm. In my language setting (slovak locale set) I'm able to write date in any form. (incl.US/UK s DD/MM/YY).
It seems, US version is not able to work with other then US/UK standard. OK, I can fix it. From now I will use only US standard...
Grimasa
User avatar
rcm1616
Posts: 30
Joined: Wed Aug 05, 2009 3:51 am
Location: Bratislava, Slovakia, EU

RE: Submarine Reporter for Excel 2010 BETA

Post by rcm1616 »

ORIGINAL: PaxMondo

ORIGINAL: bk19@mweb.co.za
This unlikely to be the problem. It appears that the original code was written for use in east Europe.
Agreed. I had to repair the same two routines as you did to get the date/time standard aligned correctly so it would find the right file name. Initially it was looking for CombatReport2.txt for me.... [:D]

I'm not sure, if I understand correctly... I was not good student of foreign languages...

So, you sad that after replacing "." with "/" in getdate routines all function of SReporter runs?

btw. sorry to all, combatreport2.txt bug was my very stupid mistake. if i will be able to spare some worktime tomorrow, i will upload repaired Sub Reporter within next 24 hours.
Grimasa
User avatar
PaxMondo
Posts: 9798
Joined: Fri Jun 06, 2008 3:23 pm

RE: Submarine Reporter for Excel 2010 BETA

Post by PaxMondo »

rcm1616 - THANKS for you efforts on this.  As you can see, it has generated a LOT of interest!   Just like the combat reporter tool, this looks to be one of my game standards.
 
Keep up the great work!!
Pax
User avatar
SgtSwanson
Posts: 212
Joined: Sun May 15, 2005 12:36 am
Location: Long Branch, NJ

RE: Submarine Reporter for Excel 2010 BETA

Post by SgtSwanson »

Macros are disabled in the general case as a mechanism to allow the user to protect him/herself from malicious code.

When the spreadsheet is loaded (in the newer versions) a toolbar option becomes available to enable the macro. This is a once off choice and must be immediately enabled before doing anything else. It is also possible to set the directory where this spreadsheet is stored to be a 'safe' source of spreadsheets and then macros will automatically be enabled.

I can't quite remember the mechanism for the older Excel application, but a similar option definately exists to enable macros in the spreadsheet. You will need to hunt around for it a bit.

Not in MS Excel Starter. See below

Image[/img]
Sgt Swanson
87-93 5/502 Inf. Berlin Bde
93-95 2/502 Inf. 101st Airborne Div.
Freedom is never free!!

Patch of the Week: 6th Infantry Division
bk19@mweb.co.za
Posts: 258
Joined: Tue Jul 26, 2011 8:27 pm

RE: Submarine Reporter for Excel 2010 BETA

Post by bk19@mweb.co.za »

Well Sgt Swanson,

I see from your screen dump that macros are not available in the starter edition at all.

It seems that you will have to upgrade or swap to OpenOffice (for example) to use this tool.
saw
Posts: 2
Joined: Sun Oct 09, 2011 9:54 pm

RE: Submarine Reporter for Excel 2010 BETA

Post by saw »

Not having any luck with this in open office either. No buttons (have Macros enabled under tools setting).

Any ideas? Looks like a great tool.
bk19@mweb.co.za
Posts: 258
Joined: Tue Jul 26, 2011 8:27 pm

RE: Submarine Reporter for Excel 2010 BETA

Post by bk19@mweb.co.za »

ORIGINAL: saw

Not having any luck with this in open office either. No buttons (have Macros enabled under tools setting).

Any ideas? Looks like a great tool.


Sorry I don't have OpenOffice installed. I am surprised by your posting though. I had understood that this product pretty much covered much of what was in the Office suite.
bk19@mweb.co.za
Posts: 258
Joined: Tue Jul 26, 2011 8:27 pm

RE: Submarine Reporter for Excel 2010 BETA

Post by bk19@mweb.co.za »

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...
Attachments
SubmarineReporter.zip
(568.29 KiB) Downloaded 43 times
User avatar
rcm1616
Posts: 30
Joined: Wed Aug 05, 2009 3:51 am
Location: Bratislava, Slovakia, EU

RE: Submarine Reporter for Excel 2010 BETA

Post by rcm1616 »

ORIGINAL: bk19@mweb.co.za


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...

Great work! Many thanks.
I had fixed all this bugs just few minutes before I have read your message.[:D]
But your solutions are more elegant... [;)] I will use them.

btw. My name is Richard. But I prefer nickname Grimasa. I don't know how (and if its possible) to change username on this forum...

Grimasa
User avatar
SgtSwanson
Posts: 212
Joined: Sun May 15, 2005 12:36 am
Location: Long Branch, NJ

RE: Submarine Reporter for Excel 2010 BETA

Post by SgtSwanson »

You have to start a new account to do that I think
Sgt Swanson
87-93 5/502 Inf. Berlin Bde
93-95 2/502 Inf. 101st Airborne Div.
Freedom is never free!!

Patch of the Week: 6th Infantry Division
User avatar
cantona2
Posts: 3749
Joined: Mon May 21, 2007 2:45 pm
Location: Gibraltar

RE: Submarine Reporter for Excel 2010 BETA

Post by cantona2 »

Thanks for the effors guys

I got the tool to load and work properly I think. The date of last loaded is 30/11/42, last date in game vs Herb. However all the fields are empy and there are no records at all. Does this work like Tracker where you see nothing in the first turn you load but yes thereafter? One more dumbass question, do I have to import records manually at every turn like we update in Tracker?
1966 was a great year for English Football...Eric was born

Post Reply

Return to “Tech Support”