Notes tests, text files, manipulation of text files in Excel and with Excel VBA CSV stuff

**DocAElstein** · 02-17-2021, 06:56 PM

In support of this post
https://excelfox.com/forum/showthrea...5355#post15355

_____ Workbook: Sample excel file.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F	G	H	I
1	Entity ID	day	month	year	time		1	<-- row number
2	700	19	2	2021	08:00		2
3	700	19	2	2021	08:30		3
4	700	20	2	2021	09:00		4
5	701	19	2	2021	09:30		5
6							6
7		2	3	4	5	<-- column number
8
9		Lr=5
10			arrIn()=Range("A1:E5").Value	1	2	3	4	5	6
11			1	Entity ID	day	month	year	time
12			2	700	19	2	2021	08:00
13			3	700	19	2	2021	08:30
14			4	700	20	2	2021	09:00
15			5	701	19	2	2021	09:30
16			6
17					Example: arrIn(5, 1) = 701

Worksheet: Sheet1

text file output

HTML Code:

<forecast>
<Entity>700</Entity>
<data>
<date>
<day>19/<day>
<month>2</month>
<year>2021</year>
</date>
<time>08:00</time>
<time>08:30</time>
</data>
<data>
<date>
<day>20/<day>
<month>2</month>
<year>2021</year>
</date>
<time>09:00</time>
</data>
</forcast>
<forecast>
<Entity>701</Entity>
<data>
<date>
<day>19/<day>
<month>2</month>
<year>2021</year>
</date>
<time>09:30</time>
</data>
</forcast>

Code:

Option Explicit
'
Sub ExcelToXML()
Rem 1 worksheets data info
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item(1)
Dim Lr As Long: Let Lr = Ws1.Range("A" & Rows.Count & "").End(xlUp).Row
Dim arrRng() As Variant: Let arrRng() = Ws1.Range("A1:E" & Lr + 1 & "").Value '  +1 is a bodge to help me not get errors when checking 1 row above my data
Rem 2  Do it
Dim TotalFile As String
Dim Rw As Long: Let Rw = 2 ' Main row count
' #STEP 1 Start
    Do While Rw <= Lr ' This keeps us going as long as data is there
     Let TotalFile = TotalFile & "<forecast>" & vbCr & vbLf & "<Entity>" & arrRng(Rw, 1) & "</Entity>" & vbCr & vbLf: Debug.Print TotalFile
    ' # STEP 2 start
     Let TotalFile = TotalFile & "<data>" & vbCr & vbLf & "<date>" & vbCr & vbLf & "<day>" & arrRng(Rw, 2) & "/<day>" & vbCr & vbLf & "<month>" & arrRng(Rw, 3) & "</month>" & vbCr & vbLf & "<year>" & arrRng(Rw, 4) & "</year>" & vbCr & vbLf & "</date>" & vbCr & vbLf & "<time>" & Format(arrRng(Rw, 5), "hh" & ":" & "mm") & "</time>" & vbCr & vbLf: Debug.Print TotalFile
        '  #STEP 3 START
        ' Check   if Entity ID in first row = Entity ID in 2nd row and date in first row = date in 2nd row then      repeat STEP 3 for 2nd row and so on
        Do While Rw + 1 <= Lr And arrRng(Rw, 1) = arrRng(Rw + 1, 1) And arrRng(Rw, 2) = arrRng(Rw + 1, 2)
         Let TotalFile = TotalFile & "<time>" & Format(arrRng(Rw + 1, 5), "hh" & ":" & "mm") & "</time>" & vbCr & vbLf: Debug.Print TotalFile
         Let Rw = Rw + 1 ' This brings us to the line we just filled in
        Loop
     Let TotalFile = TotalFile & "</data>" & vbCr & vbLf: Debug.Print TotalFile
        
        ' Chect  if Entity ID in first row = Entity ID in 2nd row and date in first row not equals to date in 2nd row then    repeat STEP 2 for 2nd row and so on
        Do While Rw + 1 <= Lr And arrRng(Rw, 1) = arrRng(Rw + 1, 1) '   And Not arrRng(Rw, 2) = arrRng(Rw + 1, 2)
         Let TotalFile = TotalFile & "<data>" & vbCr & vbLf & "<date>" & vbCr & vbLf & "<day>" & arrRng(Rw + 1, 2) & "/<day>" & vbCr & vbLf & "<month>" & arrRng(Rw + 1, 3) & "</month>" & vbCr & vbLf & "<year>" & arrRng(Rw + 1, 4) & "</year>" & vbCr & vbLf & "</date>" & vbCr & vbLf & "<time>" & Format(arrRng(Rw + 1, 5), "hh" & ":" & "mm") & "</time>" & vbCr & vbLf: Debug.Print TotalFile
         Let Rw = Rw + 1 ' This brings us to the line we just filled in
        Loop
     Let TotalFile = TotalFile & "</data>" & vbCr & vbLf: Debug.Print TotalFile
    
        '  #STEP 3 END
        '  STEP 2 END
     Let TotalFile = TotalFile & "</forcast>" & vbCr & vbLf: Debug.Print TotalFile
     Let Rw = Rw + 1 '  ' This brings us to the next line
    '    STEP 1 END
    Loop '  While Rw <= Lr
 
 Let TotalFile = Replace(TotalFile, "</data>" & vbCr & vbLf & "</data>" & vbCr & vbLf, "</data>" & vbCr & vbLf, 1, -1, vbBinaryCompare): Debug.Print TotalFile ' I end up with a double  "</data>" & vbCr & vbLf
Rem 3   Make text file
Dim FileNum2 As Long: Let FileNum2 = FreeFile(0)                                  ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName2 As String
 Let PathAndFileName2 = ThisWorkbook.Path & "\" & "XML_Stuff.txt"  ' ' CHANGE TO SUIT  ' Will be made if not there
 Open PathAndFileName2 For Output As #FileNum2
 Print #FileNum2, TotalFile ' write out entire text file
 Close #FileNum2

End Sub

'    <forecast>             ' #STEP 1 Start     Print #intFile, "<Forecast>"
'    <Entity>700</Entity>   ' #STEP 1 Start     Print #intFile, "<Entity>" & Entity ID & "</Entity>"
'    <data>                 ' #STEP 2 Start     Print #intFile, "<Data>"
'    <date>                 ' #STEP 2 Start     Print #intFile, "<date>"
'    <day>19</day>          ' #STEP 2 Start     Print #intFile, "<day>" & day &
'    <month>2</month>       ' #STEP 2 Start              "</day><month>" & month & "</month>
'    <year>2021</year>      ' #STEP 2 Start              <year>" & yeear & "</year>"
'    </date>                ' #STEP 2 Start                  </date>"
'    <time>8:00</time>      ' #STEP 3 START     Print #intFile, "<time>" & time & "</time>"
'          Check  if Entity ID in first row = Entity ID in 2nd row
            ' and date in first row = date in 2nd row then
    '    <time>8:30</time>      ' repeat STEP 3 for 2nd row and so on
    '    </data>           ' #STEP 3 END
    '          Check  if Entity ID in first row = Entity ID in 2nd row
                ' and date in first row IS NOT =   date in 2nd row then'
        '    repeat STEP ??3??  2 for 2nd row and so on
        '    <data>
        '    <date>
        '    <day>20</day>
        '    <month>2</month>
        '    <year>2021</year>
        '    </date>
        '    <time> ??8:00??  9.00   </time>
        '    </data>
    '    </forecast>       ' STEP 2 END           Print #intFile, "</forecast>"


'    If Entity ID is not same as in previous row repeat STEP 1
'
'    <forecast>
'    <Entity>701</Entity>
'    <data>
'    <date>
'    <day>19</day>
'    <month>2</month>
'    <year>2021</year>
'    </date>
'    <time>9:30</time>
'    </data>
'    </forecast>
'    <forecast>

**DocAElstein** · 02-17-2021, 06:56 PM

In support of this post
https://excelfox.com/forum/showthrea...5355#post15355

_____ Workbook: Sample excel file.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F	G	H	I
1	Entity ID	day	month	year	time		1	<-- row number
2	700	19	2	2021	08:00		2
3	700	19	2	2021	08:30		3
4	700	20	2	2021	09:00		4
5	701	19	2	2021	09:30		5
6							6
7		2	3	4	5	<-- column number
8
9		Lr=5
10			arrIn()=Range("A1:E5").Value	1	2	3	4	5	6
11			1	Entity ID	day	month	year	time
12			2	700	19	2	2021	08:00
13			3	700	19	2	2021	08:30
14			4	700	20	2	2021	09:00
15			5	701	19	2	2021	09:30
16			6
17					Example: arrIn(5, 1) = 701

Worksheet: Sheet1

text file output

HTML Code:

<forecast>
<Entity>700</Entity>
<data>
<date>
<day>19/<day>
<month>2</month>
<year>2021</year>
</date>
<time>08:00</time>
<time>08:30</time>
</data>
<data>
<date>
<day>20/<day>
<month>2</month>
<year>2021</year>
</date>
<time>09:00</time>
</data>
</forcast>
<forecast>
<Entity>701</Entity>
<data>
<date>
<day>19/<day>
<month>2</month>
<year>2021</year>
</date>
<time>09:30</time>
</data>
</forcast>

Code:

Option Explicit
'
Sub ExcelToXML()
Rem 1 worksheets data info
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item(1)
Dim Lr As Long: Let Lr = Ws1.Range("A" & Rows.Count & "").End(xlUp).Row
Dim arrRng() As Variant: Let arrRng() = Ws1.Range("A1:E" & Lr + 1 & "").Value '  +1 is a bodge to help me not get errors when checking 1 row above my data
Rem 2  Do it
Dim TotalFile As String
Dim Rw As Long: Let Rw = 2 ' Main row count
' #STEP 1 Start
    Do While Rw <= Lr ' This keeps us going as long as data is there
     Let TotalFile = TotalFile & "<forecast>" & vbCr & vbLf & "<Entity>" & arrRng(Rw, 1) & "</Entity>" & vbCr & vbLf: Debug.Print TotalFile
    ' # STEP 2 start
     Let TotalFile = TotalFile & "<data>" & vbCr & vbLf & "<date>" & vbCr & vbLf & "<day>" & arrRng(Rw, 2) & "/<day>" & vbCr & vbLf & "<month>" & arrRng(Rw, 3) & "</month>" & vbCr & vbLf & "<year>" & arrRng(Rw, 4) & "</year>" & vbCr & vbLf & "</date>" & vbCr & vbLf & "<time>" & Format(arrRng(Rw, 5), "hh" & ":" & "mm") & "</time>" & vbCr & vbLf: Debug.Print TotalFile
        '  #STEP 3 START
        ' Check   if Entity ID in first row = Entity ID in 2nd row and date in first row = date in 2nd row then      repeat STEP 3 for 2nd row and so on
        Do While Rw + 1 <= Lr And arrRng(Rw, 1) = arrRng(Rw + 1, 1) And arrRng(Rw, 2) = arrRng(Rw + 1, 2)
         Let TotalFile = TotalFile & "<time>" & Format(arrRng(Rw + 1, 5), "hh" & ":" & "mm") & "</time>" & vbCr & vbLf: Debug.Print TotalFile
         Let Rw = Rw + 1 ' This brings us to the line we just filled in
        Loop
     Let TotalFile = TotalFile & "</data>" & vbCr & vbLf: Debug.Print TotalFile
        
        ' Chect  if Entity ID in first row = Entity ID in 2nd row and date in first row not equals to date in 2nd row then    repeat STEP 2 for 2nd row and so on
        Do While Rw + 1 <= Lr And arrRng(Rw, 1) = arrRng(Rw + 1, 1) '   And Not arrRng(Rw, 2) = arrRng(Rw + 1, 2)
         Let TotalFile = TotalFile & "<data>" & vbCr & vbLf & "<date>" & vbCr & vbLf & "<day>" & arrRng(Rw + 1, 2) & "/<day>" & vbCr & vbLf & "<month>" & arrRng(Rw + 1, 3) & "</month>" & vbCr & vbLf & "<year>" & arrRng(Rw + 1, 4) & "</year>" & vbCr & vbLf & "</date>" & vbCr & vbLf & "<time>" & Format(arrRng(Rw + 1, 5), "hh" & ":" & "mm") & "</time>" & vbCr & vbLf: Debug.Print TotalFile
         Let Rw = Rw + 1 ' This brings us to the line we just filled in
        Loop
     Let TotalFile = TotalFile & "</data>" & vbCr & vbLf: Debug.Print TotalFile
    
        '  #STEP 3 END
        '  STEP 2 END
     Let TotalFile = TotalFile & "</forcast>" & vbCr & vbLf: Debug.Print TotalFile
     Let Rw = Rw + 1 '  ' This brings us to the next line
    '    STEP 1 END
    Loop '  While Rw <= Lr
 
 Let TotalFile = Replace(TotalFile, "</data>" & vbCr & vbLf & "</data>" & vbCr & vbLf, "</data>" & vbCr & vbLf, 1, -1, vbBinaryCompare): Debug.Print TotalFile ' I end up with a double  "</data>" & vbCr & vbLf
Rem 3   Make text file
Dim FileNum2 As Long: Let FileNum2 = FreeFile(0)                                  ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName2 As String
 Let PathAndFileName2 = ThisWorkbook.Path & "\" & "XML_Stuff.txt"  ' ' CHANGE TO SUIT  ' Will be made if not there
 Open PathAndFileName2 For Output As #FileNum2
 Print #FileNum2, TotalFile ' write out entire text file
 Close #FileNum2

End Sub

'    <forecast>             ' #STEP 1 Start     Print #intFile, "<Forecast>"
'    <Entity>700</Entity>   ' #STEP 1 Start     Print #intFile, "<Entity>" & Entity ID & "</Entity>"
'    <data>                 ' #STEP 2 Start     Print #intFile, "<Data>"
'    <date>                 ' #STEP 2 Start     Print #intFile, "<date>"
'    <day>19</day>          ' #STEP 2 Start     Print #intFile, "<day>" & day &
'    <month>2</month>       ' #STEP 2 Start              "</day><month>" & month & "</month>
'    <year>2021</year>      ' #STEP 2 Start              <year>" & yeear & "</year>"
'    </date>                ' #STEP 2 Start                  </date>"
'    <time>8:00</time>      ' #STEP 3 START     Print #intFile, "<time>" & time & "</time>"
'          Check  if Entity ID in first row = Entity ID in 2nd row
            ' and date in first row = date in 2nd row then
    '    <time>8:30</time>      ' repeat STEP 3 for 2nd row and so on
    '    </data>           ' #STEP 3 END
    '          Check  if Entity ID in first row = Entity ID in 2nd row
                ' and date in first row IS NOT =   date in 2nd row then'
        '    repeat STEP ??3??  2 for 2nd row and so on
        '    <data>
        '    <date>
        '    <day>20</day>
        '    <month>2</month>
        '    <year>2021</year>
        '    </date>
        '    <time> ??8:00??  9.00   </time>
        '    </data>
    '    </forecast>       ' STEP 2 END           Print #intFile, "</forecast>"


'    If Entity ID is not same as in previous row repeat STEP 1
'
'    <forecast>
'    <Entity>701</Entity>
'    <data>
'    <date>
'    <day>19</day>
'    <month>2</month>
'    <year>2021</year>
'    </date>
'    <time>9:30</time>
'    </data>
'    </forecast>
'    <forecast>

**DocAElstein** · 08-17-2021, 11:50 PM

Test csv file upload ( this post was moved from https://excelfox.com/forum/showthread.php/2759-Test-csv-file-upload?p=15594#post15594
Its new link is https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=15594&viewfull=1#post15594
Originally it was referrenced ( linked via URL ) here https://excelfox.com/forum/showthrea...ll=1#post15596 https://excelfox.com/forum/showthread.php/2758-formatting-number-in-csv-file-import-Number-being-rounded-decimnal-places-truncated?p=15596&viewfull=1#post15596 )

**DocAElstein** · 08-17-2021, 11:50 PM

Test csv file upload ( this post was moved from https://excelfox.com/forum/showthread.php/2759-Test-csv-file-upload?p=15594#post15594
Its new link is https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=15594&viewfull=1#post15594
Originally it was referrenced ( linked via URL ) here https://excelfox.com/forum/showthrea...ll=1#post15596 https://excelfox.com/forum/showthread.php/2758-formatting-number-in-csv-file-import-Number-being-rounded-decimnal-places-truncated?p=15596&viewfull=1#post15596 )

**DocAElstein** · 08-18-2021, 10:36 AM

Post for later use, to get URL now

**DocAElstein** · 08-18-2021, 10:36 AM

Post for later use, to get URL now

**DocAElstein** · 08-18-2021, 10:52 AM

Some notes in support of these Theads and posts in main forum
https://excelfox.com/forum/showthrea...ll=1#post15586 https://excelfox.com/forum/showthread.php/2757-Run-time-error-1004-when-trying-to-resize-a-multi-area-range-object?p=15586&viewfull=1#post15586
https://excelfox.com/forum/showthrea...aces-truncated https://excelfox.com/forum/showthread.php/2758-formatting-number-in-csv-file-import-Number-being-rounded-decimnal-places-truncated

Run-time error 1004 when trying to resize a multi area range object
https://excelfox.com/forum/showthread.php/2757-Run-time-error-1004-when-trying-to-resize-a-multi-area-range-object?p=15597&viewfull=1#post15597 https://excelfox.com/forum/showthread.php/2757-Run-time-error-1004-when-trying-to-resize-a-multi-area-range-object?p=15597&viewfull=1#post15597
https://excelfox.com/forum/showthrea...ll=1#post15586 https://excelfox.com/forum/showthread.php/2757-Run-time-error-1004-when-trying-to-resize-a-multi-area-range-object?p=15586&viewfull=1#post15586
https://excelfox.com/forum/showthrea...aces-truncated https://excelfox.com/forum/showthread.php/2758-formatting-number-in-csv-file-import-Number-being-rounded-decimnal-places-truncated
apvtrn.csv - first 7 of 9 lines

Code:

2021-08-17  10:56:40AM,Navigata Communications Limited,Page 1,A/P Vendor Transactions  (APVTRN01),From Vendor Number,[100045]  To  [100045],From Vendor Group,[VCAD]  To  [VEFT],,,,,Sort Vendors By,[Vendor Name],,,,,,,From Document Date,[2021-04-01]  To  [2021-08-17],Session Date,[2021-08-17],Report Format,[Vendor Transactions by Document Date],Transaction Types,"[Invoice, Credit Note, Prepayment, Payment]",Include Contact/Phone/Credit Limit,[No],Include Space For Comments,[No],Include Zero-Balance Vendors,[Yes],Include Transaction Type Totals,[No],Show Applied Details,[No],Show Fully Paid Transactions,[Yes],Sort Transactions by Transaction Type,[No],Print Amounts In,[Vendor Currency],,,,,,,,,,,,,,,,,,Vendor Number/Name/,,Batch-,Days,Transaction,Document Number/Type,Order Number,PO Number,Doc. Date,Due Date or Check Number,Entry,Over,Amount,Balance,,,,,,,,,100045,BC Hydro (1500 0415 081) consolidated account,,,,,,,,,,,,,,,,,,,,,,,,,,,,PY00000000000000004918,PY,,,2021-04-01,,749-3,,-240.74,0,Vendor Total: ,CAD,-240.74,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,CR: Credit Note                            DB: Debit Note                           IN: Invoice                            IT: Interest Charge                       PI: Prepayment                      MC: Miscellaneous Payment,AD: Adjustment                            CF: Applied Credit (from)           CT: Applied Credit (to)         DF: Applied Debit (from)              DT: Applied Debit (to)   ,ED: Earned Discount Taken         GL: Gain or Loss (multicurrency ledgers)                                PY: Payment                                RD: Rounding,,1 vendor printed,1 vendor name record printed
2021-08-17  10:56:40AM,Navigata Communications Limited,Page 1,A/P Vendor Transactions  (APVTRN01),From Vendor Number,[100045]  To  [100045],From Vendor Group,[VCAD]  To  [VEFT],,,,,Sort Vendors By,[Vendor Name],,,,,,,From Document Date,[2021-04-01]  To  [2021-08-17],Session Date,[2021-08-17],Report Format,[Vendor Transactions by Document Date],Transaction Types,"[Invoice, Credit Note, Prepayment, Payment]",Include Contact/Phone/Credit Limit,[No],Include Space For Comments,[No],Include Zero-Balance Vendors,[Yes],Include Transaction Type Totals,[No],Show Applied Details,[No],Show Fully Paid Transactions,[Yes],Sort Transactions by Transaction Type,[No],Print Amounts In,[Vendor Currency],,,,,,,,,,,,,,,,,,Vendor Number/Name/,,Batch-,Days,Transaction,Document Number/Type,Order Number,PO Number,Doc. Date,Due Date or Check Number,Entry,Over,Amount,Balance,,,,,,,,,100045,BC Hydro (1500 0415 081) consolidated account,,,,,,,,,,,,,,,,,,,,,,,,,,,,4.00003E+11,IN,,JAN 16-MAR 17-2021,2021-04-08,2021-05-08,1100-23,0,"2,371.66",0,Vendor Total: ,CAD,-240.74,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,CR: Credit Note                            DB: Debit Note                           IN: Invoice                            IT: Interest Charge                       PI: Prepayment                      MC: Miscellaneous Payment,AD: Adjustment                            CF: Applied Credit (from)           CT: Applied Credit (to)         DF: Applied Debit (from)              DT: Applied Debit (to)   ,ED: Earned Discount Taken         GL: Gain or Loss (multicurrency ledgers)                                PY: Payment                                RD: Rounding,,1 vendor printed,1 vendor name record printed
2021-08-17  10:56:40AM,Navigata Communications Limited,Page 1,A/P Vendor Transactions  (APVTRN01),From Vendor Number,[100045]  To  [100045],From Vendor Group,[VCAD]  To  [VEFT],,,,,Sort Vendors By,[Vendor Name],,,,,,,From Document Date,[2021-04-01]  To  [2021-08-17],Session Date,[2021-08-17],Report Format,[Vendor Transactions by Document Date],Transaction Types,"[Invoice, Credit Note, Prepayment, Payment]",Include Contact/Phone/Credit Limit,[No],Include Space For Comments,[No],Include Zero-Balance Vendors,[Yes],Include Transaction Type Totals,[No],Show Applied Details,[No],Show Fully Paid Transactions,[Yes],Sort Transactions by Transaction Type,[No],Print Amounts In,[Vendor Currency],,,,,,,,,,,,,,,,,,Vendor Number/Name/,,Batch-,Days,Transaction,Document Number/Type,Order Number,PO Number,Doc. Date,Due Date or Check Number,Entry,Over,Amount,Balance,,,,,,,,,100045,BC Hydro (1500 0415 081) consolidated account,,,,,,,,,,,,,,,,,,,,,,,,,,,,PY00000000000000005064,PY,,,2021-05-06,,769-1,,"-2,371.66",0,Vendor Total: ,CAD,-240.74,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,CR: Credit Note                            DB: Debit Note                           IN: Invoice                            IT: Interest Charge                       PI: Prepayment                      MC: Miscellaneous Payment,AD: Adjustment                            CF: Applied Credit (from)           CT: Applied Credit (to)         DF: Applied Debit (from)              DT: Applied Debit (to)   ,ED: Earned Discount Taken         GL: Gain or Loss (multicurrency ledgers)                                PY: Payment                                RD: Rounding,,1 vendor printed,1 vendor name record printed
2021-08-17  10:56:40AM,Navigata Communications Limited,Page 1,A/P Vendor Transactions  (APVTRN01),From Vendor Number,[100045]  To  [100045],From Vendor Group,[VCAD]  To  [VEFT],,,,,Sort Vendors By,[Vendor Name],,,,,,,From Document Date,[2021-04-01]  To  [2021-08-17],Session Date,[2021-08-17],Report Format,[Vendor Transactions by Document Date],Transaction Types,"[Invoice, Credit Note, Prepayment, Payment]",Include Contact/Phone/Credit Limit,[No],Include Space For Comments,[No],Include Zero-Balance Vendors,[Yes],Include Transaction Type Totals,[No],Show Applied Details,[No],Show Fully Paid Transactions,[Yes],Sort Transactions by Transaction Type,[No],Print Amounts In,[Vendor Currency],,,,,,,,,,,,,,,,,,Vendor Number/Name/,,Batch-,Days,Transaction,Document Number/Type,Order Number,PO Number,Doc. Date,Due Date or Check Number,Entry,Over,Amount,Balance,,,,,,,,,100045,BC Hydro (1500 0415 081) consolidated account,,,,,,,,,,,,,,,,,,,,,,,,,,,,4.00003E+11,IN,,APRIL 01-12-2021,2021-05-10,2021-06-09,1135-7,0,262.8,0,Vendor Total: ,CAD,-240.74,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,CR: Credit Note                            DB: Debit Note                           IN: Invoice                            IT: Interest Charge                       PI: Prepayment                      MC: Miscellaneous Payment,AD: Adjustment                            CF: Applied Credit (from)           CT: Applied Credit (to)         DF: Applied Debit (from)              DT: Applied Debit (to)   ,ED: Earned Discount Taken         GL: Gain or Loss (multicurrency ledgers)                                PY: Payment                                RD: Rounding,,1 vendor printed,1 vendor name record printed
2021-08-17  10:56:40AM,Navigata Communications Limited,Page 1,A/P Vendor Transactions  (APVTRN01),From Vendor Number,[100045]  To  [100045],From Vendor Group,[VCAD]  To  [VEFT],,,,,Sort Vendors By,[Vendor Name],,,,,,,From Document Date,[2021-04-01]  To  [2021-08-17],Session Date,[2021-08-17],Report Format,[Vendor Transactions by Document Date],Transaction Types,"[Invoice, Credit Note, Prepayment, Payment]",Include Contact/Phone/Credit Limit,[No],Include Space For Comments,[No],Include Zero-Balance Vendors,[Yes],Include Transaction Type Totals,[No],Show Applied Details,[No],Show Fully Paid Transactions,[Yes],Sort Transactions by Transaction Type,[No],Print Amounts In,[Vendor Currency],,,,,,,,,,,,,,,,,,Vendor Number/Name/,,Batch-,Days,Transaction,Document Number/Type,Order Number,PO Number,Doc. Date,Due Date or Check Number,Entry,Over,Amount,Balance,,,,,,,,,100045,BC Hydro (1500 0415 081) consolidated account,,,,,,,,,,,,,,,,,,,,,,,,,,,,PY00000000000000005149,PY,,,2021-05-27,,784-7,,-262.8,0,Vendor Total: ,CAD,-240.74,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,CR: Credit Note                            DB: Debit Note                           IN: Invoice                            IT: Interest Charge                       PI: Prepayment                      MC: Miscellaneous Payment,AD: Adjustment                            CF: Applied Credit (from)           CT: Applied Credit (to)         DF: Applied Debit (from)              DT: Applied Debit (to)   ,ED: Earned Discount Taken         GL: Gain or Loss (multicurrency ledgers)                                PY: Payment                                RD: Rounding,,1 vendor printed,1 vendor name record printed
2021-08-17  10:56:40AM,Navigata Communications Limited,Page 1,A/P Vendor Transactions  (APVTRN01),From Vendor Number,[100045]  To  [100045],From Vendor Group,[VCAD]  To  [VEFT],,,,,Sort Vendors By,[Vendor Name],,,,,,,From Document Date,[2021-04-01]  To  [2021-08-17],Session Date,[2021-08-17],Report Format,[Vendor Transactions by Document Date],Transaction Types,"[Invoice, Credit Note, Prepayment, Payment]",Include Contact/Phone/Credit Limit,[No],Include Space For Comments,[No],Include Zero-Balance Vendors,[Yes],Include Transaction Type Totals,[No],Show Applied Details,[No],Show Fully Paid Transactions,[Yes],Sort Transactions by Transaction Type,[No],Print Amounts In,[Vendor Currency],,,,,,,,,,,,,,,,,,Vendor Number/Name/,,Batch-,Days,Transaction,Document Number/Type,Order Number,PO Number,Doc. Date,Due Date or Check Number,Entry,Over,Amount,Balance,,,,,,,,,100045,BC Hydro (1500 0415 081) consolidated account,,,,,,,,,,,,,,,,,,,,,,,,,,,,4.00003E+11,IN,,APR 01 - MAY 18- 2021,2021-06-08,2021-07-08,1168-2,0,"1,864.35",0,Vendor Total: ,CAD,-240.74,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,CR: Credit Note                            DB: Debit Note                           IN: Invoice                            IT: Interest Charge                       PI: Prepayment                      MC: Miscellaneous Payment,AD: Adjustment                            CF: Applied Credit (from)           CT: Applied Credit (to)         DF: Applied Debit (from)              DT: Applied Debit (to)   ,ED: Earned Discount Taken         GL: Gain or Loss (multicurrency ledgers)                                PY: Payment                                RD: Rounding,,1 vendor printed,1 vendor name record printed
2021-08-17  10:56:40AM,Navigata Communications Limited,Page 1,A/P Vendor Transactions  (APVTRN01),From Vendor Number,[100045]  To  [100045],From Vendor Group,[VCAD]  To  [VEFT],,,,,Sort Vendors By,[Vendor Name],,,,,,,From Document Date,[2021-04-01]  To  [2021-08-17],Session Date,[2021-08-17],Report Format,[Vendor Transactions by Document Date],Transaction Types,"[Invoice, Credit Note, Prepayment, Payment]",Include Contact/Phone/Credit Limit,[No],Include Space For Comments,[No],Include Zero-Balance Vendors,[Yes],Include Transaction Type Totals,[No],Show Applied Details,[No],Show Fully Paid Transactions,[Yes],Sort Transactions by Transaction Type,[No],Print Amounts In,[Vendor Currency],,,,,,,,,,,,,,,,,,Vendor Number/Name/,,Batch-,Days,Transaction,Document Number/Type,Order Number,PO Number,Doc. Date,Due Date or Check Number,Entry,Over,Amount,Balance,,,,,,,,,100045,BC Hydro (1500 0415 081) consolidated account,,,,,,,,,,,,,,,,,,,,,,,,,,,,PY00000000000000005260,PY,,,2021-06-24,,801-3,,"-1,864.35",0,Vendor Total: ,CAD,-240.74,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,CR: Credit Note                            DB: Debit Note                           IN: Invoice                            IT: Interest Charge                       PI: Prepayment                      MC: Miscellaneous Payment,AD: Adjustment                            CF: Applied Credit (from)           CT: Applied Credit (to)         DF: Applied Debit (from)              DT: Applied Debit (to)   ,ED: Earned Discount Taken         GL: Gain or Loss (multicurrency ledgers)                                PY: Payment                                RD: Rounding,,1 vendor printed,1 vendor name record printed

continued in next post

**DocAElstein** · 08-18-2021, 10:52 AM

Some notes in support of these Theads and posts in main forum
https://excelfox.com/forum/showthrea...ll=1#post15586 https://excelfox.com/forum/showthread.php/2757-Run-time-error-1004-when-trying-to-resize-a-multi-area-range-object?p=15586&viewfull=1#post15586
https://excelfox.com/forum/showthrea...aces-truncated https://excelfox.com/forum/showthread.php/2758-formatting-number-in-csv-file-import-Number-being-rounded-decimnal-places-truncated

Run-time error 1004 when trying to resize a multi area range object
https://excelfox.com/forum/showthread.php/2757-Run-time-error-1004-when-trying-to-resize-a-multi-area-range-object?p=15597&viewfull=1#post15597 https://excelfox.com/forum/showthread.php/2757-Run-time-error-1004-when-trying-to-resize-a-multi-area-range-object?p=15597&viewfull=1#post15597
https://excelfox.com/forum/showthrea...ll=1#post15586 https://excelfox.com/forum/showthread.php/2757-Run-time-error-1004-when-trying-to-resize-a-multi-area-range-object?p=15586&viewfull=1#post15586
https://excelfox.com/forum/showthrea...aces-truncated https://excelfox.com/forum/showthread.php/2758-formatting-number-in-csv-file-import-Number-being-rounded-decimnal-places-truncated
apvtrn.csv - first 7 of 9 lines

Code:

2021-08-17  10:56:40AM,Navigata Communications Limited,Page 1,A/P Vendor Transactions  (APVTRN01),From Vendor Number,[100045]  To  [100045],From Vendor Group,[VCAD]  To  [VEFT],,,,,Sort Vendors By,[Vendor Name],,,,,,,From Document Date,[2021-04-01]  To  [2021-08-17],Session Date,[2021-08-17],Report Format,[Vendor Transactions by Document Date],Transaction Types,"[Invoice, Credit Note, Prepayment, Payment]",Include Contact/Phone/Credit Limit,[No],Include Space For Comments,[No],Include Zero-Balance Vendors,[Yes],Include Transaction Type Totals,[No],Show Applied Details,[No],Show Fully Paid Transactions,[Yes],Sort Transactions by Transaction Type,[No],Print Amounts In,[Vendor Currency],,,,,,,,,,,,,,,,,,Vendor Number/Name/,,Batch-,Days,Transaction,Document Number/Type,Order Number,PO Number,Doc. Date,Due Date or Check Number,Entry,Over,Amount,Balance,,,,,,,,,100045,BC Hydro (1500 0415 081) consolidated account,,,,,,,,,,,,,,,,,,,,,,,,,,,,PY00000000000000004918,PY,,,2021-04-01,,749-3,,-240.74,0,Vendor Total: ,CAD,-240.74,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,CR: Credit Note                            DB: Debit Note                           IN: Invoice                            IT: Interest Charge                       PI: Prepayment                      MC: Miscellaneous Payment,AD: Adjustment                            CF: Applied Credit (from)           CT: Applied Credit (to)         DF: Applied Debit (from)              DT: Applied Debit (to)   ,ED: Earned Discount Taken         GL: Gain or Loss (multicurrency ledgers)                                PY: Payment                                RD: Rounding,,1 vendor printed,1 vendor name record printed
2021-08-17  10:56:40AM,Navigata Communications Limited,Page 1,A/P Vendor Transactions  (APVTRN01),From Vendor Number,[100045]  To  [100045],From Vendor Group,[VCAD]  To  [VEFT],,,,,Sort Vendors By,[Vendor Name],,,,,,,From Document Date,[2021-04-01]  To  [2021-08-17],Session Date,[2021-08-17],Report Format,[Vendor Transactions by Document Date],Transaction Types,"[Invoice, Credit Note, Prepayment, Payment]",Include Contact/Phone/Credit Limit,[No],Include Space For Comments,[No],Include Zero-Balance Vendors,[Yes],Include Transaction Type Totals,[No],Show Applied Details,[No],Show Fully Paid Transactions,[Yes],Sort Transactions by Transaction Type,[No],Print Amounts In,[Vendor Currency],,,,,,,,,,,,,,,,,,Vendor Number/Name/,,Batch-,Days,Transaction,Document Number/Type,Order Number,PO Number,Doc. Date,Due Date or Check Number,Entry,Over,Amount,Balance,,,,,,,,,100045,BC Hydro (1500 0415 081) consolidated account,,,,,,,,,,,,,,,,,,,,,,,,,,,,4.00003E+11,IN,,JAN 16-MAR 17-2021,2021-04-08,2021-05-08,1100-23,0,"2,371.66",0,Vendor Total: ,CAD,-240.74,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,CR: Credit Note                            DB: Debit Note                           IN: Invoice                            IT: Interest Charge                       PI: Prepayment                      MC: Miscellaneous Payment,AD: Adjustment                            CF: Applied Credit (from)           CT: Applied Credit (to)         DF: Applied Debit (from)              DT: Applied Debit (to)   ,ED: Earned Discount Taken         GL: Gain or Loss (multicurrency ledgers)                                PY: Payment                                RD: Rounding,,1 vendor printed,1 vendor name record printed
2021-08-17  10:56:40AM,Navigata Communications Limited,Page 1,A/P Vendor Transactions  (APVTRN01),From Vendor Number,[100045]  To  [100045],From Vendor Group,[VCAD]  To  [VEFT],,,,,Sort Vendors By,[Vendor Name],,,,,,,From Document Date,[2021-04-01]  To  [2021-08-17],Session Date,[2021-08-17],Report Format,[Vendor Transactions by Document Date],Transaction Types,"[Invoice, Credit Note, Prepayment, Payment]",Include Contact/Phone/Credit Limit,[No],Include Space For Comments,[No],Include Zero-Balance Vendors,[Yes],Include Transaction Type Totals,[No],Show Applied Details,[No],Show Fully Paid Transactions,[Yes],Sort Transactions by Transaction Type,[No],Print Amounts In,[Vendor Currency],,,,,,,,,,,,,,,,,,Vendor Number/Name/,,Batch-,Days,Transaction,Document Number/Type,Order Number,PO Number,Doc. Date,Due Date or Check Number,Entry,Over,Amount,Balance,,,,,,,,,100045,BC Hydro (1500 0415 081) consolidated account,,,,,,,,,,,,,,,,,,,,,,,,,,,,PY00000000000000005064,PY,,,2021-05-06,,769-1,,"-2,371.66",0,Vendor Total: ,CAD,-240.74,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,CR: Credit Note                            DB: Debit Note                           IN: Invoice                            IT: Interest Charge                       PI: Prepayment                      MC: Miscellaneous Payment,AD: Adjustment                            CF: Applied Credit (from)           CT: Applied Credit (to)         DF: Applied Debit (from)              DT: Applied Debit (to)   ,ED: Earned Discount Taken         GL: Gain or Loss (multicurrency ledgers)                                PY: Payment                                RD: Rounding,,1 vendor printed,1 vendor name record printed
2021-08-17  10:56:40AM,Navigata Communications Limited,Page 1,A/P Vendor Transactions  (APVTRN01),From Vendor Number,[100045]  To  [100045],From Vendor Group,[VCAD]  To  [VEFT],,,,,Sort Vendors By,[Vendor Name],,,,,,,From Document Date,[2021-04-01]  To  [2021-08-17],Session Date,[2021-08-17],Report Format,[Vendor Transactions by Document Date],Transaction Types,"[Invoice, Credit Note, Prepayment, Payment]",Include Contact/Phone/Credit Limit,[No],Include Space For Comments,[No],Include Zero-Balance Vendors,[Yes],Include Transaction Type Totals,[No],Show Applied Details,[No],Show Fully Paid Transactions,[Yes],Sort Transactions by Transaction Type,[No],Print Amounts In,[Vendor Currency],,,,,,,,,,,,,,,,,,Vendor Number/Name/,,Batch-,Days,Transaction,Document Number/Type,Order Number,PO Number,Doc. Date,Due Date or Check Number,Entry,Over,Amount,Balance,,,,,,,,,100045,BC Hydro (1500 0415 081) consolidated account,,,,,,,,,,,,,,,,,,,,,,,,,,,,4.00003E+11,IN,,APRIL 01-12-2021,2021-05-10,2021-06-09,1135-7,0,262.8,0,Vendor Total: ,CAD,-240.74,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,CR: Credit Note                            DB: Debit Note                           IN: Invoice                            IT: Interest Charge                       PI: Prepayment                      MC: Miscellaneous Payment,AD: Adjustment                            CF: Applied Credit (from)           CT: Applied Credit (to)         DF: Applied Debit (from)              DT: Applied Debit (to)   ,ED: Earned Discount Taken         GL: Gain or Loss (multicurrency ledgers)                                PY: Payment                                RD: Rounding,,1 vendor printed,1 vendor name record printed
2021-08-17  10:56:40AM,Navigata Communications Limited,Page 1,A/P Vendor Transactions  (APVTRN01),From Vendor Number,[100045]  To  [100045],From Vendor Group,[VCAD]  To  [VEFT],,,,,Sort Vendors By,[Vendor Name],,,,,,,From Document Date,[2021-04-01]  To  [2021-08-17],Session Date,[2021-08-17],Report Format,[Vendor Transactions by Document Date],Transaction Types,"[Invoice, Credit Note, Prepayment, Payment]",Include Contact/Phone/Credit Limit,[No],Include Space For Comments,[No],Include Zero-Balance Vendors,[Yes],Include Transaction Type Totals,[No],Show Applied Details,[No],Show Fully Paid Transactions,[Yes],Sort Transactions by Transaction Type,[No],Print Amounts In,[Vendor Currency],,,,,,,,,,,,,,,,,,Vendor Number/Name/,,Batch-,Days,Transaction,Document Number/Type,Order Number,PO Number,Doc. Date,Due Date or Check Number,Entry,Over,Amount,Balance,,,,,,,,,100045,BC Hydro (1500 0415 081) consolidated account,,,,,,,,,,,,,,,,,,,,,,,,,,,,PY00000000000000005149,PY,,,2021-05-27,,784-7,,-262.8,0,Vendor Total: ,CAD,-240.74,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,CR: Credit Note                            DB: Debit Note                           IN: Invoice                            IT: Interest Charge                       PI: Prepayment                      MC: Miscellaneous Payment,AD: Adjustment                            CF: Applied Credit (from)           CT: Applied Credit (to)         DF: Applied Debit (from)              DT: Applied Debit (to)   ,ED: Earned Discount Taken         GL: Gain or Loss (multicurrency ledgers)                                PY: Payment                                RD: Rounding,,1 vendor printed,1 vendor name record printed
2021-08-17  10:56:40AM,Navigata Communications Limited,Page 1,A/P Vendor Transactions  (APVTRN01),From Vendor Number,[100045]  To  [100045],From Vendor Group,[VCAD]  To  [VEFT],,,,,Sort Vendors By,[Vendor Name],,,,,,,From Document Date,[2021-04-01]  To  [2021-08-17],Session Date,[2021-08-17],Report Format,[Vendor Transactions by Document Date],Transaction Types,"[Invoice, Credit Note, Prepayment, Payment]",Include Contact/Phone/Credit Limit,[No],Include Space For Comments,[No],Include Zero-Balance Vendors,[Yes],Include Transaction Type Totals,[No],Show Applied Details,[No],Show Fully Paid Transactions,[Yes],Sort Transactions by Transaction Type,[No],Print Amounts In,[Vendor Currency],,,,,,,,,,,,,,,,,,Vendor Number/Name/,,Batch-,Days,Transaction,Document Number/Type,Order Number,PO Number,Doc. Date,Due Date or Check Number,Entry,Over,Amount,Balance,,,,,,,,,100045,BC Hydro (1500 0415 081) consolidated account,,,,,,,,,,,,,,,,,,,,,,,,,,,,4.00003E+11,IN,,APR 01 - MAY 18- 2021,2021-06-08,2021-07-08,1168-2,0,"1,864.35",0,Vendor Total: ,CAD,-240.74,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,CR: Credit Note                            DB: Debit Note                           IN: Invoice                            IT: Interest Charge                       PI: Prepayment                      MC: Miscellaneous Payment,AD: Adjustment                            CF: Applied Credit (from)           CT: Applied Credit (to)         DF: Applied Debit (from)              DT: Applied Debit (to)   ,ED: Earned Discount Taken         GL: Gain or Loss (multicurrency ledgers)                                PY: Payment                                RD: Rounding,,1 vendor printed,1 vendor name record printed
2021-08-17  10:56:40AM,Navigata Communications Limited,Page 1,A/P Vendor Transactions  (APVTRN01),From Vendor Number,[100045]  To  [100045],From Vendor Group,[VCAD]  To  [VEFT],,,,,Sort Vendors By,[Vendor Name],,,,,,,From Document Date,[2021-04-01]  To  [2021-08-17],Session Date,[2021-08-17],Report Format,[Vendor Transactions by Document Date],Transaction Types,"[Invoice, Credit Note, Prepayment, Payment]",Include Contact/Phone/Credit Limit,[No],Include Space For Comments,[No],Include Zero-Balance Vendors,[Yes],Include Transaction Type Totals,[No],Show Applied Details,[No],Show Fully Paid Transactions,[Yes],Sort Transactions by Transaction Type,[No],Print Amounts In,[Vendor Currency],,,,,,,,,,,,,,,,,,Vendor Number/Name/,,Batch-,Days,Transaction,Document Number/Type,Order Number,PO Number,Doc. Date,Due Date or Check Number,Entry,Over,Amount,Balance,,,,,,,,,100045,BC Hydro (1500 0415 081) consolidated account,,,,,,,,,,,,,,,,,,,,,,,,,,,,PY00000000000000005260,PY,,,2021-06-24,,801-3,,"-1,864.35",0,Vendor Total: ,CAD,-240.74,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,CR: Credit Note                            DB: Debit Note                           IN: Invoice                            IT: Interest Charge                       PI: Prepayment                      MC: Miscellaneous Payment,AD: Adjustment                            CF: Applied Credit (from)           CT: Applied Credit (to)         DF: Applied Debit (from)              DT: Applied Debit (to)   ,ED: Earned Discount Taken         GL: Gain or Loss (multicurrency ledgers)                                PY: Payment                                RD: Rounding,,1 vendor printed,1 vendor name record printed

continued in next post

**DocAElstein** · 08-18-2021, 11:24 AM

( this is post 35 15601 , it was used to copy to get post 36 below )

continued from last post

https://excelfox.com/forum/showthrea...ll=1#post15597 https://excelfox.com/forum/showthread.php/2757-Run-time-error-1004-when-trying-to-resize-a-multi-area-range-object?p=15597&viewfull=1#post15597
apvtrn.csv - last 2 of 9 lines

Code:

2021-08-17  10:56:40AM,Navigata Communications Limited,Page 1,A/P Vendor Transactions  (APVTRN01),From Vendor Number,[100045]  To  [100045],From Vendor Group,[VCAD]  To  [VEFT],,,,,Sort Vendors By,[Vendor Name],,,,,,,From Document Date,[2021-04-01]  To  [2021-08-17],Session Date,[2021-08-17],Report Format,[Vendor Transactions by Document Date],Transaction Types,"[Invoice, Credit Note, Prepayment, Payment]",Include Contact/Phone/Credit Limit,[No],Include Space For Comments,[No],Include Zero-Balance Vendors,[Yes],Include Transaction Type Totals,[No],Show Applied Details,[No],Show Fully Paid Transactions,[Yes],Sort Transactions by Transaction Type,[No],Print Amounts In,[Vendor Currency],,,,,,,,,,,,,,,,,,Vendor Number/Name/,,Batch-,Days,Transaction,Document Number/Type,Order Number,PO Number,Doc. Date,Due Date or Check Number,Entry,Over,Amount,Balance,,,,,,,,,100045,BC Hydro (1500 0415 081) consolidated account,,,,,,,,,,,,,,,,,,,,,,,,,,,,4.00003E+11,IN,,APR 13-JUN 10-2021,2021-07-08,2021-08-07,1187-25,0,196.23,0,Vendor Total: ,CAD,-240.74,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,CR: Credit Note                            DB: Debit Note                           IN: Invoice                            IT: Interest Charge                       PI: Prepayment                      MC: Miscellaneous Payment,AD: Adjustment                            CF: Applied Credit (from)           CT: Applied Credit (to)         DF: Applied Debit (from)              DT: Applied Debit (to)   ,ED: Earned Discount Taken         GL: Gain or Loss (multicurrency ledgers)                                PY: Payment                                RD: Rounding,,1 vendor printed,1 vendor name record printed
2021-08-17  10:56:40AM,Navigata Communications Limited,Page 1,A/P Vendor Transactions  (APVTRN01),From Vendor Number,[100045]  To  [100045],From Vendor Group,[VCAD]  To  [VEFT],,,,,Sort Vendors By,[Vendor Name],,,,,,,From Document Date,[2021-04-01]  To  [2021-08-17],Session Date,[2021-08-17],Report Format,[Vendor Transactions by Document Date],Transaction Types,"[Invoice, Credit Note, Prepayment, Payment]",Include Contact/Phone/Credit Limit,[No],Include Space For Comments,[No],Include Zero-Balance Vendors,[Yes],Include Transaction Type Totals,[No],Show Applied Details,[No],Show Fully Paid Transactions,[Yes],Sort Transactions by Transaction Type,[No],Print Amounts In,[Vendor Currency],,,,,,,,,,,,,,,,,,Vendor Number/Name/,,Batch-,Days,Transaction,Document Number/Type,Order Number,PO Number,Doc. Date,Due Date or Check Number,Entry,Over,Amount,Balance,,,,,,,,,100045,BC Hydro (1500 0415 081) consolidated account,,,,,,,,,,,,,,,,,,,,,,,,,,,,PY00000000000000005453,PY,,,2021-08-05,,834-1,,-196.23,0,Vendor Total: ,CAD,-240.74,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,CR: Credit Note                            DB: Debit Note                           IN: Invoice                            IT: Interest Charge                       PI: Prepayment                      MC: Miscellaneous Payment,AD: Adjustment                            CF: Applied Credit (from)           CT: Applied Credit (to)         DF: Applied Debit (from)              DT: Applied Debit (to)   ,ED: Earned Discount Taken         GL: Gain or Loss (multicurrency ledgers)                                PY: Payment                                RD: Rounding,,1 vendor printed,1 vendor name record printed

continued in next post

**DocAElstein** · 08-18-2021, 11:24 AM

Post #36 15604 ( copied originally from post 35, then edited as a way of getting a post here )

continued from last post

This is how the text file from the last 2 post opens in my Excel
_____ Workbook: apvtrn.csv ( Using Excel 2007 32 bit )

_Row\^Col	DI	DJ	DL	DM	DN	DO	DQ
1	PY00000000000000004918	PY		04-01-2021		749-3	-240.74
2	4.00E+11	IN	JAN 16-MAR 17-2021	04-08-2021	05-08-2021	1100-23	2,371.66
3	PY00000000000000005064	PY		05-06-2021		769-1	-2,371.66
4	4.00E+11	IN	APRIL 01-12-2021	05-10-2021	06-09-2021	1135-7	262.8
5	PY00000000000000005149	PY		05-27-2021		784-7	-262.8
6	4.00E+11	IN	APR 01 - MAY 18- 2021	06-08-2021	07-08-2021	1168-2	1,864.35
7	PY00000000000000005260	PY		06-24-2021		801-3	-1,864.35
8	4.00E+11	IN	APR 13-JUN 10-2021	07-08-2021	08-07-2021	1187-25	196.23
9	PY00000000000000005453	PY		08-05-2021		834-1	-196.23

Worksheet: apvtrn

_.. The OP appears unable to explain what he wants, and he probably doesn't know or can hardly speak a word of English.. never mind ... it isn't the fucking first time, and i doubt it will be the last, onward! based on seeing this a lot form the OP , Set rngDst = wkbDst.Worksheets("STATEMENT").Range("A17:C17, E17") , as well as a screenshots he gave,

https://i.imgur.com/VtfbGD2.jpg

https://i.imgur.com/W0qJZCN.png ImageWhatWantedButNoData.JPG statement.PNG
I am taking a guess that somehow, some of the data above needs to find its way somewhere in the general area shown below
_____ Workbook: VENDOR STATEMENT.xlsm ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E
15	Invoice Date	Invoice #	Invoice Due Date	A/C	Invoice $ +
16		*Balance brought forward*
17
18
19

Worksheet: STATEMENT

-.. and from seeing this... For Each Col In Array("DM", "DI", "DN", "DQ") , I will take a guess we can make these relations
DM – A ( date )
DI – B ( big number )
DN – C ( date )

DQ – E ( a number that might be something to do with money )