Results 1 to 4 of 4

Excel Automation Autofit Column (or Cell?) Width

  1. #1
    orcinus is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    63

    Excel Automation Autofit Column (or Cell?) Width

    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

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,205
    Does this work?

    .Columns.AutoFit
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  3. #3
    orcinus is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    63
    Quote Originally Posted by pbaldy View Post
    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..

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,205
    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.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Exporting to Excel with Automation using QueryDef
    By Niezels in forum Import/Export Data
    Replies: 3
    Last Post: 09-26-2010, 04:55 PM
  2. Export a value to specific Excel cell
    By gg80 in forum Import/Export Data
    Replies: 5
    Last Post: 07-23-2010, 12:58 PM
  3. Import single cell from excel
    By zippy483 in forum Import/Export Data
    Replies: 9
    Last Post: 02-24-2010, 12:16 PM
  4. Replies: 0
    Last Post: 12-25-2008, 08:05 AM
  5. Can we post Access cell data to Excel cell properties?
    By Zethro in forum Import/Export Data
    Replies: 1
    Last Post: 12-13-2005, 06:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums