3 Replies - 1417 Views - Last Post: 02 May 2012 - 12:40 AM Rate Topic: -----

#1 manjuswamy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 01-May 12

Repeating set of cells in Excel through vb.net based on some condition

Posted 01 May 2012 - 11:57 PM

hi all,

i dont know how to repeat set of cells in excel through vb.net. Am just fed up with searching for it. Am a student n am currently working on my final year poject n am new to vb.net. My project is for a clearing n forwarding agency. i need to prepare a bill for the duty to be paid. in a particular invoice, there may be single item r multiple item. for each item i've to calculate some values n display it on excel. i've made my excel book to be a template with all the things i should display. n the calculated values'll be displayed in corresponding cells which'll be retrived from database. if its single item its all good n i can prepare the bill easily but for multiple items i've to repeat certain cells from the template. i dunno hw to do it. am attaching my excel file with tis.i want to repeat the cells A24 to J35 'N' number of times with the cells in between filled with respective data from database. kindly help plz...

 Dim xla As Excel.Application
        Dim xlwb As Excel.Workbook
        Dim xlws As Excel.Worksheet
        Dim r As Integer
        Dim cl As Integer
        Dim Obj As Object
        Dim rng As Excel.Range
        xla = New Excel.ApplicationClass
        xlwb = xla.Workbooks.Open("D:\Manju\Reports\BE\Be.xlsx")
        'xla.Workbooks.Close ("D:\Manju\Reports\BE\Be.xlsx")
        xla.Visible = False
        xlws = xlwb.Worksheets("Sheet1")
        rng = xlws.UsedRange
        rng.Font.Name = "Times New Roman"
        rng.Font.Size = 8
        cmd = New SqlCommand("select * from import where sajob='" + Import.Jbtxt.Text + "'", con)
        dr = cmd.ExecuteReader
        If dr.Read Then
            xlws.Range("E1").Value = dr(11)
            xlws.Range("B2").Value = dr(25)
            xlws.Range("C3").Value = dr(6)
            xlws.Range("F8").Value = dr(35)
            xlws.Range("B3").Value = dr(5)
            xlws.Range("B4").Value = dr(12)
            xlws.Range("A5").Value = dr(15)
            xlws.Range("A6").Value = dr(16)
            xlws.Range("B9").Value = dr(34)
            xlws.Range("F9").Value = dr(36)
        End If
        dr.Close()
        If Import.Mdcmbx.Text = "SEA" Then
            cmd = New SqlCommand("select * from imp_sea where JbNo='" + Import.Jbtxt.Text + "'", con)
            dr = cmd.ExecuteReader
            If dr.Read Then
                xlws.Range("B8").Value = dr(3)
                xlws.Range("C8").Value = dr(4)
                xlws.Range("B10").Value = dr(5)
                xlws.Range("F10").Value = dr(7)
                xlws.Range("B11").Value = dr(6)
                xlws.Range("F11").Value = dr(8)
                xlws.Range("B12").Value = dr(9)
                xlws.Range("F12").Value = dr(11)
                xlws.Range("B13").Value = dr(14)
            End If
            dr.Close()
        ElseIf Import.Mdcmbx.Text = "AIR" Then
            cmd = New SqlCommand("select * from imp_air where JobNo='" + Import.Jbtxt.Text + "'", con)
            dr = cmd.ExecuteReader
            If dr.Read Then
                xlws.Range("B8").Value = dr(3) & " " & dr(4)
                xlws.Range("B10").Value = dr(5)
                xlws.Range("F10").Value = dr(7)
                xlws.Range("B11").Value = dr(6)
                xlws.Range("F11").Value = dr(8)
                xlws.Range("B12").Value = dr(9)
                xlws.Range("F12").Value = dr(11)
                xlws.Range("B13").Value = dr(14)
            End If
            dr.Close()
        End If
        cmd = New SqlCommand("SELECT * FROM imp_inv where jobno='" + Import.Jbtxt.Text + "'", con)
        dr = cmd.ExecuteReader
        If dr.Read Then
            xlws.Range("B15").Value = dr(2)
            xlws.Range("C15").Value = dr(3)
            xlws.Range("F15").Value = dr(25)
            xlws.Range("F16").Value = dr(26)
            xlws.Range("F17").Value = dr(27)
            xlws.Range("B16").Value = dr(16)
            xlws.Range("C16").Value = dr(10)
            xlws.Range("E16").Value = dr(13)
            xlws.Range("B17").Value = dr(32)
            xlws.Range("C17").Value = dr(33)
            xlws.Range("B18").Value = dr(35)
            xlws.Range("C18").Value = dr(36)
            xlws.Range("B21").Value = dr(44)
            xlws.Range("E21").Value = dr(45)
            xlws.Range("A28").Value = dr(0)
        End If
        dr.Close()
        cmd = New SqlCommand("select * from SVB where invno='" + Invtxt.Text + "' and jbno='" + Invoice.JbNotxt.Text + "'", con)
        dr = cmd.ExecuteReader
        If dr.Read Then
            xlws.Range("C19").Value = dr(6)
            xlws.Range("C20").Value = dr(8)
        End If
        dr.Close()
        If Invoice.Hsstxt.Text = "Y" Then
            cmd = New SqlCommand("select * from inv_hss where invno='" + Invtxt.Text + "' and jbno='" + Invoice.JbNotxt.Text + "'", con)
            dr = cmd.ExecuteReader
            If dr.Read Then
                xlws.Range("F20").Value = dr(2)
                xlws.Range("H20").Value = dr(3)
            End If
            dr.Close()
        End If
        cmd = New SqlCommand("select * from Imp_invdesc where InvNo='" + Invtxt.Text + "'", con)
        dr = cmd.ExecuteReader
        If dr.Read Then
            xlws.Range("B28").Value = dr(12)
            xlws.Range("C28").Value = dr(11)
            xlws.Range("A29").Value = dr(19)
            xlws.Range("C29").Value = dr(21)
            xlws.Range("D29").Value = dr(13)
            xlws.Range("A30").Value = dr(20)
            xlws.Range("D30").Value = dr(14)
        End If
        dr.Close()
        'cmd = New SqlCommand("select * from cal where jbno='" + Invoice.JbNotxt.Text + "' and invno='" + Invtxt.Text + "'", con)
        'dr = cmd.ExecuteReader
        'If dr.Read Then
        'BCD(amt) 
        xlws.Range("I29").Value = bdv
        'ass(Val) 
        xlws.Range("C30").Value = asvl
        'cvd(amt) 
        xlws.Range("I30").Value = cvd
        'edu cess 2% 
        xlws.Range("I31").Value = ec
        'edu cess 1% 
        xlws.Range("I32").Value = ec1
        'cust edu cess 2% 
        xlws.Range("I33").Value = cec
        'cust edu cess 1% '
        xlws.Range("I34").Value = cec1
        'addl dty '
        xlws.Range("I35").Value = ad
        'total dty 4% '
        xlws.Range("E35").Value = td
        '1 inc ass val ' 
        xlws.Range("B37").Value = asvl
        'bcd+cvd+edu 2 1+cedu 2 1+addl+ ' 
        xlws.Range("I37").Value = asvl / 0.04
        'ass(Val) '
        xlws.Range("C38").Value = asvl
        'total(dty) ' 
        xlws.Range("I38").Value = td
        'bcd(amt) ' 
        xlws.Range("C39").Value = bdv
        'cvd(amt) ' 
        xlws.Range("C41").Value = cvd
        'edu cess cvd ' 
        xlws.Range("C44").Value = ec + ec1
        'cust edu cess ' 
        xlws.Range("I44").Value = cec + cec1
        'addl(dty) '
        xlws.Range("I45").Value = ad
        'schedule cess cvd '
        xlws.Range("C46").Value = cec
        'schedule cust edu cess ' 
        xlws.Range("I46").Value = cec1
        'duty(payable) ' 
        xlws.Range("I48").Value = td
        'End If
        'dr.Close()
        cmd = New SqlCommand("select * from cess where invno='" + Invtxt.Text + "' and jbno='" + Invoice.JbNotxt.Text + "'", con)
        dr = cmd.ExecuteReader
        If dr.Read Then
            xlws.Range("C42").Value = dr(2)
        End If
        dr.Close()
        cmd = New SqlCommand("select * from ENM where invno='" + Invtxt.Text + "' and jbno='" + Invoice.JbNotxt.Text + "'", con)
        dr = cmd.ExecuteReader
        If dr.Read Then
            xlws.Range("E29").Value = dr(0)
            xlws.Range("I39").Value = dr(11)
            xlws.Range("I40").Value = dr(14)
            xlws.Range("I41").Value = dr(8)
            xlws.Range("C43").Value = dr(10)
            xlws.Range("C45").Value = dr(12)
        End If
        dr.Close()
        If xlws.Range("E1").Value = "HOME" Then
            xlws.Range("D3").Value = "N/H"
        ElseIf xlws.Range("E1").Value = "BOND" Then
            xlws.Range("D3").Value = "N/WH"
        ElseIf xlws.Range("E1").Value = "EX-BOND" Then
            xlws.Range("D3").Value = "N/EX"
        End If
        For r = 1 To rng.Rows.Count
            For cl = 1 To rng.Columns.Count
                Obj = CType(rng.Cells(r, cl), Excel.Range)
                xla.Visible = True
            Next
        Next
        xlws.SaveAs("D:\Manju\Reports\BE\" + Invoice.JbNotxt.Text + ".xlsx")



Plz help...

Is This A Good Question/Topic? 0
  • +

Replies To: Repeating set of cells in Excel through vb.net based on some condition

#2 DimitriV  Icon User is offline

  • They don't think it be like it is, but it do
  • member icon

Reputation: 583
  • View blog
  • Posts: 2,738
  • Joined: 24-July 11

Re: Repeating set of cells in Excel through vb.net based on some condition

Posted 02 May 2012 - 12:02 AM

Maybe… 2 loops:
For Row
For Cell
'do stuff
Next
Next

Please don't type in text-speak ;)
Was This Post Helpful? 0
  • +
  • -

#3 manjuswamy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 01-May 12

Re: Repeating set of cells in Excel through vb.net based on some condition

Posted 02 May 2012 - 12:35 AM

View PostDimitriV, on 02 May 2012 - 12:02 AM, said:

Maybe… 2 loops:
For Row
For Cell
'do stuff
Next
Next

Please don't type in text-speak ;)

Was This Post Helpful? 0
  • +
  • -

#4 manjuswamy  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 01-May 12

Re: Repeating set of cells in Excel through vb.net based on some condition

Posted 02 May 2012 - 12:40 AM

well thank u...

but how would i know from which cell to which cell to repeat?
no. of items may be 5 or 20 or more...
Please help..
Thanks again..
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1