Resizing column to best fit vba
Resizing column to best fit vba
(OP)
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!
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!
RE: Resizing column to best fit vba
But if you want to do that, you might could do something to this extent... you'd have to fill in the details:
CODE
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
RE: Resizing column to best fit vba
RE: Resizing column to best fit vba
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
RE: Resizing column to best fit vba
RE: Resizing column to best fit vba
--
"If to err is human, then I must be some kind of human!" -Me
RE: Resizing column to best fit vba
RE: Resizing column to best fit vba
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
RE: Resizing column to best fit vba
RE: Resizing column to best fit vba
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
RE: Resizing column to best fit vba
http
RE: Resizing column to best fit vba
CODE
(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