• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1397
  • Last Modified:

Excel Row Height Autofit

I have a Access (2007)  table that I export to Excel(2007) .I want to number the records in  column A to the left of each records put their number (1,2,3,4,etc) and also been trying to autofit the row height unsuccessful, what is the method for Excel 2007 VBA? I wrap the text in column F to with= 60, and I want to auto fit to the high of that column text.
Code used below.
Thanks in advance for the input

With Sheet
    iRow=2
Do Until rs.EOF
    
        .Columns("F:F").ColumnWidth = 60
        .Range("F:F").WrapText = True
        .Columns("A:F").EntireColumn.AutoFit
        .Rows("1:1000").EntireRow.AutoFit
        .Columns("A:I").Font.Name = Calibri
        .Columns("A:I").Font.Size = 8
        .Columns("D:D").NumberFormat = "mm-dd-yy"
        .Column("A").Count
        .Cells(iRow, 1).Value = rs("No")
        .Cells(iRow, 2).Value = rs("Unit")
        .Cells(iRow, 3).Value = rs("Model")
	iRow=iRow+1
	rs.moveNext
Loop
End With

Open in new window

0
Exl04
Asked:
Exl04
  • 9
  • 8
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
your codes always call the formatting at every record read.
* populate first your excel sheet with values
* do the formatting separately after the sheet was populated
  -place your formatting codes outside the loop


what exactly is your problem?
0
 
Exl04Author Commented:
Can't format Row height, even when formatting code is outside loop
0
 
Rey Obrero (Capricorn1)Commented:
where are you running the codes?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
BadotzCommented:
No points, please.

Code like this:


With Sheet
    iRow=2
    Do Until rs.EOF
    
        .Cells(iRow, 1).Value = rs("No")
        .Cells(iRow, 2).Value = rs("Unit")
        .Cells(iRow, 3).Value = rs("Model")
	iRow=iRow+1
	rs.moveNext
    
    Loop
    
    .Columns("F:F").ColumnWidth = 60
    .Range("F:F").WrapText = True
    .Columns("A:F").EntireColumn.AutoFit
    .Rows("1:1000").EntireRow.AutoFit
    .Columns("A:I").Font.Name = Calibri
    .Columns("A:I").Font.Size = 8
    .Columns("D:D").NumberFormat = "mm-dd-yy"
    .Column("A").Count

End With

Open in new window

0
 
Exl04Author Commented:
I’m running the code from an Access Form command button.
Badotz: When use you code it breaks in Column(“A”).Count,… so still can check row height
0
 
Exl04Author Commented:
Error message said “Object doesn’t support this properly or method”
0
 
Rey Obrero (Capricorn1)Commented:
upload a .mdb version of the db
0
 
Rey Obrero (Capricorn1)Commented:
or post the whole codes
0
 
BadotzCommented:
>>When use you code it breaks in Column(“A”).Count

So move it out of the loop.
0
 
Exl04Author Commented:
Here is code
Private Sub Command0_Click()

    sAppPath = "MyDirectory”
    sDate = Replace(FormatDateTime(Now(), vbShortDate), "/", ".")
    sFileName = sAppPath & "Dbs_Issues As " & sDate & ".xlsx"

    Dim rs As DAO.Recordset, iCol As Integer, iRow As Integer
    Dim xlObj As Object, Sheet As Object
    DoCmd.OpenQuery "issuesAllConcApp_Q"
    Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Add

    Set rs = CurrentDb.OpenRecordset("ConcatIssues_T")

    Set Sheet = xlObj.ActiveWorkbook.Worksheets("Sheet1")

    For iCol = 0 To rs.Fields.Count - 1
        Sheet.Cells(1, iCol + 1).Value = rs.Fields(iCol).Name
    Next
    rs.MoveFirst


    With Sheet
        iRow = 2
        Do Until rs.EOF

            .Cells(iRow, 1).Value = rs("No")
            .Cells(iRow, 2).Value = rs("Unit")
            .Cells(iRow, 3).Value = rs("Model")
            .Cells(iRow, 4).Value = rs("field1")
            .Cells(iRow, 5).Value = rs("field2")
            .Cells(iRow, 6).Value = CStr(rs("Issues_Description") & "")
            .Cells(iRow, 7).Value = rs("field3")
            .Cells(iRow, 8).Value = CStr(rs("Issues_Comments") & "")
            .Cells(iRow, 9).Value = rs("field3")
            iRow = iRow + 1
            rs.MoveNext
        Loop
        
        .Columns("F:F").ColumnWidth = 60
        .Range("F:F").WrapText = True
        .Columns("A:F").EntireColumn.Autofit
        .Range("A1:E1").Rows.Autofit
        .Columns("A:I").Font.Name = Calibri
        .Columns("A:I").Font.Size = 8
        .Columns("D:D").NumberFormat = "mm-dd-yy"

    End With

    xlObj.ActiveWorkbook.saveas FileName:=sFileName, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=True

    Set Sheet = Nothing
    xlObj.Quit
    Set xlObj = Nothing


End Sub

Open in new window

0
 
Exl04Author Commented:
I need to number the records per row (1,2,3,4,etc) and autofit rows
0
 
Rey Obrero (Capricorn1)Commented:
<I need to number the records per row (1,2,3,4,etc) and autofit rows>

in what column do you want the numbering ?
if in column A, start the column to 2

dim rowCnt as integer
rowCnt=1

        Do Until rs.EOF


        .Cells(iRow, 1).Value = rowCnt     '< Row counter

        .Cells(iRow, 2).Value = rs("No")
 




            rowCnt=rowCnt + 1

            iRow = iRow + 1
            rs.MoveNext
        Loop




0
 
Exl04Author Commented:
Capricorn1 thanks for your advice...
I may not explained correctly; code already accomplish the green area (see pic), all need is enumerate the records (yellow area) when you said start column in 2 I don’t follow you. Also when I set width in column width F then when populate I need rows to auto fit height of text
I need to modify the code already have to enumerate the rows staring in row 2 and autofit all populated rows height.

sheet.bmp
0
 
Rey Obrero (Capricorn1)Commented:
can you upload a copy of the db.. need to see some data(could be the reason for not allowing you to set the rowheight properly, replaced sensitive info with dummy)


0
 
Rey Obrero (Capricorn1)Commented:
..also, do the columns and rows autofit as the last codes to be executed after formatting your data



        .Columns("A:I").Font.Name = Calibri
        .Columns("A:I").Font.Size = 8
        .Columns("D:D").NumberFormat = "mm-dd-yy"

        .Columns("F:F").ColumnWidth = 60  ' < might not be needed
        .Range("F:F").WrapText = True
        .Columns("A:F").EntireColumn.Autofit
        .Range("A1:E1").Rows.Autofit
0
 
Exl04Author Commented:
Colunm F is the main one I need it to be = 60. This column may have lots for text so I got them as Memo in the table and that’s the reason I want to set I tto =60 and I want the row to auto fit the height of the amount of text .
Attached dummyFile
Export.mdb
0
 
Rey Obrero (Capricorn1)Commented:
try this
ExportRev.mdb
0
 
Exl04Author Commented:
That did it, thanks!..
Do you know why even that we set up         .Range("A1:E1").Rows.Autofit I can still see blank space above some cell text or when I have lots of text in these fields some of this text gets hidden at the bottom of cell? Do I need to set a minimum too or what is the trick to make every row auto fit, or better yet is AutoFit the right way to go, is there any other property of Row it be use to accommodate (make all text visible and fit in a columns cells?
0
 
Exl04Author Commented:
Thanks for the solution!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 9
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now