Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Posts
    102
    Provided Answers: 6

    Unhappy Answered: Autofit Excel Columns

    Hi all

    I have a DB that creates a blank Excel spreadsheet and opens it for user input. It would be nice if I could get the columns to autofit the column headers (never understood why this isn't a default action in Excel anyway).

    Below is the code I am using to create and open the Excel spreadsheet.. Does anybody know what code I need to use to have the columns autofit the column titles? Thanks in advance.

    Code:
    Public Sub cmdCreateExcelFile_Click()
        Dim XL As Excel.Application, WB As Excel.Workbook, WKS As Excel.Worksheet
        Dim db As DAO.Database, rec As DAO.Recordset, f As DAO.Field
        Dim i As Integer, j As Integer
    
        ' Prepare Excel stuff
        Set XL = New Excel.Application
        XL.Visible = True
        Set WB = XL.Workbooks.Add
        WB.Activate
        Set WKS = WB.ActiveSheet ' Default: The first sheet in the newly created book
    
        ' Read data here
        Set db = CurrentDb()
        Set rec = db.OpenRecordset("_ImportFromExcel")
    
        ' A simple table that will show the data from rec
        ' i and j will be the coordiantes of the active cell in the worksheet
        With rec
            '.MoveFirst
    
            ' The table headers
            i = 1
            j = 1
            For Each f In .Fields
                WKS.Cells(i, j).Value = f.Name
                j = j + 1
            Next f
        End With
    
    End Sub

  2. Best Answer
    Posted by weejas

    "
    Code:
    WKS.Columns("A:A").EntireColumn.Autofit
    Change the string value to the column(s) that you want to resize. Repeat for non-contiguous sets of columns."


  3. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    Code:
    WKS.Columns("A:A").EntireColumn.Autofit
    Change the string value to the column(s) that you want to resize. Repeat for non-contiguous sets of columns.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  4. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    805
    Provided Answers: 2
    Hi
    Not sure how you have implemented the previouse solution, but this is a little more staright forword, IMHO.
    Code:
        With rec
            '.MoveFirst
    
            ' The table headers
            i = 1
            j = 1
            For Each f In .Fields
                WKS.Cells(i, j).Value = f.Name
                WKS.Columns(j).EntireColumn.AutoFit
                j = j + 1
            Next f
        End With
    MTB

  5. #4
    Join Date
    Sep 2010
    Posts
    102
    Provided Answers: 6
    Hi Mike

    Thanks for the input. This is how I implemented Weejas' solution. Which works just fine.
    Code:
        With rec
            '.MoveFirst
    
            ' The table headers
            i = 1
            j = 1
            For Each f In .Fields
                WKS.Cells(i, j).Value = f.Name
                j = j + 1
            Next f
            ' Autofit Column widths
            WKS.Columns("A:M").EntireColumn.AutoFit
        End With
    
    End Sub
    However I see the benefit of your solution in that I don't need to absolutely define the number of columns. The loop handle that.

    Always a couple of ways to skin a cat.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •