3 Replies - 5959 Views - Last Post: 18 July 2010 - 06:15 PM Rate Topic: -----

#1 Guest_S. JEYARAMAN*


Reputation:

VB6 - PRINT SUM AT THE LAST ROW OF COLUMN G

Posted 15 July 2010 - 07:01 AM

Private Sub cmdexp2_Click()
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   
   
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   
   'Rem Make Excel Visible
    oExcel.Visible = True
  'REM FILL IN ROW VALUES
  Dim DataArray(1 To 1000, 1 To 100) As Variant
  
  Dim r As Integer
  Dim NumberOfRows As Integer
  NumberOfRows = Adodc1.Recordset.recordcount
  Adodc1.Recordset.MoveFirst
  
  For r = 1 To NumberOfRows
  
  DataArray(r, 1) = Adodc1.Recordset.Fields("jobno")
  DataArray(r, 2) = Adodc1.Recordset.Fields("customer")
  DataArray(r, 3) = Adodc1.Recordset.Fields("colour")
  DataArray(r, 4) = Adodc1.Recordset.Fields("date2")
  DataArray(r, 5) = Adodc1.Recordset.Fields("weight")
  DataArray(r, 6) = Adodc1.Recordset.Fields("dye1")
  DataArray(r, 7) = Adodc1.Recordset.Fields("dye1qty")
  DataArray(r, 8) = Adodc1.Recordset.Fields("dye1add")
 
  
  Adodc1.Recordset.MoveNext
  
  Next
  
  
  'PAGE BREAK UPS
   With oExcel.ActiveSheet
   oExcel.ActiveSheet.PageSetup.Zoom = False
   'oExcel.ActiveSheet.PageSetup.FitToPagesTall = 1
   oExcel.ActiveSheet.PageSetup.FitToPagesWide = 1
  End With
  
  Set oSheet = oBook.Worksheets(1)
  
   oBook.Worksheets(1).Range("D1").Value = "DYES CONSUMPTION LIST"
   oBook.Worksheets(1).Range("D1").Font.Name = "Verdana"
   oBook.Worksheets(1).Range("D1").Font.Size = 15
   oBook.Worksheets(1).Range("D1").Font.Bold = True
   
   
   oSheet.Range("A2:H2").Font.Name = "Verdana"
    oSheet.Range("A2:H2").Font.Size = 13
   oSheet.Range("A2:H2").Font.Bold = True
  oSheet.Range("A2:H2").columnwidth = 23
  oSheet.Range("A2:H2").Value = Array("JOBNO", "CUSTOMER", "COLOUR", "DATE", "WEIGHT", "DYE1", "DYE1QTY", "DYE1ADD")
  oSheet.Range("A4").Resize(NumberOfRows, 8).Value = DataArray
    
  oBook.Worksheets(1).Range("A1:A1000").RowHeight = 25
  
  oBook.Worksheets(1).Range("A1:A1000").Font.Bold = True
  oBook.Worksheets(1).Range("B1:B1000").Font.Bold = True
  oBook.Worksheets(1).Range("C1:C1000").Font.Bold = True
  oBook.Worksheets(1).Range("D1:D1000").Font.Bold = True
  oBook.Worksheets(1).Range("E1:E1000").Font.Bold = True
  oBook.Worksheets(1).Range("F1:F1000").Font.Bold = True
  oBook.Worksheets(1).Range("G1:G1000").Font.Bold = True
  oBook.Worksheets(1).Range("H1:H1000").Font.Bold = True
  
  'for center alignment
   
    oBook.Worksheets(1).Range("A1:A1000").HorizontalAlignment = myVAlignCenter
    oBook.Worksheets(1).Range("B1:B1000").HorizontalAlignment = myVAlignCenter
    oBook.Worksheets(1).Range("C1:C1000").HorizontalAlignment = myVAlignCenter
    oBook.Worksheets(1).Range("D1:D1000").HorizontalAlignment = myVAlignCenter
    oBook.Worksheets(1).Range("E1:E1000").HorizontalAlignment = myVAlignCenter
    oBook.Worksheets(1).Range("F1:F1000").HorizontalAlignment = myVAlignCenter
    oBook.Worksheets(1).Range("G1:G1000").HorizontalAlignment = myVAlignCenter
    oBook.Worksheets(1).Range("H1:H1000").HorizontalAlignment = myVAlignCenter
       
    'FOR PRINTING AT THE LASTROW
    
      oBook.Worksheets(1).Range("G1").End(xlDown).Formula = "=Sum(G3:G100)"
     
      


End Sub


I am not able to print at the last column INSTEAD I am getting at the column heder at "G3" as shwon below "95.548"
replacing the column header

DYES CONSUMPTION LIST
JOBNO CUSTOMERCOLOUR DATE WEIGHT DYE1 95.548 DYE1ADD

K1618 SLK MARINE 7/14/2010 581 MEAC YELLLOW F3R 11.8524
K1632 SNQS DARK NAVY 7/14/2010 1512 MEAC YELLLOW F3R 25.4772
K1639 SKL RED 7/14/2010 225 MEAC YELLLOW F3R 0.81
K1643 QUALITY GREY 7/14/2010 91 MEAC YELLLOW F3R 1.64255
K1659 SNQS CHERRY CRUSH 7/14/2010 131 MEAC YELLLOW F3R 2.0305
K1662 SNQS DARK NAVY 7/14/2010 850 MEAC YELLLOW F3R 14.3225
K1663 SNQS DARK NAVY 7/14/2010 1484 MEAC YELLLOW F3R 25.0054
K1670 SKL CYCLOMAN 7/14/2010 7 MEAC YELLLOW F3R 0.0007
K1673 SNQS DARK NAVY 7/14/2010 855 MEAC YELLLOW F3R 14.40675

can anyone help me to get the total printed at the last row of dynamically changing row lenght.

thank in advance

With best regards

S. Jeyaraman

This post has been edited by born2c0de: 15 July 2010 - 09:51 AM


Is This A Good Question/Topic? 0

Replies To: VB6 - PRINT SUM AT THE LAST ROW OF COLUMN G

#2 bohemian9485   User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 38
  • Joined: 05-August 09

Re: VB6 - PRINT SUM AT THE LAST ROW OF COLUMN G

Posted 15 July 2010 - 05:22 PM

Private Sub cmdexp2_Click()
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim ColumnHeader As Variant
Dim lRow As Long, lCol As Long
Dim dTotal As Double

ColumnHeader = Array("JOBNO", "CUSTOMER", "COLOUR", "DATE", "WEIGHT", "DYE1", "DYE1QTY", "DYE1ADD")

Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Sheets(1)

' report header
With oSheet.Cells(4, 1)
  .Value = "DYES CONSUMPTION LIST"
  With .Font
    .Name = "Verdana"
    .Size = 15
    .FontBold = True
  End With
End With

' populate column header
For lCol = 1 To 8
  With oSheet.Cells(5, lCol)
    .Value = ColumnHeader(lCol - 1)
    With .Font
      .Name = "Verdana"
      .Size = 15
      .FontBold = True
    End With
  End With
Next lCol

' data population
lRow = 7
Adodc1.Recordset.MoveFirst
Do
  dTotal = dTotal + Adodc1.Recordset.Fields("dye1qty").Value
  oSheet.Rows(lRow).RowHeight = 25
  For lCol = 1 To 8
    With oSheet.Cells(lRow, lCol)
      .Value = Adodc1.Recordset.Fields(lCol - 1).Value
      .FontBold = True
      .HorizontalAlignment = myVAlignCenter
    End With
  Next lCol
  lRow = lRow + 1
  Adodc1.Recordset.MoveNext
Loop While Not Adodc1.Recordset.EOF

' put total in the last row of column G
oSheet.Cells(lRow, 7).Value = dTotal

End Sub



The above code use the Excel cells object instead of the range object. Hope it can help you some way.

Good luck.
Was This Post Helpful? 0
  • +
  • -

#3 Guest_S. JEYARAMAN*


Reputation:

Re: VB6 - PRINT SUM AT THE LAST ROW OF COLUMN G

Posted 17 July 2010 - 12:50 AM

View Postbohemian9485, on 15 July 2010 - 04:22 PM, said:

Private Sub cmdexp2_Click()
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim ColumnHeader As Variant
Dim lRow As Long, lCol As Long
Dim dTotal As Double

ColumnHeader = Array("JOBNO", "CUSTOMER", "COLOUR", "DATE", "WEIGHT", "DYE1", "DYE1QTY", "DYE1ADD")

Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Sheets(1)

' report header
With oSheet.Cells(4, 1)
  .Value = "DYES CONSUMPTION LIST"
  With .Font
    .Name = "Verdana"
    .Size = 15
    .FontBold = True
  End With
End With

' populate column header
For lCol = 1 To 8
  With oSheet.Cells(5, lCol)
    .Value = ColumnHeader(lCol - 1)
    With .Font
      .Name = "Verdana"
      .Size = 15
      .FontBold = True
    End With
  End With
Next lCol

' data population
lRow = 7
Adodc1.Recordset.MoveFirst
Do
  dTotal = dTotal + Adodc1.Recordset.Fields("dye1qty").Value
  oSheet.Rows(lRow).RowHeight = 25
  For lCol = 1 To 8
    With oSheet.Cells(lRow, lCol)
      .Value = Adodc1.Recordset.Fields(lCol - 1).Value
      .FontBold = True
      .HorizontalAlignment = myVAlignCenter
    End With
  Next lCol
  lRow = lRow + 1
  Adodc1.Recordset.MoveNext
Loop While Not Adodc1.Recordset.EOF

' put total in the last row of column G
oSheet.Cells(lRow, 7).Value = dTotal

End Sub



The above code use the Excel cells object instead of the range object. Hope it can help you some way.

Good luck.


THANKS FOR YOUR REPLY

THE ABOVE METHOD WORKS WELL, BUT NOT WITH THE NULL CELL

THE BELOW METHOD WORKS WELL WITH THE VARIABLE LENGTH COLUMN WITH NULL CELL TOO.
Private Sub cmdexp2_Click()
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   
    
    
   
    'Set oSheet = oExcel.Worksheets.Add
   
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   
   'Rem Make Excel Visible
    oExcel.Visible = True
  'REM FILL IN ROW VALUES
  Dim DataArray(1 To 1000, 1 To 100) As Variant
  
  Dim r As Integer
  Dim NumberOfRows As Integer
  NumberOfRows = Adodc1.Recordset.recordcount
  Adodc1.Recordset.MoveFirst
  
  For r = 1 To NumberOfRows
  
  DataArray(r, 1) = Adodc1.Recordset.Fields("jobno")
  DataArray(r, 2) = Adodc1.Recordset.Fields("customer")
  DataArray(r, 3) = Adodc1.Recordset.Fields("colour")
  DataArray(r, 4) = Adodc1.Recordset.Fields("date2")
  DataArray(r, 5) = Adodc1.Recordset.Fields("weight")
  DataArray(r, 6) = Adodc1.Recordset.Fields("dye")
  DataArray(r, 7) = Adodc1.Recordset.Fields("dyeqty")
  DataArray(r, 8) = Adodc1.Recordset.Fields("dyeadd")
 
  
  Adodc1.Recordset.MoveNext
  
  Next
  
  
  'PAGE BREAK UPS
   'With oExcel.ActiveSheet
   'oExcel.ActiveSheet.PageSetup.Zoom = False
   'oExcel.ActiveSheet.PageSetup.FitToPagesTall = 1
   'oExcel.ActiveSheet.PageSetup.FitToPagesWide = 1
  'End With
  
  Set oSheet = oBook.Worksheets(1)
  
   oBook.Worksheets(1).Range("D1").Value = "DYES CONSUMPTION LIST"
   oBook.Worksheets(1).Range("D1").Font.Name = "Verdana"
   oBook.Worksheets(1).Range("D1").Font.Size = 15
   oBook.Worksheets(1).Range("D1").Font.Bold = True
   
  
  
  
   oSheet.Range("A2:H2").Font.Name = "Verdana"
    oSheet.Range("A2:H2").Font.Size = 13
   oSheet.Range("A2:H2").Font.Bold = True
  oSheet.Range("A2:H2").ColumnWidth = 23
  oSheet.Range("A2:H2").Value = Array("JOBNO", "CUSTOMER", "COLOUR", "DATE", "WEIGHT", "DYE", "DYEQTY", "DYEADD")
  oSheet.Range("A4").Resize(NumberOfRows, 8).Value = DataArray
  
  
  oBook.Worksheets(1).Range("A1:A1000").RowHeight = 25
  
  oBook.Worksheets(1).Range("A1:A1000").Font.Bold = True
  oBook.Worksheets(1).Range("B1:B1000").Font.Bold = True
  oBook.Worksheets(1).Range("C1:C1000").Font.Bold = True
  oBook.Worksheets(1).Range("D1:D1000").Font.Bold = True
  oBook.Worksheets(1).Range("E1:E1000").Font.Bold = True
  oBook.Worksheets(1).Range("F1:F1000").Font.Bold = True
  oBook.Worksheets(1).Range("G1:G1000").Font.Bold = True
  oBook.Worksheets(1).Range("H1:H1000").Font.Bold = True
  
  'for center alignment
   
     oBook.Worksheets(1).Range("A1:A1000").HorizontalAlignment = myVAlignCenter
      oBook.Worksheets(1).Range("B1:B1000").HorizontalAlignment = myVAlignCenter
      oBook.Worksheets(1).Range("C1:C1000").HorizontalAlignment = myVAlignCenter
      oBook.Worksheets(1).Range("D1:D1000").HorizontalAlignment = myVAlignCenter
       oBook.Worksheets(1).Range("E1:E1000").HorizontalAlignment = myVAlignCenter
       oBook.Worksheets(1).Range("F1:F1000").HorizontalAlignment = myVAlignCenter
       oBook.Worksheets(1).Range("G1:G1000").HorizontalAlignment = myVAlignCenter
       oBook.Worksheets(1).Range("H1:H1000").HorizontalAlignment = myVAlignCenter
       
          
      
 FOR SUM
 Dim X As Long
 
  X = Range("G65536").End(xlUp).Row
  Range("G" & X + 2) = Application.WorksheetFunction.Sum(Range("G1:G" & X))

  Dim Y As Long
 
  Y = Range("H65536").End(xlUp).Row
 Range("H" & X + 2) = Application.WorksheetFunction.Sum(Range("H1:H" & X))



I HAVE DONE A MISTAKE WITHOUT ADDING (MICROSOFT 11 EXCEL LIBRARY) FOR THE REFERENCE TO WORK WITH EXCEL
THEN I TRIED NOW BOTH THE METHOD WORKS WELL


THANKS AND

WITH BEST REAGARDS

S. JEYARAMAN

This post has been edited by born2c0de: 17 July 2010 - 10:18 AM

Was This Post Helpful? 0

#4 thava   User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,607
  • Joined: 17-April 07

Re: VB6 - PRINT SUM AT THE LAST ROW OF COLUMN G

Posted 18 July 2010 - 06:15 PM

  oBook.Worksheets(1).Range("A1:A1000").Font.Bold = True
  oBook.Worksheets(1).Range("B1:B1000").Font.Bold = True
  oBook.Worksheets(1).Range("C1:C1000").Font.Bold = True
  oBook.Worksheets(1).Range("D1:D1000").Font.Bold = True
  oBook.Worksheets(1).Range("E1:E1000").Font.Bold = True
  oBook.Worksheets(1).Range("F1:F1000").Font.Bold = True
  oBook.Worksheets(1).Range("G1:G1000").Font.Bold = True
  oBook.Worksheets(1).Range("H1:H1000").Font.Bold = True


instead of doing like this
use this
  oBook.Worksheets(1).Range("A1:H1000").Font.Bold = True

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1