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

rcm1616 · Post by **rcm1616** » Sat Oct 08, 2011 11:08 am

Submarine Reporter for Excel 2010
*******************************************************************************************************************
Update 10/28/11
Version BETA 1.3 - another update from Bruce (bk19@mweb.co.za)

*******************************************************************************************************************
Update 10/14/11
Version BETA 1.2a - massive update from bk19@mweb.co.za
FIXED most of data importing related problems

*******************************************************************************************************************

Update 10/13/11
Last time I have uploaded wrong file. (SAME VERSION AS BEFORE) [:(]
Now you can download real version BETA1.1a ...

*******************************************************************************************************************

Update 10/10/11

Changes in version BETA1.1a (10/10/2011)

1. failure of DB import on platforms where WITP AE was installed on other then c: drive
FIXED - thanks to bk19@mweb.co.za

2. DATE format bug on US/UK Excel localisation
FIXED - thanks to bk19@mweb.co.za

3. all bugs in BAT file execution
FIXED - thanks to bk19@mweb.co.za

4. unable to import combat record where ship name in combat report is without class type prefix (example APD -107 instead APD APD -107)
script which write new record to Combat Reports table is unable to found long name match in ShipsDB
FIXED - class type name is no more added in front of ship name in case that ship name is number

*******************************************************************************************************************

I have developed MS Excel 2010 tool for parsing submarine combat records data from combat and operational reports.
This is strictly Excel2010 worksheet. I had never tested it on older version. It is first BETA version.
Your feedback will be welcomed.

Features:
- load and parse Sub Attack, Sub vs Sub, and ASW type records from combatreport file and save it to excel table
(only records where some damage on side of Target was reported - in case of ASW only records where submarine strikes back)

- append ship (target) tonnage, ship nationality and submarine nationality to records

- load and parse SUNK ship report and FALSELY REPORTED SUNK report types from operationsreport file

- generate reports (pivot tables) monthly tonnage (sunk/damaged) and Submarine top scoring list

This is strictly Excel2010 worksheet. I had never tested it on older version.

Configuration instructions:

Configuration Sheet:

Settings:
1.
WITP AE Path - path to WITP AE Game folder - to point import scripts to combatreport.txt a Xoperationsreport.txt files
2.
TMP File Path - path to any folder which can be used as temporary storage for small auxillary text file - do not need copy any game files there
3.
Import from and Import to date - values must be correctly in date format ( example: from: 7.12.1941 to 31.12.1941 or from 7/12/41 to 31/12/41)
Values are used for batch importing combat reports from [WITP AE Path]\SAVE\archive (combatreport_YYMMDD.txt and Xoperationsreport_YYMMDD.txt files)
4.
Scenario # - number of scenario from which ship data will be imported

Buttons:
1.
Database import - will import ship database from scenario files. Script use witploadAE utilitz to extract data from scenario to .csv files and then import data do Submarine Reporter. It is critical to wait until witploadae exports all files and then click OK button on messagebox to continue.
2.
Import record - will process last saved combat and operations reports from \SAVE folder
3.
Import archive records - will process reports from \SAVE\archive folder within selected range

Combat Reports Sheet:
Main database sheet - all submarine scoring records are stored here
Values in Sunk column:
DAMAGED - initial value (only combat records where sub commander reports damage of enemy ship are stored)
SUNK - sunk confirmed in operations report
FR SUNK - changed after reporting incorrect previous sunk report in operations report

MONTHS:
pivot tables with filters

AUX:
Values in nationality can be changed.

Download:

cantona2 · Post by **cantona2** » Sat Oct 08, 2011 12:09 pm

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?

rcm1616 · Post by **rcm1616** » Sat Oct 08, 2011 12:43 pm

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?

No you do not. SR runs witploadae automatically after you click on"Database Import Button.

You may see something like this:

No tests with several games yet. But SR opens only files named combatreport.txt, aoperationsreport.txt and joperationsreport.txt from \SAVE folder.

cantona2 · Post by **cantona2** » Sat Oct 08, 2011 1:13 pm

I see all that but i get an error saying the programme cannot find wtipcls001.csv in the scenario folder

Dan Nichols · Post by **Dan Nichols** » Sat Oct 08, 2011 1:31 pm

I didn't see the cmd window. I got a working icon for 3-4 minutes and then when I tried to see the data I got an error saying the program connot find wticls002.csv. I looked and there are no csv files in my scen folder.

rcm1616 · Post by **rcm1616** » Sat Oct 08, 2011 1:33 pm

ORIGINAL: cantona2

I see all that but i get an error saying the programme cannot find wtipcls001.csv in the scenario folder

And is there file with this name?
Is your WITP AE Path set correctly? - with no \ at end of line

If you click Debug on error message on which line of code program stops?

rcm1616 · Post by **rcm1616** » Sat Oct 08, 2011 1:41 pm

ORIGINAL: Dan Nichols

I didn't see the cmd window. I got a working icon for 3-4 minutes and then when I tried to see the data I got an error saying the program connot find wticls002.csv. I looked and there are no csv files in my scen folder.

Check if you have witploadae.exe in your SCEN folder. I use updated witploadae from BETA version.

cantona2 · Post by **cantona2** » Sat Oct 08, 2011 1:42 pm

'' Èo treba dorobi?:
''
''  - kontrolu èi nechcem importova? už naimportované dáta
''
''

''
''
''
''
''

Dim WITPAEPath, TMPPath, BATPath As String
Dim ActionDate As Date
Dim ImportFromDate, ImportToDate As Date
Dim ScenNo, Side As String

Sub MakeBAT()
Dim fs, f
Dim Line As String

    Line = "witploadAE.exe" & "/s" & ScenNo & " /e /b,"

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.OpenTextFile(BATPath, 8, True, 0)

    f.Writeline ("c:")
    f.Writeline ("cd " + WITPAEPath + "\SCEN")
    f.Writeline (Line)
    f.Close

End Sub

Sub DelBAT()
Dim fs, f

    Set fs = CreateObject("Scripting.FileSystemObject")
    fs.DeleteFile BATPath

End Sub
Sub DeleteSheet(strSheetName As String)
' deletes a sheet named strSheetName in the active workbook
    Application.DisplayAlerts = False
    Sheets(strSheetName).Delete
    Application.DisplayAlerts = True
End Sub

Sub ImportDataWorksheets()
Dim LastRow As Double
Dim FileName, BookName, SheetName As String
Dim MyBookName As String
    
    ''DeleteSheet ("Classes")
    ''DeleteSheet ("Ships")
       
    SheetName = "witpcls" + ScenNo
    BookName = SheetName + ".csv"
    FileName = WITPAEPath + "\SCEN\" + BookName
    MyBookName = ActiveWorkbook.Name
   
   
    Workbooks.Open (FileName), , , 2
    Workbooks(BookName).Sheets(1).Copy _
         After:=Workbooks(MyBookName).Sheets("AUX")

    Workbooks(BookName).Close SaveChanges:=False
    Worksheets(SheetName).Name = "Classes"

    SheetName = "witpshp" + ScenNo
    BookName = SheetName + ".csv"
    FileName = WITPAEPath + "\SCEN\" + SheetName
   
   
    Workbooks.Open (FileName), , , 2
    Workbooks(BookName).Sheets(1).Copy _
         After:=Workbooks(MyBookName).Sheets("AUX")
        
    Workbooks(BookName).Close SaveChanges:=False
    Worksheets(SheetName).Name = "Ships"
   
    Call PrepareShipsDB
    DeleteSheet ("Classes")
    DeleteSheet ("Ships")
   
End Sub

Sub PrepareShipsDB()
Dim MyWrkSht As Worksheet
Dim MyRange As Range
Dim i, j, LastRow, LastRowClasses As Double
Dim s As String
Dim MyVarA, MyVarB

' Clear all Cells in ShipsDB to prepare sheet to new data
LastRow = GetLastRow("ShipsDB")
s = "A2:" & "K" & CStr(LastRow)
Set MyWrkSht = ActiveWorkbook.Worksheets("ShipsDB")
Set MyRange = MyWrkSht.Range(s)
MyRange.ClearContents

LastRowClasses = GetLastRow("Classes")
LastRow = GetLastRow("Ships")
i = 2
j = 2

Do While i <= LastRow
    If ActiveWorkbook.Worksheets("Ships").Cells(i, 3) <> 0 Then
        '' SHIP ID
        MyWrkSht.Cells(j, 1).Value = ActiveWorkbook.Worksheets("Ships").Cells(i, 1)
        '' SHIP NAME
        MyWrkSht.Cells(j, 2).Value = ActiveWorkbook.Worksheets("Ships").Cells(i, 2)
        '' CLASS ID
        MyWrkSht.Cells(j, 3).Value = ActiveWorkbook.Worksheets("Ships").Cells(i, 3)
        '' NATIONALITY ID
        MyWrkSht.Cells(j, 4).Value = ActiveWorkbook.Worksheets("Ships").Cells(i, 15)
        '' NATIONALITY
        MyVarA = Application.WorksheetFunction.Match(MyWrkSht.Cells(j, 4).Value, Worksheets("AUX").Range("D2:D19"))
        MyVarB = Application.WorksheetFunction.HLookup("Nationality", Worksheets("AUX").Range("E2:E19"), MyVarA)
        MyWrkSht.Cells(j, 5).Value = MyVarB
        '' Type ID
        s = "A2:A" & CStr(LastRowClasses)
        MyVarA = Application.WorksheetFunction.Match(MyWrkSht.Cells(j, 3).Value, Worksheets("Classes").Range(s))
        MyWrkSht.Cells(j, 6).Value = ActiveWorkbook.Worksheets("Classes").Cells(MyVarA + 1, 3).Value
        '' Type
        MyVarA = Application.WorksheetFunction.Match(MyWrkSht.Cells(j, 6).Value, Worksheets("AUX").Range("A2:A84"))
        MyVarB = Application.WorksheetFunction.HLookup("Type", Worksheets("AUX").Range("B2:B84"), MyVarA)
        MyWrkSht.Cells(j, 7).Value = MyVarB
        '' Full Name
        s = MyWrkSht.Cells(j, 7).Value & " " & MyWrkSht.Cells(j, 2).Value
        MyWrkSht.Cells(j, 8).Value = s
        '' Tonnage
        s = "A2:A" & CStr(LastRowClasses)
        MyVarA = Application.WorksheetFunction.Match(MyWrkSht.Cells(j, 3).Value, Worksheets("Classes").Range(s))
        MyWrkSht.Cells(j, 9).Value = ActiveWorkbook.Worksheets("Classes").Cells(MyVarA + 1, 22).Value
        j = j + 1
    End If
    i = i + 1
Loop

''MyWrkSht.Range(s).FormatConditions

Set MyRange = Nothing
Set MyWrkSht = Nothing

End Sub

Sub LoadConfig()

    WITPAEPath = Worksheets("Configuration").Cells(3, 3).Value
    TMPPath = Worksheets("Configuration").Cells(4, 3).Value + "\RCMTMP.txt"
    BATPath = Worksheets("Configuration").Cells(3, 3).Value + "\SCEN\DUMPSCEN.bat"
    ImportFromDate = Worksheets("Configuration").Cells(5, 3).Value
    ImportToDate = Worksheets("Configuration").Cells(6, 3).Value
    ScenNo = Worksheets("Configuration").Cells(7, 3).Value
    Side = Worksheets("Configuration").Cells(8, 3).Value

End Sub

Sub LoadDB()
Dim RetVar
Dim ImWrkSht As Worksheet
Dim ImRange As Range
Dim MsgBoxResponse As String

    LoadConfig
    MakeBAT
    RetVar = Shell(BATPath, vbNormalFocus)
    MsgBoxResponse = MsgBox("Wait for dumping end, then press OK", vbOKOnly)
   
   
    DelBAT
    ImportDataWorksheets

   
   

End Sub
Function GetLastRow(WrkShtName As String) As Double

Dim MyWrkSht As Worksheet
Dim MyRange As Range

    Set MyWrkSht = ActiveWorkbook.Worksheets(WrkShtName)
    Set MyRange = MyWrkSht.UsedRange

    GetLastRow = MyRange.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Set MyRange = Nothing
    Set MyWrkSht = Nothing

End Function

Sub ImportReport()
Dim Path As String

    LoadConfig
    Path = WITPAEPath + "\SAVE\combatreport2.txt"
    OpenCombatReport (Path)
    Path = WITPAEPath + "\SAVE\" & Side & "operationsreport2.txt"
    OpenOperationsReport (Path)
       
       
    Call RemoveDuplicates
    Call RefreshPivotTables
       
End Sub
Sub RemoveDuplicates()
Dim MyWrkSht As Worksheet
Dim MyRange As Range
Dim a

    Set MyWrkSht = ActiveWorkbook.Worksheets("Combat Reports")
    Set MyRange = MyWrkSht.UsedRange
  
    MyRange.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7), Header:=xlYes
   
    Set MyRange = Nothing
    Set MyWrkSht = Nothing
End Sub
Sub RefreshPivotTables()
Dim s As String
Dim LastRow As Double

    LastRow = GetLastRow("Combat Reports")
    s = "Combat Reports!R1C1:R" & CStr(LastRow) & "C7"

    ActiveWorkbook.Worksheets("MONTHS").PivotTables("MonthReview").SourceData = s
    ActiveWorkbook.Worksheets("MONTHS").PivotTables("SubmarineReview").SourceData = s
    ActiveWorkbook.Worksheets("MONTHS").PivotTables("MonthReview").RefreshTable
    ActiveWorkbook.Worksheets("MONTHS").PivotTables("SubmarineReview").RefreshTable

End Sub
Sub OpenOperationsReport(ORPath As String)
Dim fs, f
Dim LastRow As Double
Dim ShipName As String
Dim SunkDate As Date
Dim MyLine
Dim s As String
Dim MyWrkSht As Worksheet
Dim MyRange As Range
   
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile(ORPath, 1, 0)
MyLine = ""

Set MyWrkSht = ActiveWorkbook.Worksheets("Combat Reports")

Set MyRange = MyWrkSht.UsedRange
LastRow = GetLastRow("Combat Reports")

Do While f.AtEndOfStream = False
    MyLine = f.Readline
    If InStr(1, MyLine, "reported to have been sunk", vbTextCompare) > 0 Then
        ShipName = GetSunkShipName(MyLine)
        SunkDate = GetSunkShipDate(MyLine)
        Set MyRange = MyWrkSht.Rows(1)
        i = 2
        Do While i <= LastRow
            If MyWrkSht.Cells(i, 1).Value = SunkDate Then
                If MyWrkSht.Cells(i, 4).Value = ShipName Then
                    MyWrkSht.Cells(i, 6).Value = "SUNK"
                    i = LastRow
                End If
            End If
            i = i + 1
        Loop
    End If
    If InStr(1, MyLine, "Previous report of sinking", vbTextCompare) > 0 Then
        ShipName = GetFalseReportedShipName(MyLine)
        ''SunkDate = GetSunkShipDate(MyLine)
        Set MyRange = MyWrkSht.Rows(1)
        i = LastRow
        Do While (MyWrkSht.Cells(i, 4).Value <> ShipName) And (i > 1)
            i = i - 1
        Loop
        If MyWrkSht.Cells(i, 4).Value = ShipName Then MyWrkSht.Cells(i, 6).Value = "FR SUNK"
    End If
Loop

f.Close
End Sub

Function GetSunkShipName(MyLine) As String
Dim i As Long

    i = InStr(1, MyLine, "is reported", vbTextCompare) - 2
    GetSunkShipName = Left(MyLine, i)

End Function
Function GetShipShortName(LongShipName As String) As String

Dim i As Double
    i = InStr(LongShipName, " ")
    GetShipShortName = Right(LongShipName, Len(LongShipName) - i)
End Function

Function GetFalseReportedShipName(MyLine) As String
Dim i, j As Long
Dim s As String

    i = InStr(1, MyLine, "of sinking of", vbTextCompare) + 13
    j = Len(MyLine)
    s = Right(MyLine, j - i)
    i = InStr(1, s, "incorrect", vbTextCompare) - 2
    GetFalseReportedShipName = Left(s, i)
End Function

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
    GetSunkShipDate = DateValue(s)

End Function

Sub ImportArchiveReports()
Dim ArchivePath, Path As String
Dim m As Integer
Dim YY, MM, DD, s As String
Dim iDay As Date
    LoadConfig
    ArchivePath = Worksheets("Configuration").Cells(3, 3).Value + "\SAVE\archive"
    iDay = ImportFromDate

    '' Sem by mala ešte prís? kontrola, èi už reporty z daného dátumu neboli naimportované
   

    Do While iDay <= ImportToDate
        DD = Day(iDay)
        If Len(DD) < 2 Then DD = "0" & DD
        m = Month(iDay)
        If m < 10 Then
            MM = "0" & CStr(m)
        Else: MM = CStr(m)
        End If
        YY = Year(iDay)
        YY = Right(YY, 2)
        Path = ArchivePath + "\combatreport_" + CStr(YY) + CStr(MM) + CStr(DD) + ".txt"
        If Dir(Path) <> "" Then OpenCombatReport (Path)
        Path = ArchivePath & "\" & Side & "operationsreport_" + CStr(YY) + CStr(MM) + CStr(DD) + ".txt"
        If Dir(Path) <> "" Then OpenOperationsReport (Path)
              
        iDay = iDay + 1
    Loop
    Call RemoveDuplicates
    Call RefreshPivotTables

End Sub

Sub OpenCombatReport(ReportPath As String)
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Dim tf, fs, f
    Dim Path, Line As String
    Dim i As Integer
      
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.OpenTextFile(ReportPath, 1, 0)
    Set tf = fs.OpenTextFile(TMPPath, 8, True, 0)

    i = 1
    ParserStream = ""
      
    Do While f.AtEndOfStream = False
        Line = f.Readline
        If Line = "--------------------------------------------------------------------------------" Then
            ' call parser Sub with ParserStream argument
            tf.Close
            ParseModule (TMPPath)
            fs.DeleteFile (TMPPath)
           
            Set tf = fs.OpenTextFile(TMPPath, 8, True, 0)
        Else
            If IsNewDay(Line) Then ActionDate = GetDayDate(Line)
            tf.Writeline (Line)
        End If
        i = i + 1
    Loop
    Worksheets("Configuration").Cells(10, 3).Value = ActionDate
    f.Close
End Sub

Function IsNewDay(s As String) As Boolean

    If Mid(s, 1, 12) = "AFTER ACTION" Then
    IsNewDay = True
    Else: IsNewDay = False
    End If
   
End Function

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
    GetDayDate = DateValue(s)

End Function

Sub ParseModule(TMPPath As String)

Dim Line, Ship As String
Dim fs, f
Dim arr
Dim s
Dim RowB, RowD As String

Dim JapArr(15) As String
Dim AllArr(15) As String
Dim i As Integer
Dim LastRow&
Dim isJapAttacker As Boolean

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile(TMPPath, 1, 0)
    Line = f.Readline
    i = 0
    isJapAttacker = False
Select Case Mid(Line, 1, 3)
    Case "Sub"
        ' Parse Ship Names from rcm_tmp.txt to JappArr and AllArr collections of ship names
        Do While f.AtEndOfStream = False
        Line = f.Readline
        If InStr(Line, "Japanese Ships") > 0 Then
            Do Until Line = " "
            Line = f.Readline
            arr = Split(Line, ",", , 1)
            If (UBound(arr) > 0 Or InStr(Line, "SS")) Then
                Ship = arr(0)
                Ship = Mid(Ship, 7)
                JapArr(i) = Ship
                ' if Jap ship is SS then set isJapAttacker as true
                If InStr(Line, "SS") Then isJapAttacker = True
                i = i + 1
            End If
            Loop
            i = 0
        End If
        If InStr(Line, "Allied Ships") > 0 Then
        Do Until Line = " "
            Line = f.Readline
            arr = Split(Line, ",", , 1)
            If (UBound(arr) > 0 Or InStr(Line, "SS")) Then
                Ship = arr(0)
                Ship = Mid(Ship, 7)
                AllArr(i) = Ship
                ' if isJapAttacker is set as true (jap ship was SS) AND Allied ship is SS then if Allied ship is NOT damaged then isJapAttacker set to false
                If isJapAttacker Then
                   If (UBound(arr) = 0 And InStr(Line, "SS")) Then isJapAttacker = False
                End If
                i = i + 1
            End If
            Loop
        End If
        Loop
        If isJapAttacker Then
            i = 0
            Do While AllArr(i) <> ""
                Call WriteCRRow(ActionDate, JapArr(0), AllArr(i))
                i = i + 1
            Loop
        Else
            i = 0
            Do While JapArr(i) <> ""
                Call WriteCRRow(ActionDate, AllArr(0), JapArr(i))
                i = i + 1
            Loop
        End If
        isJapAttacker = False
    Case "ASW"
        Do While f.AtEndOfStream = False
        Line = f.Readline
        If InStr(Line, "Japanese Ships") > 0 Then
            Do Until Line = " "
            Line = f.Readline
            arr = Split(Line, ",", , 1)
            If (UBound(arr) > 0 Or InStr(Line, "SS")) Then
                Ship = arr(0)
                Ship = Mid(Ship, 7)
                JapArr(i) = Ship
                ' if Jap ship is SS then set isJapAttacker as true
                If InStr(Line, "SS") Then isJapAttacker = True
                i = i + 1
            End If
            Loop
            i = 0
        End If
        If InStr(Line, "Allied Ships") > 0 Then
        Do Until Line = " "
            Line = f.Readline
            arr = Split(Line, ",", , 1)
            If (UBound(arr) > 0 Or InStr(Line, "SS")) Then
                Ship = arr(0)
                Ship = Mid(Ship, 7)
                AllArr(i) = Ship
                ' if isJapAttacker is set as true (jap ship was SS) AND Allied ship is SS then if Allied ship is NOT damaged then isJapAttacker set to false
                If isJapAttacker Then
                   If (UBound(arr) = 0 And InStr(Line, "SS")) Then isJapAttacker = False
                   End If
                i = i + 1
            End If
            Loop
        End If
        Loop
        If isJapAttacker Then
            i = 0
            Do While AllArr(i) <> ""
                Call WriteCRRow(ActionDate, JapArr(0), AllArr(i))
                i = i + 1
            Loop
        Else
            i = 0
            Do While JapArr(i) <> ""
                Call WriteCRRow(ActionDate, AllArr(0), JapArr(i))
                i = i + 1
            Loop
        End If
        isJapAttacker = False
    Case Else
End Select

i = 0
f.Close
End Sub
Function GetMonth(ParsedDateString As String) As String

Select Case ParsedDateString

    Case "Jan"
        GetMonth = "01"
    Case "Feb"
        GetMonth = "02"
    Case "Mar"
        GetMonth = "03"
    Case "Apr"
        GetMonth = "04"
    Case "May"
        GetMonth = "05"
    Case "Jun"
        GetMonth = "06"
    Case "Jul"
        GetMonth = "07"
    Case "Aug"
        GetMonth = "08"
    Case "Sep"
        GetMonth = "09"
    Case "Oct"
        GetMonth = "10"
    Case "Nov"
        GetMonth = "11"
    Case "Dec"
        GetMonth = "12"
    End Select

End Function

Private Sub WriteCRRow(RowA As Date, RowB As String, RowD As String)

Dim MyWrkSht As Worksheet
Dim MyRange As Range
Dim LastRow&, LastRowShipsDB
Dim s, ss As String
Dim MyVarA, i As Double
Dim err As Variant

Set MyWrkSht = ActiveWorkbook.Worksheets("Combat Reports")
Set MyRange = MyWrkSht.UsedRange

    LastRow = MyRange.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LastRowShipsDB = GetLastRow("ShipsDB")
    s = "H1:H" & CStr(LastRowShipsDB)
    MyVarA = Application.WorksheetFunction.Match(RowB, Worksheets("ShipsDB").Range(s), 0)
    i = ActiveWorkbook.Worksheets("ShipsDB").Cells(MyVarA, 4).Value
   
    If (Side = "a" And i > 3) Or (Side = "j" And i < 3) Then
        '' Date
        MyWrkSht.Cells(LastRow + 1, 1).Value = RowA
        '' Long Submarine Name
        MyWrkSht.Cells(LastRow + 1, 2).Value = RowB
        '' Submarine Nation
        MyWrkSht.Cells(LastRow + 1, 3).Value = ActiveWorkbook.Worksheets("ShipsDB").Cells(MyVarA, 5).Value
        '' Long Victim Name
        MyWrkSht.Cells(LastRow + 1, 4).Value = RowD
        '' Tonnage
       
       
       
        '' if ship type is same MATCH then write tonnage
        s = "H1:H" & CStr(LastRowShipsDB)
        err = Application.Match(RowD, Worksheets("ShipsDB").Range(s), 0)
        If IsError(err) Then
            s = "B1:B" & CStr(LastRowShipsDB)
            ss = GetShipShortName(RowD)
            MyVarA = Application.Match(ss, Worksheets("ShipsDB").Range(s), 0)
            MyWrkSht.Cells(LastRow + 1, 5).Value = ActiveWorkbook.Worksheets("ShipsDB").Cells(MyVarA, 9).Value
       
        '' If ship type is diferent then lookup next short name match
        Else
            s = "H1:H" & CStr(LastRowShipsDB)
            MyVarA = Application.Match(RowD, Worksheets("ShipsDB").Range(s), 0)
            MyWrkSht.Cells(LastRow + 1, 5).Value = ActiveWorkbook.Worksheets("ShipsDB").Cells(MyVarA, 9).Value

        End If
        MyWrkSht.Cells(LastRow + 1, 6).Value = "DAMAGED"
        If Len(Month(RowA)) < 2 Then
            ss = Year(RowA) & "/0" & Month(RowA)
        Else: ss = Year(RowA) & "/" & Month(RowA)
        End If
        MyWrkSht.Cells(LastRow + 1, 7).Value = ss
       
    End If

Set MyRange = Nothing
Set MyWrkSht = Nothing

End Sub

Here is the debug dialouge
 

cantona2 · Post by **cantona2** » Sat Oct 08, 2011 1:43 pm

There is no file with that name in the scen folder of my AE install. I am running the latest official patch.

rcm1616 · Post by **rcm1616** » Sat Oct 08, 2011 1:50 pm

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.

cantona2 · Post by **cantona2** » Sat Oct 08, 2011 1:53 pm

Do i just need the witploadae BETA or i need to upgrade to Beta patch fully?

cantona2 · Post by **cantona2** » Sat Oct 08, 2011 1:55 pm

I have had a look in the Scen folders of both installs of AE I have and there are no CSV files in any of them!?!?!?!

rcm1616 · Post by **rcm1616** » Sat Oct 08, 2011 2:07 pm

ORIGINAL: cantona2

Do i just need the witploadae BETA or i need to upgrade to Beta patch fully?

You just need to copy this file to SCEN folder. (and don't forget to rename or copy somewhere else old version)

Dan Nichols · Post by **Dan Nichols** » Sat Oct 08, 2011 2:09 pm

Here is what the dumpscen.bat file looks like:

c:
cd D:\War in the Pacific Admiral's Edition\PBEM game vs obvert\SCEN
witploadAE.exe/s002 /e /b,

when I run it I get this:

D:\War in the Pacific Admiral's Edition\PBEM game vs obvert\SCEN>c:

C:\>cd D:\War in the Pacific Admiral's Edition\PBEM game vs obvert\SCEN

C:\>witploadAE.exe/s002 /e /b,
'witploadAE.exe' is not recognized as an internal or external command,
operable program or batch file.

C:\>pause
Press any key to continue . . .

I think you need to put " around the file name.
and you are not handling the case where we have the game
installed to a disk other than c:

I changed my dumpscen.bat file to this and it extracted the files

d:
cd "D:\War in the Pacific Admiral's Edition\PBEM game vs obvert\SCEN"
witploadAE.exe/s002 /e /b,
pause

It does work on my 2007 excel.

rcm1616 · Post by **rcm1616** » Sat Oct 08, 2011 2:18 pm

ORIGINAL: Dan Nichols

Here is what the dumpscen.bat file looks like:

c:
cd D:\War in the Pacific Admiral's Edition\PBEM game vs obvert\SCEN
witploadAE.exe/s002 /e /b,

when I run it I get this:

D:\War in the Pacific Admiral's Edition\PBEM game vs obvert\SCEN>c:

C:\>cd D:\War in the Pacific Admiral's Edition\PBEM game vs obvert\SCEN

C:\>witploadAE.exe/s002 /e /b,
'witploadAE.exe' is not recognized as an internal or external command,
operable program or batch file.

C:\>pause
Press any key to continue . . .

I think you need to put " around the file name.
and you are not handling the case where we have the game
installed to a disk other than c:

I changed my dumpscen.bat file to this and it extracted the files

d:
cd "D:\War in the Pacific Admiral's Edition\PBEM game vs obvert\SCEN"
witploadAE.exe/s002 /e /b,
pause

Yeah. It looks like bug. I must fix it in next version.

Many thanks Dan

cantona2 · Post by **cantona2** » Sat Oct 08, 2011 2:28 pm

OK, tried it with new beta and dumping started but there are no changes to the excel sheet, it stays exactly the same, ie with the same data that i downloaded it with

rcm1616 · Post by **rcm1616** » Sat Oct 08, 2011 2:47 pm

ORIGINAL: cantona2

OK, tried it with new beta and dumping started but there are no changes to the excel sheet, it stays exactly the same, ie with the same data that i downloaded it with

I left in ShipDB sheet data from scenario 1 in first version of file.
If you're importing scen 1 or 2, data in ShipDB must be same.

If you have version downloaded from Damien site you may have some forgotten data in Combat Reports sheet. Just delete all rows in Combat Reports sheet starting with second one (headings must stay intact)

Then you can import your own combatreports.

SgtSwanson · Post by **SgtSwanson** » Sat Oct 08, 2011 3:49 pm

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.[:@][:@]

Pascal_slith · Post by **Pascal_slith** » Sat Oct 08, 2011 4:10 pm

You're much better off developing this in Excel 2003. I've encountered few people that have 2007 or 2010 (and lots of complaints about the last two).

rcm1616 · Post by **rcm1616** » Sat Oct 08, 2011 4:15 pm

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.[:@][:@]

So it look like another reason to make version 2 on other (more platform independent) technology. My first plan was just make small reporting tool for myself. Sharing it with others came in second wave...

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.

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

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

RE: Submarine Reporter for Excel 2010 BETA

RE: Submarine Reporter for Excel 2010 BETA

RE: Submarine Reporter for Excel 2010 BETA

RE: Submarine Reporter for Excel 2010 BETA

RE: Submarine Reporter for Excel 2010 BETA

RE: Submarine Reporter for Excel 2010 BETA

RE: Submarine Reporter for Excel 2010 BETA

RE: Submarine Reporter for Excel 2010 BETA

RE: Submarine Reporter for Excel 2010 BETA

RE: Submarine Reporter for Excel 2010 BETA

RE: Submarine Reporter for Excel 2010 BETA

RE: Submarine Reporter for Excel 2010 BETA

RE: Submarine Reporter for Excel 2010 BETA

RE: Submarine Reporter for Excel 2010 BETA

RE: Submarine Reporter for Excel 2010 BETA

RE: Submarine Reporter for Excel 2010 BETA

RE: Submarine Reporter for Excel 2010 BETA

RE: Submarine Reporter for Excel 2010 BETA

RE: Submarine Reporter for Excel 2010 BETA