asked on 5/22/2012

Create a table with VBA and name it

Is there a way with VBA in Excel to programically create a table and name is? I am pulling in data from an ACCESS 2003 database, placing it in a worksheet called DataSource, but I also need to then create a table of the data and be sure its name is always DataSource. I tried recording a macro, but naturally it first names is an auto-generated name, but is there a way around this? This is Excel 2010 VBA.

Sandra

Microsoft Development Microsoft Applications Microsoft Excel

Last Comment

dlmille

5/24/2012

dlmille

5/22/2012

Do you have a sample, sanitized workbook to work from?

Dave

Sandra Smith

5/22/2012

ASKER

actually Dave, I got a version to work and was just going to delete this question.

Sandra

ASKER CERTIFIED SOLUTION

dlmille

5/22/2012

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Sandra Smith

5/24/2012

ASKER

Very similar.

Sandra

Sandra Smith

5/24/2012

ASKER

Actually, taking a look at your version, it was a bit simplier than what I had so did use it. I was using naming a range, but I like the ListObjects add method better than what I did.

Sandra

dlmille

5/24/2012

I would suggest unlisting any table that might exist, prior to making a new one - assuming you're refreshing your data and the table stub of the object is still there:

Sub makeTable()
Dim wkb As Workbook
Dim wks As Worksheet
Dim rng As Range
Dim r As Range
Dim lastCol As Long
Dim lastRow As Long

    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("DataSource")
    
    lastCol = wks.Cells(1, wks.Columns.Count).End(xlToLeft).Column
    lastRow = wks.Cells(wks.Rows.Count, 1).End(xlUp).Row
    
    'get range of table
    Set rng = wks.Range("A1", wks.Cells(lastRow, lastCol))
    
    'convert to table list object
    On Error Resume Next
    wks.ListObjects("Table1").Unlist
    On Error GoTo 0
    wks.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "Table1"
End Sub

Open in new window

Cheers,

Dave