Excel Automation Autofit Column (or Cell?) Width

[orcinus]

I have some code as below that I would like to autofit the column to the largest value in the populated cells (A1:G6). However, the code I have does not exactly work like that (see bolded code below).

What this piece does do is autofit the column headers which are in the A1:G1 range! I, however, want the column width to fit the largest value in whatever cell it occurs in the A1:G6 range..

I tried changing ".Columns" to .Cell with basically the rest of the code being the same and still no dice.

I also tried it with and without the .Columns.Range("A1:G6").Select.

Seems like this should be a simple fix..Thanks for any suggestions..!



Private Function fcnExport()
Dim automApp As Excel.Application
Dim xlWksht As Excel.Worksheet
Dim xlWkbook As Excel.Workbook
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Dim strPath As String
Dim strFP As String 'file path
Dim strFN As String 'file rpt name
Dim strDT As String 'file name date tag
Dim strFE As String 'file extention
Dim lngRecCount As Long
Dim iCols As Integer
Set db = CurrentDb
Set automApp = CreateObject("Excel.Application")
'strPath = CurrentProject.Path
strFP = "c:\6481\"
strFN = "5753_Monthly_IFP_Billing_"


strDT = Format(Date, "yyyymm")
strFE = ".xls"
strPath = strFP & strFN & strDT & strFE
strSQL = "Select * from qry_output_Metric_Final"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
With rs
.MoveLast
lngRecCount = .RecordCount
.MoveFirst
End With
With automApp
.Workbooks.Add
.DisplayAlerts = False
.Visible = True

For iCols = 0 To rs.Fields.Count - 1
.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name 'changed ".cells(2, icols + 1)" from 2 to 1
Next

.Cells.Range("A1:G1").Font.Bold = True
.Columns.Range("A:G").HorizontalAlignment = xlCenter
.Columns.Range("A1:G6").Select
.Columns.Range("A1:G6").EntireColumn.AutoFit
.Range("A2").CopyFromRecordset rs
.ActiveWorkbook.SaveAs FileName:=strPath
End With
End Function

[pbaldy]

Does this work?

.Columns.AutoFit

[orcinus]

Does this work?

.Columns.AutoFit

ok, after trying this in a multitude of ways, I realized it was not a syntax issue, but a logical issue. Why was it just autofitting the headers and not the using the rest of the data to get the largest autofit?

My code has the headers being pasted and then the autofit and THEN the data is pasted from Access. So the autofit needs to come after the data has been pasted!

It works now..thanks..

[pbaldy]

I should have pointed that out. I noticed it, but when you said

"I, however, want the column width to fit the largest value in whatever cell it occurs in the A1:G6 range"

I thought you wanted the autofit limited to what was in that range. Should have mentioned it anyway, so sorry for that. Glad you got it sorted out.