Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: VBA Code Help Again

    Hello Gentlemen,

    I am planning to read the data from table T_PaperRoll_Consumption and re-present in Excel sheet.

    I have done all the necessary settings in VBA like opening Excel Application, Worksheet, referenced the DAO and Excel references turned on. My code works fine. But I need to go a bit ahead. I need alternate row of Excel sheet should be colored while it automatically generats the data.

    Following code line gives me desired result of color row but the problem is it is leaving one blank White line without data (Pls ref. the pic I attached) And data is appearing on Gray color row only which results more rows on Excel sheet. I need the data to appear on both Black and Gray lines.

    Can somebody check where I made the mistake ?

    STARTING CODE HERE...
    ...
    ...

    Set rst = CurrentDb.OpenRecordset("SELECT * FROM T_PaperRoll_Consumption WHERE ShiftDate BETWEEN #" & _
    Me!TxtFrom & "# AND #" & Me!TxtTo & "# ORDER BY RolNo, RollSize;")

    rst.MoveFirst

    Do While Not rst.EOF

    objSht.Cells(iRow, 1).Value = rst!RolNo
    objSht.Cells(iRow, 2).Value = rst!RollSize
    objSht.Cells(iRow, 3).Value = rst!GSM
    objSht.Cells(iRow, 4).Value = rst!Orig_Weight
    objSht.Cells(iRow, 5).Value = rst!Orig_DiaMeter
    objSht.Cells(iRow, 6).Value = rst!Remained_Weight
    objSht.Cells(iRow, 7).Value = rst!Remained_DiaMeter
    objSht.Cells(iRow, 8).Value = rst!Consumed_Weight
    objSht.Cells(iRow, 9).Value = rst!Consumed_DiaMeter
    objSht.Cells(iRow, 10).Value = rst!Part_No
    objSht.Cells(iRow, 11).Value = rst!OriginOf
    objSht.Cells(iRow, 12).Value = rst!SuppliersRollNo

    PROBLEM STARTS HERE

    If iRow = iRow Then
    objSht.Rows(iRow).Select
    objXL.Selection.Interior.ColorIndex = 15
    iRow = iRow + 1
    End If

    PROBLEM ENDS HERE

    iRow = iRow + 1

    rst.MoveNext
    Loop

    REST CODE IS HERE....
    ....
    ..

    Thanks in advance.

    With kind regards,
    Ashfaque
    Attached Thumbnails Attached Thumbnails RowColors.JPG  

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    A couple of problems I'm afraid:
    Code:
    If iRow = iRow Then
    is always true. Perhaps you mean:
    Code:
    If iRow Mod 2 = 0 Then
    Also - you increment iRow twice:
    Code:
    ...
    iRow = iRow + 1
    End If
     
    PROBLEM ENDS HERE
     
    iRow = iRow + 1
    ....
    Get rid of the first iRow = iRow + 1

    Finally (and this is just advice now) - you can avoid writing a cell at a time (slow to execute, not very scalable, requires more code) using .CopyFromRecordset method. An example is here:
    http://www.dbforums.com/showthread.p...=1#post6222281

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Excellent ! ! !

    Thanks again Pootle .

    Mod 2 = 0 Worked well.

    With kind regards,
    Ashfaque

  4. #4
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Oh..

    I have just seen it coloring alternate row upto the end of row.

    Can limit be set for it ? I mean color row should start from left to the last cell of Data. In my case I used horizontally A1:L12 means column 12 will be the last to color and not ahead.

    Vertically it is ok.

    Can again somebody help me to come over this problem ?

    With kind regards,
    Ashfaque

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Something like:
    Code:
    objXL.Range(objXL.Cells(iRow, 1),objXL.Cells(iRow, 12)).Interior.ColorIndex = 15
    ??
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Not something like...It is Exactly.....wow..

    I appreciate you immediate response pootle.

    Thanks a ton.

    With kind regards,
    Ashfaque

  7. #7
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Hi again,

    I have following sub to draw border to the cells in Excel Sheet that mentioned in first question of this thread.

    Public Sub DrawLines(objXL As Object, objSht As Object, strRange As String)

    objSht.Range(strRange).Borders(xlDiagonalDown).Lin eStyle = xlNone
    objSht.Range(strRange).Borders(xlDiagonalUp).LineS tyle = xlNone

    With objSht.Range(strRange).Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With objSht.Range(strRange).Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With objSht.Range(strRange).Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With objSht.Range(strRange).Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With objSht.Range(strRange).Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With objSht.Range(strRange).Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With

    End Sub


    I called this sub in VBA after starting pulling data:

    Call DrawLines(objXL, objSht, "A10"L12")

    Syntax of the above needs declaration of range of cells that needs border and due to this the limitation occured while we don't know how many rows of excel sheet will be used by data. In above, it produces borders to cells starts from A10 To L12 (3 rows only bcz we specified).

    Can some body please tell me how can get rid of this. It should provide border until data ends.

    With kind regards,
    Ashfaque

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can supply only the top left most cell and use code a bit like:
    Code:
    Set exRange = exSheet.Range(exSheet.Cells(1, 1), exSheet.Cells.SpecialCells(xlLastCell))
    Do a search for xlLastCell in the attachment here to see it working in action:
    http://www.dbforums.com/showpost.php...81&postcount=4

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •