Avatar of Sandra Smith
Sandra Smith
Flag for United States of America asked on

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.

Microsoft DevelopmentMicrosoft ApplicationsMicrosoft Excel

Avatar of undefined
Last Comment

8/22/2022 - Mon

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

Sandra Smith

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


Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sandra Smith

Very similar.

Your help has saved me hundreds of hours of internet surfing.
Sandra Smith

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.


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
    On Error GoTo 0
    wks.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "Table1"
End Sub

Open in new window