Resizing column to best fit vba

[vvilan]

Hi, I create the table in access by importing from a text document via this code:

DoCmd.TransferText acImportDelim, "Import Specs", fdr, CurrentProject.path & "\" & fdr & ".csv", True

How do I auto resize the columns to best fit such that the data from every cell is displayed fully. Thanks!

 

[kjv1611]

I would wonder why you would bother with doing that anyway, as surely your table is not the way you are viewing the data, or allowing the data to be viewed on a regular basis.

But if you want to do that, you might could do something to this extent... you'd have to fill in the details:

Private Sub Import()
  Dim db as DAO.Database
  Dim tdf as TableDef
  Dim x as Integer 'Count fields
  Dim y as Integer 'Counter to loop through fields
  Set db = CurrentDb

  DoCmd.TransferText acImportDelim, "Import Specs", fdr, CurrentProject.path & "\" & fdr & ".csv", True
  
  Set tdf = db.Tabledefs(fdr)
  
  'get the count of the fields
  'Something like:
  'x = tdf.Fields.Count - but I don't remember for sure
  
  For y = 1 to x
    db.ModifyTable(tdf)
    tdf.Fields(y).Width = MaxLengthOfAnyRecordWithinField
    tdf.Update
  Next y
  
  Set tdf = Nothing
  db.close
  Set db = Nothing
End Sub

As you can see, lots to fill in, and probably some of it is not 100% correct on terminology, as I just typed here... didn't test in VBA window.

--

"If to err is human, then I must be some kind of human!" -Me

 

[vvilan]

Thanks for the quick reply but I'm a new user who does not understand the syntax of that code at all. I c/p into my code and it doesn't work.

 

[kjv1611]

Well, my advice is to just not worry about it. You shouldn't have to resize the columns, as you shouldn't be using the table as the way you look at the data in most situations, anyway.

Create a customizable user form instead, that'll work much better.

--

"If to err is human, then I must be some kind of human!" -Me

 

[vvilan]

Well to view data, I use the form to put the data and the data executes a query from the table. If the table's columns aren't resized, the data will not show completely and the user will have to resize manually. I just wanna get around this problem.

 

[kjv1611]

So, you're saying you view the data in the form, correct? Otherwise, I'm not 100% sure I follow exactly what you mean.

--

"If to err is human, then I must be some kind of human!" -Me

 

[vvilan]

The user inputs are taken from a form and then the form executes a query based on the input which selects data from the table and the data is displayed with the table's column width.

 

[kjv1611]

So the form executes a query is what your saying...

A user opens the form, selects/types some data, then clicks a button that opens a query that is based on the inputs.

You could just open the query in a subform, or a separate form, instead of directly in the query, as well, so that you could have more control over the appearance as well.

--

"If to err is human, then I must be some kind of human!" -Me

 

[vvilan]

Okay, let's say I want to execute a query named query1. How would I do that in a subform?

 

[kjv1611]

You create a subform, say on the bottom of your main form. Then set the subform's data source to your query as a start. Then, you customize your subform to appear in whatever way you want/need.

If the query is always a brand new query, and not an existing query, in other words a query is actually created each time, and not just opened, then you'll need to change the data source each time using code.

--

"If to err is human, then I must be some kind of human!" -Me

 

[MajP]

if you really need to:

http://msdn.microsoft.com/en-us/library/aa217449(office.11).aspx

 

[MajP]

I played with this code and works well, here is the relevant code so you do not have to dig through the article.

Resizing columns

The ability to resize columns programatically to display the data currently in the column is the original reason I wanted to write this article: It's just an amazing feature. The first place I ever saw it was in Access's Normalizer, better known as the Access Table Analyzer (the names of all of the wizard's objects are still prefixed with "NORM_" because the marketing change to "Table Analyzer" didn't force any code changes!). The Normalizer creates a query using DAO, and then it sets the columns of the resulting datasheet to be the appropriate width to display the data in the columns. And remember, tables and queries in Access are displayed using datasheets (at least, when you have them in browse view), which are forms.

The trick to resizing columns automatically to the data currently in them requires two steps:

1. You must set the datasheet's ColumnWidth properties to -2, a number that seems to mean "best fit" to Access, internally. Access will then change the ColumnWidth property value to an appropriate number (in twips).
2. To make the change permanent, you have to add a property called ColumnWidth to DAO's Property collection (which won't contain the property by default for all queries) and set the property's value to the same value as the control's ColumnWidth property from Step 1.

To do this, you can use my procedures FixColumnWidthsOfQuery or FixColumnWidthsOfTable and their helpful subroutine, SetDAOFieldProperty:

Public Function FixColumnWidthsOfQuery _
               (stName As String)
    Dim db As Database
    Dim qdf As QueryDef
    Dim fld As DAO.Field
    Dim frm As Form
    Dim ictl As Integer
    Dim ctl As Control
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs(stName)
    DoCmd.OpenQuery stName, acViewNormal
    Set frm = Screen.ActiveDatasheet
    For ictl = 0 To frm.Controls.Count - 1
     Set ctl = frm.Controls(ictl)
     ctl.ColumnWidth = -2
     Call SetDAOFieldProperty(qdf.Fields(ictl), _
      "ColumnWidth", ctl.ColumnWidth, dbInteger)
    Next ictl
    DoCmd.Save acQuery, stName
End Function

Public Function FixColumnWidthsOfTable _
                      (stName As String)
    Dim db As Database
    Dim tdf As TableDef
    Dim fld As DAO.Field
    Dim frm As Form
    Dim ictl As Integer
    Dim ctl As Control
    
    Set db = CurrentDb
    Set tdf = db.TableDefs(stName)
    DoCmd.OpenTable stName, acViewNormal
    Set frm = Screen.ActiveDatasheet
    For ictl = 0 To frm.Controls.Count - 1
     Set ctl = frm.Controls(ictl)
     ctl.ColumnWidth = -2
     Call SetDAOFieldProperty(tdf.Fields(ictl), _
      "ColumnWidth", ctl.ColumnWidth, dbInteger)
    Next ictl
    DoCmd.Save acTable, stName
End Function

Private Sub SetDAOFieldProperty _
               (fld As DAO.Field, _
 stName As String, vValue As Variant, _
               lType As Long)
    Dim prp As DAO.Property
    
    For Each prp In fld.Properties
        If StrComp(prp.Name, stName, _
         vbBinaryCompare) = 0 Then
            prp.Value = vValue
            Exit For
        End If
        Set prp = Nothing
    Next prp
    
    If prp Is Nothing Then
        Set prp = fld.CreateProperty(stName, _
         lType, vValue)
        fld.Properties.Append prp
    End If
End Sub

You can simply pass the name of any table or query to these routines and let them do the rest!

If you need to do the same task with a Form, it's even easier: Just set all of the ColumnWidth properties to -2. No extra steps are needed