• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to get two rows of data at a time in loop in VB6?

K

Kuldeep singh

New Member
Hello, I have a following sample excelsheet data:

voucherid voucherno transdate account debit credit
I 0000000025 26-04-2013 ABCD ENTERPRIZES 0.00 50,000.00
I 0000000025 26-04-2013 ABCD BANK 50,000.00 0.00
I CAS0000002 23-05-2013 SIMPLE ENGINEERING SOLUTIONS 0.00 1,519.00
I CAS0000002 23-05-2013 CASH BOOK 1,519.00 0.00
I CAS0000003 23-05-2013 MARKETING 1 0.00 1.00
I CAS0000003 23-05-2013 CASH BOOK 1.00 0.00
J 0000000001 27-05-2013 BPO LIMITED 12,600.00 0.00
J 0000000001 27-05-2013 EXP TAX CONSULTING 0.00 12,600.00

Based on voucherid, voucherno(will not repeat more than two times in a sheet) and transdate continuously in two rows, i want account with debit and credit amount in a loop at a time so that i can make one single data entry in VB 6.0. Please i need a help on this, thanks in advance.
 
Hi Kuldeep ,

Please give a sample output for the 6 entries with voucherid I.

Will every voucherno necessarily have two entries , or can there be a voucherno with only one entry ?

Narayan
 
Hello Narayank991,

First thank to reply me back soon.

I want to generate XML With help of VB 6.0, I have below sample code snippet which gets single row data:

Dim XMLPath As String
XMLPath = "\Payment.Xml"

Path = App.Path & "\"
CommonDialog1.Filter = "Excel Files (*.xlsx)|*.xlsx"
CommonDialog1.FilterIndex = 1

CommonDialog1.ShowOpen
Title = CommonDialog1.FileTitle
inputPath = CommonDialog1.FileName

If Len(Title) > 0 Then
Dim recPayment As New Recordset '************ Fields declar for read excel file ************

'************* Connection to the excel file *************

'connString = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & inputPath
connString = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};IMEX=1;" & "DBQ=" & inputPath & ";"
recPayment.Open "select * from [Sheet1$]", connString

F1 = App.Path & XMLPath

Close #1
Open F1 For Output As #1
VoucherType = "Payment1"

recPayment.MoveFirst

Print #1, StrEnvelop
Do While Not recPayment.EOF
'StrBody = "<!-- Body Part Start -->" & vbCrLf

StrBody = "<TALLYMESSAGE xmlns:UDF='TallyUDF'>" & vbCrLf & _
"<VOUCHER VCHTYPE='" & VoucherType & "' ACTION='Create' OBJVIEW='Accounting Voucher View'>" & vbCrLf

Partyname = recPayment.Fields(2).Value
BankName = recPayment.Fields(8).Value
Paymethod = recPayment.Fields(10).Value

' Here will be the code to generate XML after i get values in variables from excel file.
recPayment.MoveNext
' StrXML = StrBody
Print #1, StrXML
Loop
Print #1, StrEnd
Close #1
MsgBox "Payment XML File Generated"
End If

I Hope u understand with this.
And excel file will always contains rows that contains same voucher number and We have to use to generate a single entry which will contain account with debit and account with credit.
 
Hi Kuldeep ,

I think there is some confusion ; when I said output I did not mean the final output which you will carry out.

As I understand you , you have the above data which you posted initially in an Excel file ; you wish to convert the above data , which is in two rows for each voucherid + voucherno combination , into one single row.

I want you to specify the format of this one row.

The input data has 6 columns ; how many columns will the single output row extend over , and what data will these columns contain from the original two input rows ?

Narayan
 
Hello Narayank991,

Here is the input data,
voucherid voucherno transdate account debit credit
I 0000000025 26-04-2013 ABCD ENTERPRIZES 0.00 50,000.00
I 0000000025 26-04-2013 ABCD BANK 50,000.00 0.00
I CAS0000002 23-05-2013 SIMPLE ENGINEERING SOLUTIONS 0.00 1,519.00
I CAS0000002 23-05-2013 CASH BOOK 1,519.00 0.00
I CAS0000003 23-05-2013 MARKETING 1.00 1.00
I CAS0000003 23-05-2013 CASH BOOK 1.00 0.00
J 0000000001 27-05-2013 BPO LIMITED 12,600.00 0.00
J 0000000001 27-05-2013 EXP TAX CONSULTING 0.00 12,600.00

I want output as :
voucherid voucherno transdate account1 credit account2 debit
I 0000000025 26-04-2013 ABCD ENTERPRIZES 50,000.00 ABCD BANK50,000.00
I CAS0000002 23-05-2013 SIMPLE ENGINEERING SOLUTIONS 1,519.00 CASH BOOK1,519.00
I CAS0000003 23-05-2013 MARKETING 1.00 CASH BOOK 1.00
J 0000000001 27-05-2013 BPO LIMITED 12,600.00 EXP TAX CONSULTING 12,600.00
 
Hi there,
I was wondering, have you manually modified the string "I CAS0000003 23-05-2013 MARKETING 1.00 1.00"? More specific the space-character between "marketing" and the first "1.00"?

FYI:
There are two types of "space"-characters being used in your sample: char32 and char160. In every line, except the one I mentioned, the last space after the piece of text is the character 160. My idea was to look for char160 after the first 24 charactars.. but this isn't working if this is not always the case.
 
Kuldeep singh said:
Still Waiting for reply. :rolleyes::rolleyes:
Click to expand...
Hi, Kuldeep singh!

Are you very hurried? If so please take a break and dedicate 5 minute to what follows. Posting without adding any useful information just for bumping up a topic doesn't guarantee neither a faster assistance nor getting the interest of people who might be reading that post, but rather the opposite effect of discouraging them.

If you'd have read the main green sticky post at this forums main page...
http://chandoo.org/forum/threads/new-users-please-read.294/
...you should have noticed this points (and if you did it seems as if you should do it again):

"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."

"If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."

"Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out."

"Say "Thanks", whenever you can. Recognize when someone has bothered to go to the trouble and time to assist you with your question for free. Often readers will spend several hours working on a solution to a problem, a line of recognition will go a long way."

Regards!

PS: If it's a high priority issue and you can't even wait... how much?... oh, 3 hours and a while... you always have the alternative of hiring a local consultant who may provide you the exact tailored service. Should I remember you that these forums are supported by contributors who dedicate part of their time in an absolutely free way to help other people and answer questions?
 
Hi Kuldeep ,

Sorry for the delay , but can you clear up a major source of confusion viz. is your data in 1 column or is it in 6 columns with the following being the column headers ?

voucherid voucherno transdate account debit credit

Narayan
 
Hi, Kuldeep singh!

Give a look at the uploaded file. It has a formula solution in columns H:N and the following VB6 code in the standard module.
Code: 
Option Explicit

Sub JustASimpleMatterOfControlBreaksProgrammingI()
' snippet in VB6 to be inserted where it corresponds by section
    ' constants
    ' declarations
    Dim sVoucherID As String, sVoucherNo As String, dTransDate As Date
    Dim sAccount1 As String, cDebit As Currency, sAccount2 As String, cCredit1 As Currency
    ' start
    recpayment.MoveFirst
    ' process
    Do Until Not recpayment.EOF
        sVoucherIDz = recypayment.Fields(1).Value
        sVoucherNoz = recypayment.Fields(2).Value
        sTransdate = recypayment.Fields(3).Value
        sAccount1 = ""
        cDebit = 0
        sAccount2 = ""
        cCredit = 0
        Do Until Not recpayment.EOF Or _
                recypayment.Fields(1).Value <> svoucherzid Or _
                recypayment.Fields(2).Value <> sVoucherNoz
            If recpayment.Fields(5).Value <> 0 Then
                sAccount1 = recpayment.Fields(4).Value
                sdebit = recpayment.Fields(5).Value
            ElseIf recpayment.Fields(6).Value <> 0 Then
                sAccount2 = recpayment.Fields(4).Value
                sdebit = recpayment.Fields(6).Value
            End If
            recpayment.MoveNext
        Loop
        anything.VoucherID = sVoucherIDz
        anything.VoucherNo = sVoucherNoz
        anything.TransDate = dTransDate
        anything.Account1 = sAccount1
        anything.Debit = sdebit
        anything.Account2 = sAccount2
        anything.Credit = sCredit
    Loop
    ' end
End Sub

Obviously it doesn't compile as it is and it's not tested, just written, so adjust properly if required.

Regards!
 

Attachments

  • How to get two rows of data at a time in loop in VB6_ (for Kuldeep singh at chandoo.org).xlsm
    16.1 KB · Views: 1
You must log in or register to reply here.
Back
Top