Notes tests, string, manipulation of text files and string manipulations

Clipboard Spam Link Wonks

In Excel columns I have the URLs, and I copy them
Then I look at what is in the clipboard with the aim of adding enough BB Code URL tags

This is what two links look like

Code:

"https" & ":" & "/" & "/" & "www" & "." & "youtube" & "." & "com" & "/" & "watch" & "?" & "v" & "=" & "pGcerfVqYyU" & "&" & "lc" & "=" & "UgwYnetS3HRljX9vOLx4AaABAg" & "." & "94UVSldHOOy9VDJUb" & "_" & "mRBH" & vbCr & vbLf & "https" & ":" & "/" & "/" & "www" & "." & "youtube" & "." & "com" & "/" & "watch" & "?" & "v" & "=" & "pGcerfVqYyU" & "&" & "lc" & "=" & "UgzCtsi4abaxZnZJjZR4AaABAg" & vbCr & vbLf

Fortunately, no surprises, so we just need to add the BB Code URL tags in some convenient way. First thought is to replace the vbCr & vbLf with [/url] & vbCr & vbLf and replace the http with [url] & http
The we put the modified string back in the clipboard

Code:

Option Explicit 'https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA/page5 'https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=16480&viewfull=1#post16480 'https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA/page5#post16480 Sub CopyExcelColumnSpamURL() Selection.Copy ' This will put it in the clipboard 'Dim objCliCodeCopied As DataObject '**Early Binding. This is for an Object from the class MS Forms. This will be a Data Object of what we "send" to the Clipboard. So I name it CLIpboardSend. But it is a DataObject. It has the Methods I need to send text to the Clipboard ' Set objCliCodeCopied = New DataObject '**Must enable Forms Library: In VB Editor do this: Tools -- References - scroll down to Microsoft Forms 2.0 Object Library -- put checkmark in. Note if you cannot find it try OR IF NOT THERE..you can add that manually: VBA Editor -- Tools -- References -- Browse -- and find FM20.DLL file under C:\WINDOWS\system32 and select it --> Open --> OK. https://web.archive.org/web/20140610055224/http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/ ' ( or instead of those two lines Dim obj As New DataObject ). or next two lines are.....Late Binding equivalent' Dim objCli As Object ' Late Binding equivalent' If you declare a variable as Object, you are late binding it. https://web.archive.org/web/20141119223828/http://excelmatters.com/2013/09/23/vba-references-and-early-binding-vs-late-binding/ Set objCli = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") ' https://web.archive.org/web/20140610055224/http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/ objCli.GetFromClipboard 'All that is in the Clipboard goes in this Data Object initial instance of the Class Dim SpamURLs As String ' String varable to take the moodified code. This can be very long, like my cock Let SpamURLs = objCli.GetText() 'retrieve the text in the initial instance of the Class. ( In this case the original code modifies to have code lines ) ' Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(SpamURLs) Let SpamURLs = Replace(SpamURLs, vbCr & vbLf, "[/uRl]" & vbCr & vbLf, 1, -1, vbBinaryCompare) Let SpamURLs = Replace(SpamURLs, "http", "[uRl]http", 1, -1, vbBinaryCompare) ' Make a data Object to put back in clipboard. This a Another Object from class just to demo, could have used the first 'Dim objDatCliBackIn As DataObject ' Set objDatCliBackIn = New DataObject 'Set to a new Instance ( Blue Print ) of dataobject Dim objDatCliBackIn As Object Set objDatCliBackIn = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") objDatCliBackIn.SetText "Alan Spam Tests" & vbCr & vbLf & SpamURLs 'Make Data object's text equal to the long string of the modified code objDatCliBackIn.PutInClipboard 'Place current Data object into the Clipboard, effectivelly putting its text, which is oue final code in the Clipcoard End Sub

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg. 9gJzxwFcnPU9gORqKw5tW_
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugyb8nmKKoXvcdM58gV4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwvvXcl1oa79xS7BAV4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgxvIFArksPprylHXYZ4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxUbeYSvsBH2Gianox4AaABAg. 9VYH-07VTyW9gJV5fDAZNe
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg. 9fsvd9zwZii9gMUka-NbIZ
https://www.youtube.com/watch?v=jdPeMPT98QU
https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

2 Attachment(s)

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/VtfbGD2.jpg https://i.imgur.com/W0qJZCN.png https://i.imgur.com/W0qJZCN.png Attachment 3603 Attachment 3604
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 )

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg. 9hI1CQJMLLo9hWn2pGBeSS
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzkRujoMw9PblmXDQ14AaABAg. 9hJRnEjxQrd9hJoCjomNI2
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzPZbG7OvUkh35nXDd4AaABAg. 9hJOZEEZa6p9hJqLC7El-w
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwUcEpm8u6ZW3uOHXx4AaABAg. 9hIlxxGY7t49hJsB2PWxC4
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyvDj6NWT1Gxyy2JyR4AaABAg. 9hIKlNPeqDn9hJskm92np6
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugwy7qx_kG9iUmMVO_F4AaABAg. 9hI2IGUdmTW9hJuyaQawqx
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg. 9hI1CQJMLLo9hJwTB9Jlob
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyyQWYVP1OnCqavb-x4AaABAg
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwJKKmExZ1FdZVDJf54AaABAg
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugz_p0kVGrLntPtYzCt4AaABAg
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9iHOYYpaAbC
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgxuL6YCUckeUIh9hoh4AaABAg
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg. 9h4sd6Vs4qE9h7G-bVm8_-
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

_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

( 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

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxUbeYSvsBH2Gianox4AaABAg. 9VYH-07VTyW9gJV5fDAZNe
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg. 9fsvd9zwZii9gMUka-NbIZ
https://www.youtube.com/watch?v=jdPeMPT98QU
https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

2 Attachment(s)

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/VtfbGD2.jpg https://i.imgur.com/W0qJZCN.png https://i.imgur.com/W0qJZCN.png Attachment 3603 Attachment 3604
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 )

_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

( Post #37 15602 )

formatting-number-in-csv-file-import-Number-being-rounded-decimnal-places-truncated
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 [/size]

continued from last post

apvtrn.csv - first 6 lines of 9 lines in Text File supplied here https://excelfox.com/forum/showthrea...ll=1#post15598

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

continued in next post

( Post #38 15603 )

contunued from last post

last 5 of nine lines in apvtrn.csv as supplied for formatting number in csv file import. Number being rounded / decimal places truncated
https://excelfox.com/forum/showthrea...aces-truncated
https://excelfox.com/forum/showthrea...ll=1#post15598
https://excelfox.com/forum/showthread.php/2758-formatting-number-in-csv-file-import-Number-being-rounded-decimnal-places-truncated?p=15598&viewfull=1#post15598

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,,,,,,,,,,,,,,,,,,,,,,,,,,,,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 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

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