Can we use Collection source as Excel

Hi,
I'm trying to display the report and the base of the report will be Excel data,
here is the sample code, Please help me

;********************** Collection *********************

[Collection: MissingEntriesCommonCollSRC]
ODBC : @@DriverInfo + "D:\Tallyversion\_MissingEntries__.xls"
SQL : "Select * From [Sheet1$]"
Fetch : *.*
Client Only : Yes

[Collection: MissingEntriesCommonColl]
Source Collection: MissingEntriesCommonCollSRC
Compute : GSTHeroTpyeNew:$_1
Compute : EIPartyGSTIN : $_2
Compute : Date : $_3
Compute : VoucherNumber : $_4
Compute : GSTHeroIgstNew: $_7
Compute : GSTHeroCgstNew: $_5
Compute : GSTHeroSgstNew: $_6
Compute : AssblVal : $_8
Compute : InvTotValue1 : $_9


[System: Formula]

Driver Info : "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ="

 

Code:

[Collection: MissingEntriesCommonCollSRC]
    ODBC : @@DriverInfo + "D:\Tallyversion\_MissingEntries__.xls"
    SQL : "Select * From [Sheet1$]"

    SQLObject    : MissingEntriesObject



[Object:MissingEntriesObject]
  
    GSTHeroTpyeNew    :$_1
    EIPartyGSTIN : $_2
    Date         : $_3
    VoucherNumber : $_4
    GSTHeroIgstNew: $_7
    GSTHeroCgstNew: $_5
    GSTHeroSgstNew: $_6
    AssblVal : $_8
    InvTotValue1 : $_9

[Collection: MissingEntiresCommonColl]
    Source Collection: MissingEntriesCommonCollSRC
    Compute : GSTHeroTpyeNew    : $GSTHeroTpyeNew
    Compute : EIPartyGSTIN         : $EIPartyGSTIN   
    Compute : Date                : $Date          
    Compute : VoucherNumber     : $VoucherNumber
    Compute : GSTHeroIgstNew    : $GSTHeroIgstNew
    Compute : GSTHeroCgstNew    : $GSTHeroCgstNew
    Compute : GSTHeroSgstNew    : $GSTHeroSgstNew
    Compute : AssblVal             : $AssblVal       
    Compute : InvTotValue1         : $InvTotValue1   
    Fetch : *.*
    Client Only : Yes

[System: Formula]

    Driver Info : "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ="

 

Thank you for your kind response

 

[Collection: Led Coll From Access]

ODBC : "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=D:\Masters.xls;DriverId=790"
SQL : "Select * From [Sheet1$]"
SQLObject : Led Object
[Object:Led Object]
LedName : $_1
LedParent : $_2
LedOpBal : $_3
[Collection: Led Coll]
Source Collection : Led coll From Access
Compute : LedName : $LedName
Compute : LedParent : $LedParent
Compute : LedOpBal : $LedOpBal
Fetch: *.*
Client Only: Yes

[#Menu: Gateway of Tally]
Add : Button : Led Import


[Button: Led Import]
Key : Alt + F5
Action : Call : Ledger Import
Title : "Voucher Import From MS Access"

[Function: Ledger Import]
01 : Walk Collection: Led Coll
11: IF : 1=1
112 : NEW OBJECT: Voucher
123 : Set Value: Date : "31-Mar-2018"
154 : Set Value:VoucherTypeName :"Purchase"
165 : Set Value: Narration :"Working"
186 : Set Value: PARTYNAME : "AA"
197 : Set Value: PARTYLEDGERNAME : "AA"
258 : Set Value: amount:"100"
269 : Insert Collection Object: LEDGERENTRIES
290 : Set Value: LedgerName : "AA"
311 : Set Value: Amount:"99"
342 : Insert Collection Object: LEDGERENTRIES
383 : Set Value: LedgerName : "Pur"
424 : Set Value: Amount:"-99"
465 : Save Target
516 : End If
518 : END WALK

 

This Code is my Almost Final Code, Which works fine with older tally version ERP 9 1.1


[Collection: Led Coll From Access50]

ODBC : "Driver={Microsoft Excel Driver (*.xls)};Dbq=D:\Sale.xls;DriverId=790"
SQL : "Select * From [Sheet2$]"

SQL Object : AccessObj50

[Object: AccessObj50]

LedName : $_1

LedParent : $_2

LedOpBal : $$AsAmount:$_3

[Collection: Led Coll50]
Source Collection : Led coll From Access50
Compute : LEDDATE : $_1
Compute : LEDNAME : $_2
Compute : LEDAMOUNT :$_3
Compute : LEDQTY :$$String:$_7
Compute : IGST :$_4
Compute : CGST :$_5
Compute : SGST :$_6
Compute : LEDQTY2:$$String:$_8

[#Menu: Gateway of Tally]
Add : Button : Led Import50




[Variable : qtyvar]
Type : String
Set Always : yes

[Variable : qtyvar2]
Type : String

[Variable : qtyvar3]
Type : String

[system : Variable]
qtyvar :""
[system : Variable]
qtyvar2 :""

[system : Variable]
qtyvar3 :""






[Button: Led Import50]
Key : Alt + F6
Action : Call : Ledger Import50
Title : "Import SaLE with Stock"

[Function: Ledger Import50]








01 : WALK COLLECTION: Led Coll50
03 : If : 1=1
04 : NEW OBJECT: Voucher
05 : Set Value: Date : $$Date($$StringPart:$LEDDATE:8:2) + "-" + +
($$StringPart:$LEDDATE:5:2)+ "-" + +
($$StringPart:$LEDDATE:0:4))
07 : Set Value:VoucherTypeName :"Sales"
08 : Set Value: Narration :"Working"
09 : Set Value: PARTYNAME : $LEDNAME
12 : Set Value:REFERENCE:"333"
14 : Set Value: PARTYLEDGERNAME : $LEDNAME
15 : Set Value: FBTPAYMENTTYPE :"Default"
16 : Set Value: PERSISTEDVIEW : "Invoice Voucher View"

116 : Insert Collection Object: LEDGERENTRIES
117 : Set Target : LEDGERENTRIES
119 : Set Value: LedgerName : $LEDNAME
120 : SET VALUE : IsDeemedPositive : Yes
121 : Set Value:ispartyledger:Yes
125 : Set Value: Amount:$$Number:$LEDAMOUNT+$$Number:$IGST+$$Number:$SGST+$$Number:$CGST
127 : Set Target:..

128 : Insert Collection Object: LEDGERENTRIES
133 : Set Value: LedgerName : "Sales Account"
134 : Set Value:IsDeemedPositive:No
135 : Set Value:ispartyledger:No

137 : Set Value: Amount:$$String:$LEDAMOUNT

140 : If :$$IsEmpty:$LEDQTY2 or $$number:$LEDQTY2 = 0


400 : Insert Collection Object:INVENTORYALLOCATIONS
450 : Set Value :StockItemName:"Sheet"
460 : Set Value:IsDeemedPositive:No
470 : SET : qtyvar : $$String:$LEDAMOUNT
480 : SET : qtyvar2 : "-"+$$String:$LEDQTY
490 : Start Block
520 : Set Object
540 : Set Value :BilledQTY :$$AsQty:##qtyvar2
550 : Set Value :ActualQTY :$$AsQty:##qtyvar2
560 : Set Value: AMOUNT:##qtyvar
580 : End Block
600 : Set Target:..
601 : Else


602 : Insert Collection Object:INVENTORYALLOCATIONS
603 : Set Value :StockItemName:"Scrap"
604 : Set Value:IsDeemedPositive:No
605 : SET : qtyvar : $$String:$LEDAMOUNT
606 : SET : qtyvar3 : "-"+$$String:$LEDQTY2
607 : Start Block
608 : Set Object
609 : Set Value :BilledQTY :$$AsQty:##qtyvar3
610 : Set Value :ActualQTY :$$AsQty:##qtyvar3
611 : Set Value: AMOUNT:##qtyvar
612 : End Block
613 : Set Target:..
614 : End If

615 : If : $$IsEmpty:$CGST or $$Number:$CGST = 0 and $$IsEmpty:$SGST or $$Number:$SGST =0

618 : Insert Collection Object:LEDGERENTRIES
620 : Set Value:LedgerName:"IGST"
625 : Set Value:IsDeemedPositive:No
630 : Set Value:IsPartyLedger:No
640 : Set Value:AMount:$IGST
645 : Save Target

650: Else

655 : Insert Collection Object:LEDGERENTRIES
660 : Set Value:LedgerName:"SGST"
670 : Set Value:IsDeemedPositive:No
680 : Set Value:IsPartyLedger:No
690 : Set Value:AMount:$SGST

700 : Insert Collection Object:LEDGERENTRIES
710 : Set Value:LedgerName:"CGST"
720 : Set Value:IsDeemedPositive:No
730 : Set Value:IsPartyLedger:No
740 : Set Value:AMount:$CGST

742 : Save Target

745 : end if









900 : End If
950 : End Walk

 

Hello..

In voucher Every InsertCollection must be followed by Set Target..

In your code..Since you are in LedgerEntries Collection (for Sales Ledger) and then in InventroyAllocations..
first each InventoryAllocation must end with its own Set Target..

and then there should be Set Target for LedgerEntries

secondly.. There is Save Target after both GST entries.. which should be Set Target...

Save Traget Should be at end at Voucher Level when all ledgerEntries Collections are exited with Set Target ...

 

Dear Sir, Please Correct this code. i used the amount value from collection(Excel)


[Collection: Led Coll From Access]

ODBC : "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=D:\Masters.xls;DriverId=790"
SQL : "Select * From [Sheet1$]"
SQLObject : Led Object
[Object:Led Object]
LedName : $_1
LedParent : $_2
LedOpBal : $_3
[Collection: Led Coll]
Source Collection : Led coll From Access
Compute : LedName : $LedName
Compute : LedParent : $LedParent
Compute : LedOpBal : $LedOpBal
Fetch: *.*
Client Only: Yes

[#Menu: Gateway of Tally]
Add : Button : Led Import


[Button: Led Import]
Key : Alt + F5
Action : Call : Ledger Import
Title : "Voucher Import From MS Access"

[Function: Ledger Import]
01 : Walk Collection: Led Coll
11: IF : 1=1
112 : NEW OBJECT: Voucher
123 : Set Value: Date : "31-Mar-2018"
154 : Set Value:VoucherTypeName :"Purchase"
165 : Set Value: Narration :"Working"
186 : Set Value: PARTYNAME : "AA"
197 : Set Value: PARTYLEDGERNAME : "AA"
258 : Set Value: amount:"100"
269 : Insert Collection Object: LEDGERENTRIES
290 : Set Value: LedgerName : "AA"
311 : Set Value: Amount:$LedOpBal
342 : Insert Collection Object: LEDGERENTRIES
383 : Set Value: LedgerName : "Pur"
424 : Set Value: Amount:"-99"
465 : Save Target
516 : End If
518 : END WALK

 

Try..

Code:

Function: Ledger Import]
01 : Walk Collection: Led Coll
11: IF : 1=1
112 : NEW OBJECT: Voucher
123 : Set Value: Date : "31-Mar-2018"
154 : Set Value:VoucherTypeName :"Purchase"
165 : Set Value: Narration :"Working"
186 : Set Value: PARTYNAME : "AA-Party"
197 : Set Value: PARTYLEDGERNAME : "AA-Party"
198: Set Value : IsDeemedPoistive : Yes
200 : Set Value: amount:1*$$AsAmount:"100"

269 : Insert Collection Object: LEDGERENTRIES
270    : Set Target: LedgerEntries
290 : Set Value: LedgerName : "AA-Sales"
311 : Set Value: Amount: -1 *$$Asamount:"90"
311: Set Value : IsDeemedPoistive : No
312    : Set Target ..

342 : Insert Collection Object: LEDGERENTRIES
343    : Set Target: LedgerEntries
383 : Set Value: LedgerName : "Sales-BB"
424 : Set Value: Amount:-1 * $$AsAmount:"10"
425: Set Value : IsDeemedPoistive : No
430    : Set Target ..
435    :  SET VALUE   : PersistedView     :  $$SysName:AcctgVchView
465 : Create Target
516 : End If
518 : END WALK