Excel 2010 VBA Tables and ListView

[JasonEnsor]

Hi Guys,

I am currently building an SQL generator that will spit out .Sql files based upon selections made in an excel document. I am using Excel as it is a great storage container for the changing values. In my first prototype i had multiple worksheets setup where my vba would call a particular worksheet to get the relevant data. Eg. I have a Profile Options worksheet, Customer Options...The number of Excel worksheets were becoming a bit excessive, so i have started to redesign the system. This time i thought about using tables as they can be resized and it means multiple tables can be used on one worksheet. this means less worksheets in the workbook.

Previously i was using the following to loop through a worksheet and populate a ListView control.

Sub LoadProfileOptionsList()
        
    Dim Item As Range
    Dim currentRow As Long
    
    profOpsLR = LastRow(ws_ProfileOptions)
     
    frmConfigurationBuilder.lstProfileOptions.ListItems.Clear
    For Each Item In ws_ProfileOptions.Range("A2:A" & profOpsLR)
        
        If ws_ProfileOptions.Range("A2").value = "" Then
        
        Else
        With frmConfigurationBuilder.lstProfileOptions

            .ListItems.Add 1, , ws_ProfileOptions.Cells(Item.Row, 1)
            .ListItems(1).ListSubItems.Add 1, , ws_ProfileOptions.Cells(Item.Row, 2)
        End With
        End If
    Next Item
    
End Sub

I am wanting to do this now using a table but seem to be struggling. I have tried the following

Sub PopulateProfileOptionsList()
    
    Dim dataList As Worksheet
    
    Set dataList = ThisWorkbook.Sheets("DataLists")
    
    With frmGenerator.lstProfileOptions
       
        .ListItems.Add 1, , dataList.Range("ProfileOptions").Cells(1, 2)
        .ListItems(1).ListSubItems.Add 1, , dataList.Range("ProfileOptions").Cells(1, 2)
    End With

End Sub

However no data is showing up on the ListView control. Ideally i would like the code to loop through and populate all enteries in the table. Once i get that sorted i can replicate the code for my other ListViews.Then move on to resizing the tables as needed.

I can give more information if needed

Many Thanks in advance guys

J.

Regards

J.

 

[SkipVought]

Hi,

How are your tables laid out on the sheet? Please be specific.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[JasonEnsor]

Hi Skip,

Sorry for the late reply, i got pulled on to another task breifly. I have several tables on one sheet, the ProfileOptions table for example has two columns The header Row is Column "S2" and "S3", i also have a single column Table called Alias whose header Row is "V2". I will be adding more tables as time goes on, and data will be added to each table and removed from the table depending on the client that is being set up. I chose to use tables as i thought they would be a better option. Well untill i tried to get the values in to a ListView.

Regards

J.

Regards

J.

 

[SkipVought]

The header Row is Column "S2" and "S3"

Can you explain that? Does not make any sense.

Also whats going on in columns A-R?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[JasonEnsor]

Hi Skip,

When creating a table in Excel you give each column of the table a name. So that is the header Row. in my example i have Cell S2 and Cell T2 as the header row, then the data starts on S3 and T3
I am using columns A to S for other options that are needed for the file.

Appologies, i realise i made a typo and it should have read S2 and T2

Regards

J.

Regards

J.

 

[SkipVought]

Ok A-R is options. s&t are first table, u separator, v a list.

Whats in row 1 above your tables?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[JasonEnsor]

Hi Skip,

Row 1 above the tables is used just to give a title to the tables.

Yeah A-R is options, S and T is the first Table, U is blank column and V is setup as a table (although it is only one column)


J.

Regards

J.

 

[SkipVought]

Are you using Structured Tables?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[JasonEnsor]

Hiya,

Yeah its a structured table, created by using insert table and selecting my table has a header.

Regards

J.

 

[JasonEnsor]

Hiya,

Yeah I am. Inserted the table by selecting insert table and ticking my table has headers

Regards

J.

 

[SkipVought]

Dim r as range

For each r in [TableName[FieldName]]

   Debug.Print intersect(r.entirerow, [TableName[OtherFieldName]]).value

Next

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[JasonEnsor]

Cheers Skip

Regards

J.