MS Word Macro needed

[chinsf]

Hi all,

I have a .CSV file that exported from a DOS-based program and then import to ACCPAC. The exported file work just fine for ACCPAC 3.0A, but now we upgraded to ACCPAC 5.1A, there is some changes to the exported .CSV file format.
Below is the sample .CSV file that can suit to ACCPAC 3.0A :

"RECTYPE","CNTBTCH","CNTITEM","IDVEND","IDINVC","TEXTTRX","INVCDESC","DATEINVC","DATEDUE","AMTGROSTOT"
"RECTYPE","CNTBTCH","CNTITEM","CNTLINE","IDGLACCT","AMTDIST"
"RECTYPE","CNTBTCH","CNTITEM","CNTPAYM","DATEDUE","AMTDUE"
"1",20,1,"100430","82200830",1,"V03/07/0030",20030630,20030730,2773.730
"2",20,1,1,"J10055-000HHO",2773.730
"3",20,1,1,20030730,2773.730
"1",20,1,"100430","82201264",1,"V03/07/0031",20030701,20030731,41000.000
"2",20,1,1,"G10010-000HHO",28700.000
"3",20,1,1,20030731,28700.000
"2",20,1,2,"J10055-000HHO",6560.000
"3",20,1,2,20030731,6560.000
"2",20,1,3,"J10060-000HHO",5740.000
"3",20,1,3,20030731,5740.000
"1",20,1,"100430","82201762",1,"V03/07/0032",20030701,20030731,2630.000
"2",20,1,1,"J10055-000HHO",2630.000
"3",20,1,1,20030731,2630.000
"1",20,1,"100430","82225272",1,"V03/07/0109",20030721,20030820,40000.000
"2",20,1,1,"G10010-000HHO",24000.000
"3",20,1,1,20030820,24000.000
"2",20,1,2,"J10055-000HHO",8000.000
"3",20,1,2,20030820,8000.000
"2",20,1,3,"J10060-000HHO",8000.000
"3",20,1,3,20030820,8000.000

There are 3 record types for this .CSV file - either "1", "2", or "3". ACCPAC 5.1A does not support this format if it encounters the line read "1", "2", "3", "2", "3", which means there are 2 entries under 1 record header.

The above .CSV file got to me change into this format in order for ACCPAC 5.1A to read : "1", "2", "2", "3", where "1" is the record header, "2"s are the entries and "3" is the total amount for the entries.

I'm looking for a MS Word Macro that can help me to change the .CSV file in order to be imported into ACCPAC 5.1A.

Anyone has any ideas?

Regards,

Benny

 

[mblaster]

Normally you would open a .csv file in Excel not Word.

 

[TonyJollans]

Hi chinsf,

mblaster is right. Why do you want to do it in Word? It can be done, of course, but it is quite a bit harder.

Here is a hastily knocked up bit of code (which could be improved upon) which I think does what you want in Excel (based on your sample data).

Dim MyCell As Range
Dim Row1 As Long
For Each MyCell In Range([a1], [a1].End(xlDown))
    If MyCell = 1 Then
        Row1 = MyCell.Row
    ElseIf MyCell = 3 Then
        If MyCell.Offset(1) = 1 _
        Or IsEmpty(MyCell.Offset(1)) Then
            MyCell.Offset(, 5) = WorksheetFunction.Sum(Range(Cells(Row1 + 1, 6), Cells(MyCell.Row - 1, 6)))
        Else
            MyCell.EntireRow.Delete

' Note that deleting this row makes all lower rows move up one
            ' And what was the next row will be missed by loop control
            ' We get away with it because we never delete two consecutive rows

        End If
    End If
Next

Enjoy,
Tony