Create a table with VBA and name it

Sandra Smith
Sandra Smith used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012

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

Dave
Sandra SmithRetired

Author

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

Sandra
Most Valuable Expert 2012
Top Expert 2012
Commented:
Ok - I assume your code was something like:

Option Explicit

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

Open in new window


Cheers,

Dave
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Sandra SmithRetired

Author

Commented:
Very similar.

Sandra
Sandra SmithRetired

Author

Commented:
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
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial