[RESOLVED] Questions On Formatting Recordset Output In Excel

[Hack]

Reference this thread which I monitored closely as it evolved.

I'm using the code that DKenny supplied in that thread to output a recordset to a newly created Excel Worksheet, and the recordset data is displaying just fine. I have questions on formatting.

1. My first column is a number, which by default in Excel, is right justified. I need to left justify it. I thought converting the recordset element to a string before dumping it to Excel would do the trick, but it has not. How do I left justify this number?

2. I need to print the report in Landscape. What would I use ensure this would happen (I know I can do it through the page setup in Excel, but I'd like to pass the Landscape attribute to the worksheet when it gets created.)

3. How do I turn the gridlines off programmatically?

4. I will never know how many records are in the recordset, so I will never know how far down the page the records will go. Regardless of that, however, I need to print two totals two lines below the last line of the worksheet. How would I calculate in what row those totals should go?

[DKenny]

This should get you started.

Sub HackFormat()
Dim oWin As Window
Dim lTotalRow As Long
    
    With ThisWorkbook.Worksheets(1)
        
        'Q1 Left Align Column A
        .Columns(1).HorizontalAlignment = xlLeft
        
        'Q2 Set Orintation to Landscape
        .PageSetup.Orientation = xlLandscape
        
        'Q4 Find Last Used Row + 2 as Row number for Totals
        lTotalRow = .Range("A1").End(xlDown).Row + 2
        
    End With
    
    'Q3 Turn off gridlines
    For Each oWin In ThisWorkbook.Windows
        oWin.DisplayGridlines = False
    Next oWin
    
End Sub

[Hack]

Thanks.

DisplayGridLines doesn't seem to be a method of oWin. In fact, I get the "Method or data member not found" error message.

The drop down intellisense for oWin reveals:

DisplayHorzontialScrollbar
DisplayLeftScrollbar
DisplayRulers
DisplayScreenTips
DisplayVerticalRuler
DisplayVerticalScrollbar

But, no DisplayGridLines

I have, as your example dictates, declared oWin As Window.

[DKenny]

Are you running this code in Excel VBA or in VB?

If the answer is VB, it may be that 'Window' could represent a Windows Window. So, try changing the declaration to

Dim oWin As Excel.Window

It's running fine for me in VBA.

[Hack]

Are you running this code in Excel VBA or in VB?

If the answer is VB, it may be that 'Window' could represent a Windows Window. So, try changing the declaration to

Dim oWin As Excel.Window

It's running fine for me in VBA.

I should have stipulated that I'm using VB6 (I have never developed an actual application in VBA). I'll try that.

Thanks.

[Hack]

Excel.Window did the trick.

Now, I have Q1, Q2 and Q3 working.

Still mucking about with Q4, but I suspect that will just take a little playing around. I'm not getting any errors, but I'm also not having anything displayed.

Stay tuned whilst I play...

[DKenny]

Hack
For Q4 all I did was pass the row number to a variable. What do you need to achieve with that row?

[Hack]

Hack
For Q4 all I did was pass the row number to a variable. What do you need to achieve with that row?

The last two rows on my recordset contain dollar amounts. Via SQL, when I create my recordset, I SUM the two fields that hold the dollar amouts for a total dollar amount for each of those two columns. (Not that is has any bearing, but they are estimated vs actual dollar amounts for a budgeting program that I'm working on).

What I need to do is calculate two rows below the last row of my recordset and insert those totals there (I have the totals stored in Long variables). The recordset will always contain the same 6 data elements, but the number or records will vary depending on what the user selects as query criteria when my SQL SELECT is run.

The estimate and actual amounts and totals will always be in column's E and F, but the row that the totals will be displayed on will vary depending on the number of records in my recordset.

[DKenny]

Here's the revised code, you will need to ammend this to refer to your variable names.

Sub HackFormat()
Dim oWin As Excel.Window
Dim lTotalRow As Long
    
    With ThisWorkbook.Worksheets(1)
        
        'Q1 Left Align Column A
        .Columns(1).HorizontalAlignment = xlLeft
        
        'Q2 Set Orintation to Landscape
        .PageSetup.Orientation = xlLandscape
        
        'Q4 Find Last Used Row + 2 as Row number for Totals
        lTotalRow = .Range("A1").End(xlDown).Row + 2
            
        'Insert Totals
        .Cells(lTotalRow, 5).Value = lYourTotVariableForThe5thDataElement
        .Cells(lTotalRow, 6).Value = lYourTotVariableForThe6thDataElement
    End With
    
    'Q3 Turn off gridlines
    For Each oWin In ThisWorkbook.Windows
        oWin.DisplayGridlines = False
    Next oWin
 
End Sub

[Hack]

I get an "Application defined or object defined error" on

bkWorkBook.Worksheets(1).Cells(lTotalRow, 5).Value = lngEstAmt

bkWorkBook is declared as WorkBook

[Hack]

Also, I'm using this line of code

shWorkSheet.Columns("A:BZ").AutoFit

to make sure everything displays properly.

It works just fine for everything except one recordset element, and that is the one that contains the Comments field. Comments is stored in a varchar field in my SQL Server 2000 database, and I need to display them on this particular report. However, when I do display them, the entire comments field is strung out in one, long, continuous string. I suspect it is because of the AutoFit thing.

How can I display the Comments (which could be a couple or more paragraphs in length) as a block of text rather than one, long, continuous string?

[DKenny]

For the Comments column, after you use the autofit method for the entire range, you should set the WrapText property and then set the column width to your desired value.

I would include all this within a WITH block for the worksheet as above.

With .Columns(10) 'Change to the Col num for you Comments field
            .WrapText = True
            .ColumnWidth = 12 'Change to the desired Width
        End With

On the other issue, can you run to the error and see if lTotalRow has a value?

[Hack]

On the other issue, can you run to the error and see if lTotalRow has a value?

I'll play with the comments things later. This is more important.

After reading your question I looked back at what I had and couldn't see anywhere that it was getting any kind of value (other than zero of course) and after stepping through the code, I discover this is the case.

When it hits the .Cells(Cells(lTotalRow, 5).Value = lngEstAmt, lTotalRow = 0

[DKenny]

In my original code I had the following line

'Q4 Find Last Used Row + 2 as Row number for Totals
        lTotalRow = .Range("A1").End(xlDown).Row + 2

This line finds the last used row in Column A and the moves two rows down. I am assuming that your RS has a value for each record in the first field.


EDIT: I'm still thinking in VBA, you may need to add a declaration for xlDown

Const xlDown as Long = -4121

[si_the_geek]

If the sheet is newly created, you can set it using the UsedRange (it apparently tends to have issues, which is why the "xlDown" version is often better), eg:

lTotalRow = .UsedRange.Rows.Count

EDIT: I'm still thinking in VBA, you may need to add a declaration for xlDown

If a reference has been set, no constant declarations are required (if there is no reference, MS has a VB module with all the constant declarations in).

[Hack]

In my original code I had the following line

'Q4 Find Last Used Row + 2 as Row number for Totals
        lTotalRow = .Range("A1").End(xlDown).Row + 2

This line finds the last used row in Column A and the moves two rows down. I am assuming that your RS has a value for each record in the first field.


EDIT: I'm still thinking in VBA, you may need to add a declaration for xlDown

Const xlDown as Long = -4121

Nope. Your VBA code worked just fine. For reasons I can't remember, that line was commented out. The only thing I can think of is that I commented it out because something wasn't working, and then when the "something" got fixed, I forgot to uncomment it.

Anyway, with that line running, it works just fine. I just ran it through 6 or 7 different tests selecting different things each time so my query would produce recordsets of varying lengths and no matter how many, or how few, records were returned, the totals were always two lines below that last one.

Yes! *dances*

Now, I'm going to play around with my comments thing, but the original 4 questions are now resolved. Thank you sir!

[DKenny]

Just glad I could help.
Now, don't forget to mark the thread as resolved, Moe

[Hack]

For the Comments column, after you use the autofit method for the entire range, you should set the WrapText property and then set the column width to your desired value.

I would include all this within a WITH block for the worksheet as above.

With .Columns(10) 'Change to the Col num for you Comments field
            .WrapText = True
            .ColumnWidth = 12 'Change to the desired Width
        End With

On the other issue, can you run to the error and see if lTotalRow has a value?

Are these properties of the Workbook object or the Worksheet object? (I still haven't sorted out what properties belong to what object yet, so until I get comfortable with that, I'm staying away from With/End With.)

[DKenny]

Columns is a property of the Worksheet object and it returns a collection.

[Hack]

Columns is a property of the Worksheet object and it returns a collection.

When I do a

bkWorkBook.Worksheets(1).Columns (12)

I'm told the "Object does not support this property or method."

[DKenny]

You are referencing the 12th member of the columns collection, but you are not doing anything with it (i.e. you are not calling a method or referencing a property of the column).
Try

bkWorkBook.Worksheets(1).Columns(12).WrapText = True
bkWorkBook.Worksheets(1).Columns(12).ColumnWidth = 12 'Change to the desired Width

[Hack]

You are referencing the 12th member of the columns collection, but you are not doing anything with it (i.e. you are not calling a method or referencing a property of the column).
Try

bkWorkBook.Worksheets(1).Columns(12).WrapText = True
bkWorkBook.Worksheets(1).Columns(12).ColumnWidth = 12 'Change to the desired Width

Geez oh boy...now I know how the noobs feel in ClassicVb.

[Hack]

I am cocked, locked and ready to rock.

Thank you sir!