Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

MS Word Macro needed

Status
Not open for further replies.

chinsf

IS-IT--Management
Jul 11, 2003
15
MY
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
 
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).

Code:
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
Code:
' 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
Code:
        End If
    End If
Next

Enjoy,
Tony
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top