How to make a table from a cdataset

From Excel 2003, you could use Tables. A cDataSet is somewhat like a table in that is structured data, expecting headings etc. When you use bigCommit() in cDataSet, it wipes out the existing Excel data and replaces it with the values you have changed. This has the side effect of eliminating any Excel table that may have been mapped to that data.

Here's an example of how to have cDataSet restore a table definition, or create a new one if necessary

Private Function testListObject()
    Dim o As ListObject, ds As cDataSet, sName As String
    Set ds = New cDataSet
    With ds.populateData(wholeSheet("vbaparsedata"), , , , , , True)
        ' this will record if any list object  currently exists overlapping this dataset
        Set o = .activeListObject
        ' keep the same name
        If Not o Is Nothing Then sName = o.name
        ' do a bigcommit
        .bigCommit
        ' did that delete it?
        Debug.Print .activeListObject Is Nothing
        ' reset to new size
        .makeListObject sName
        ' whats the dimension?
        Debug.Print .activeListObject.Range.Address
        .tearDown
    End With
End Function

Summary


.activeListObject() can be used to recover any tables that currently overlap a cDataSet.
.makeListObject(some name) can be used to create an Excel table with the characteristics of the cDataset

You can get me on Google plus, Twitter or this forum.


For help and more information join our forum,follow the blog or follow me on twitter .


Shop Amazon Gift Cards - Perfect Gifts Anytime

Comments