Page 4 of 12 FirstFirst ... 23456 ... LastLast
Results 31 to 40 of 115

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

  1. 02-17-2021, 06:56 PM #31
    DocAElstein
    DocAElstein is offline
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,452
    Rep Power
    10

    Convert Excel data range to XML type text file

    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>
    Attached Files Attached Files
    Last edited by DocAElstein; 02-17-2021 at 08:39 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
     Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!
    Reply With Quote Reply With Quote
  2. 02-17-2021, 06:56 PM #32
    DocAElstein
    DocAElstein is offline
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,452
    Rep Power
    10

    Convert Excel data range to XML type text file

    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>
    Attached Files Attached Files
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
     Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!
    Reply With Quote Reply With Quote
  3. 08-17-2021, 11:50 PM #33
    DocAElstein
    DocAElstein is offline
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,452
    Rep Power
    10

    Test csv file upload

    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     )
    Attached Files Attached Files
    Last edited by DocAElstein; 08-18-2021 at 10:48 AM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
     Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!
    Reply With Quote Reply With Quote
  4. 08-17-2021, 11:50 PM #34
    DocAElstein
    DocAElstein is offline
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,452
    Rep Power
    10

    Test csv file upload

    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     )
    Attached Files Attached Files
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
     Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!
    Reply With Quote Reply With Quote
  5. 08-18-2021, 10:36 AM #35
    DocAElstein
    DocAElstein is offline
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,452
    Rep Power
    10
    Post for later use, to get URL now
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
     Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!
    Reply With Quote Reply With Quote
  6. 08-18-2021, 10:36 AM #36
    DocAElstein
    DocAElstein is offline
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,452
    Rep Power
    10
    Post for later use, to get URL now
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
     Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!
    Reply With Quote Reply With Quote
  7. 08-18-2021, 10:52 AM #37
    DocAElstein
    DocAElstein is offline
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,452
    Rep Power
    10
    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
    Last edited by DocAElstein; 08-18-2021 at 11:24 AM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
     Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!
    Reply With Quote Reply With Quote
  8. 08-18-2021, 10:52 AM #38
    DocAElstein
    DocAElstein is offline
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,452
    Rep Power
    10
    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
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
     Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!
    Reply With Quote Reply With Quote
  9. 08-18-2021, 11:24 AM #39
    DocAElstein
    DocAElstein is offline
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,452
    Rep Power
    10
    ( 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
    Last edited by DocAElstein; 08-18-2021 at 02:54 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
     Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!
    Reply With Quote Reply With Quote
  10. 08-18-2021, 11:24 AM #40
    DocAElstein
    DocAElstein is offline
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,452
    Rep Power
    10
    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 DK DL DM DN DO DP 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 )

    Last edited by DocAElstein; 08-18-2021 at 04:57 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
     Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Similar Threads

  1. Notes tests, string, manipulation of text files and string manipulations
    By DocAElstein in forum Test Area
    Replies: 109
    Last Post: 03-29-2024, 07:01 PM
  2. Vba - Check String/Text From Multiple Workbook Without Opening All Files
    By muhammad susanto in forum Excel Help
    Replies: 4
    Last Post: 01-30-2022, 04:05 PM
  3. Delete rows based on match criteria in two excel files, 1 might be .csv file .Opened in Excel=Fail Chaos
    By fixer in forum Test Area
    Replies: 29
    Last Post: 06-09-2020, 06:00 PM
  4. Notes tests. Excel VBA Folder File Search
    By DocAElstein in forum Test Area
    Replies: 39
    Last Post: 03-20-2018, 04:09 PM
  5. Collate Data from csv files to excel sheet
    By dhiraj.ch185 in forum Excel Help
    Replies: 16
    Last Post: 03-06-2012, 07:37 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules