Underlining Excel Cell Rows

[DMT Dave]

Hi Guy's i am wondering how close i maybe with this one, i am wanting to underline each row in Excel dependant on recordset qty

So if rs.recordcount = 22 then underline 22 rows

columns always start from A to F as there are 6 fields in the recordset

The titles on the excel form is row A so recordset count starts at B (row2)



This code below doesn't stall but doesn't underline ?

Dims are all ok b9 and m9 are strings
startrow, Col EndRow, Col are Integers
pOpen (path open) fOpen (File Open) and pSave (Path Save) and fSave (File Save) are all strings

It doesn't stall to tell what isn't correct but but doesn't underline ???

Set rsOut = CurrentDb.OpenRecordset("Select tblRemovals.RecordNo, tblRemovals.Client, tblRemovals.Town, tblRemovals.Make, tblRemovals.Model, tblRemovals.SerialNo " _& "From tblRemovals WHERE Make = '" & b9 & "' And Model = '" & m9 & "' And Status = '" & myStatus & "' ORDER BY RecordNo;")
StartRow = 2
EndRow = rsOut.RecordCount
StartCol = 1
EndCol = 6


Set apXL = CreateObject("Excel.Application")
Set xlWB = apXL.Workbooks.Open(pSave & fSave)
apXL.Workbooks.Open pSave & fSave, True, False
apXL.Visible = True
With xlWB
.Worksheets(1).Cells(2, 1).CopyFromRecordset rsOut
.Worksheets(1).Cells.EntireColumn.AutoFit
.Worksheets(1).Range(StartCol & StartRow & ":" & EndCol & EndRow).Borders(xlEdgeBottom).LineStyle = xlContinuous

[Micron]

Did you step through this code and watch your variables? I think not otherwise you would likely know the problem, unless I'm missing something because a chunk of code is missing. When you do that, what is the value for the recordset count? Probably is 1 but do you know why?

[DMT Dave]

Hi Micron, forgive my experience, how do I step through the code ?

The chunk of code missing are Objects for Excel, Integers for the numerical and Strings for file path, does this explain ?

[Micron]

I took a look because I say that so often, like here
https://www.accessforums.net/showthr...t=step+through

If you didn't know then, why didn't you didn't ask?

You put a break on a line somewhere by clicking on the left gray bar in the code window then cause the code to run. It will stop on that break. NOTE that the line it breaks on is not executed yet, so any variables you check will not have been affected by that line of code. You need to step off of that line and execute it with F8 or perhaps a function key (fn) on your keyboard as well as F8. Depends on your keyboard. You can also use Debug menu. I customize my toolbar and add the common ones such as F8, F5, Indent, Outdent and Compile.

[Gicu]

Hi Dave,
Here is how I do it for a Totals row, I think you're missing the thickness:

'we need to add a total now
        lastRow = oWS.Range("A" & oWS.Rows.Count).End(xlUp).Row
        oWS.Range("A" & lastRow + 1).Activate
        oWS.Range("A" & lastRow + 1) = "TOTAL:"
        oWS.Range("A" & lastRow + 1).Font.Bold = True
        oWS.Range("B" & lastRow + 1) = .WorksheetFunction.Sum(.Selection.CurrentRegion)
        oWS.Range("B" & lastRow + 1).Font.Bold = True
        oWS.Range("A" & lastRow & ":B" & lastRow).Borders(xlEdgeBottom).LineStyle = xlContinuous
        oWS.Range("A" & lastRow & ":B" & lastRow).Borders(xlEdgeBottom).Weight = xlThick
        oWS.Range("A" & lastRow & ":B" & lastRow).Borders(xlEdgeBottom).ColorIndex = xlAutomatic

Cheers,
Vlad

[DMT Dave]

Thank you all for your help, i will try suggested by Micron and Vlad thanks again you guys

[DMT Dave]

Hi Vlad, i have managed to underline row 22 (qty of records) using xlEdgeTop and xlEdgeBottom

How do i underline rows 2 to 22 (ie recordset minus 1) as Row 1 is header ?

Hope i am making sense

This is what i have adapted, i will take the data out for privacy reasons but i have snipped the result

Sorry again for being a little naïve on this!

Set apXL = CreateObject("Excel.Application")Set xlWB = apXL.Workbooks.Open(pSave & fSave)
apXL.Workbooks.Open pSave & fSave, True, False
apXL.Visible = True
With xlWB
.Worksheets(1).Cells(2, 1).CopyFromRecordset rsOut
EndRow = .Worksheets(1).Range("B" & .Worksheets(1).Rows.Count).End(xlUp).Row
.Worksheets(1).Range("A" & EndRow & ":F" & EndRow).Borders(xlEdgeBottom).LineStyle = xlContinuous
.Worksheets(1).Range("A" & rsOut.RecordCount & ":F" & EndRow).Borders(xlEdgeBottom).LineStyle = xlContinuous
.Worksheets(1).Range("A" & EndRow & ":F" & EndRow).Borders(xlEdgeTop).LineStyle = xlContinuous

[accesstos]

Hi Dave!
In your first code snippet, with 22 records in recordset, (StartCol & StartRow & ":" & EndCol & EndRow)="12:622". I think that you have an underline in row 622. Check it.

I propose this:

       Set rsOut = CurrentDb.OpenRecordset("Select tblRemovals.RecordNo, tblRemovals.Client, tblRemovals.Town, tblRemovals.Make, tblRemovals.Model, tblRemovals.SerialNo " _& "From tblRemovals WHERE Make = '" & b9 & "' And Model = '" & m9 & "' And Status = '" & myStatus & "' ORDER BY RecordNo;")
    With rsOut
        If .RecordCount > 0 Then
            .MoveLast
            .MoveFirst
            EndRow = .RecordCount + 1
        End If
    End With

    Set apXL = CreateObject("Excel.Application")
    Set xlWB = apXL.Workbooks.Open(pSave & fSave)
    'apXL.Workbooks.Open pSave & fSave, True, False 'Workbook pSave & fSave is already open.
    With xlWB.Worksheets(1)
        .Cells(2, 1).CopyFromRecordset rsOut
        .Cells.EntireColumn.AutoFit
        .Range("A2:F" & EndRow).Borders(xlInsideHorizontal).LineStyle = xlContinuous
       '[...]
    End With
    apXL.Visible = True

Cheers,
John

[Micron]

            .MoveLast

Therein lies the crux of what I was hoping the Dave would discover for himself. IF there are any records, without a MoveLast the count is 1, thus the beginning row is 2 and the ending row is 1.

StartRow = 2
EndRow = rsOut.RecordCount

Hardly ever going to work, I'd say, regardless of whether or not the correct construct is used for the line properties.

[DMT Dave]

Thank you guys, i think i needed the understanding what micron has suggested, thank you all will adapt in the morning, i have written a number systems in access and never cease to learn the easy way

Thank you again,

Keep safe

[DMT Dave]

Thank you guy's all working