Hi team,
First, Happy new year and best wishes to all.
Even progressing in VBA I struggle for long time with the following macro.
The aim is to read the width of all column of a table, and write them in a table (to be reuse is need to set back the width to what has been previously saved)
Currently I am able to read the width of each column but not the name of the colum,
Public Sub RPL_SaveColumnWidths1()
' Mise en forme pour la vue Ready RPL
Dim tRPL As New MGE_Table 'Table of which I read the column width
Dim tCOL As New MGE_Table 'Table to fill with 3 values per column (Name, Witdh HeaderName) ex A, 10.5, Customer
Dim iCol
' Lecture de la table
tRPL.GetTable "DRB"
tCOL.GetTable "ColWidth1"
' Preparation de la table
tCOL().ClearContents
' Lecture des Colonnes
iCol = tRPL.ColCount - tCOL.RowCount
If iCol > 0 Then tCOL.AddRow iCol
For iCol = 1 To tRPL.ColCount
With tRPL(iCol, 1)
tCOL(1, iCol) = Split(.Address, "$")(1)
tCOL(2, iCol) = .ColumnWidth
' tCOL(3, iCol) = ("[#Headers]") ' It is here .... where I need help
End With
Next
End Sub
About the "GetTable" ClassModule, it has been develop by the man who was working before me and was a real Dev
Public Sub GetTable(sTableName, Optional oWb = Nothing)
' Associates this instance of MGE_Table class with the existing Excel table named sTableName in Workbook oWb (default: ActiveWorkbook).
If oWb Is Nothing Then Set oWb = ActiveWorkbook
m_sTableRef = Replace(Replace(m_sTableRefPattern _
, "WB", oWb.Name) _
, "TBL", sTableName)
Set m_oList = Nothing
On Error Resume Next
Set m_oList = Range(m_sTableRef).Parent.ListObjects(sTableName)
End Sub
Bookmarks