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.

Sandra
Microsoft DevelopmentMicrosoft ApplicationsMicrosoft Excel

Avatar of undefined
Last Comment
dlmille

8/22/2022 - Mon
dlmille

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

Dave
Sandra Smith

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

Sandra
ASKER CERTIFIED SOLUTION
dlmille

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
or
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

ASKER
Very similar.

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

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

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