VBA Adding a Table Name

[Mike_VBA]

Hi All,

This is my code:

With Sheets("XYZ").ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=SQLOLEDB.1;Password=XYZ@1;Persist Security Info=True;User ID=XYZ;Data Source=XYZ;Use Procedure for Prepare=1;" _
        , _
        "Auto Translate=True;Packet Size=4096;Workstation ID=MKUMAR72;Use Encryption for Data=False;Tag with column collation when possib" _
        , "le=False;TableStyleName=TableStyleMedium8;Initial Catalog=ecompass"), Destination:=Sheets("XYZ").Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM XYZ WHERE M='XYZ' AND Months = '" & month & "' ORDER BY Name")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = True
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = _
        "snapa.odc"
        .Refresh BackgroundQuery:=True
        End With

For this, excel keeps adding a random table name like :

Table_ExternalData_152
Table_ExternalData_153
Table_ExternalData_154

Is there a way I can name it myself ? Thanks!

 

[Yard]

Try using a construction like

Set MyTable=Sheets("XYZ").ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=SQLOLEDB.1;Password=XYZ@1;Persist Security Info=True;User ID=XYZ;Data Source=XYZ;Use Procedure for Prepare=1;" _
        , _
        "Auto Translate=True;Packet Size=4096;Workstation ID=MKUMAR72;Use Encryption for Data=False;Tag with column collation when possib" _
        , "le=False;TableStyleName=TableStyleMedium8;Initial Catalog=ecompass"), Destination:=Sheets("XYZ").Range("$A$1")).QueryTable
 
With MyTable
     .Name="MyNameForThisTable"
     .CommandType.......................................

 

[Mike_VBA]

Thanks a lot Yard. The one that worked out for me is :

Using Listobjects.Listobject.name = "XYZwhatever"

Also using Listobjects.Listobject.name = "Tablestylelight8" helps out for themes that Excel has bundled in..

Try using a construction like

Set MyTable=Sheets("XYZ").ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=SQLOLEDB.1;Password=XYZ@1;Persist Security Info=True;User ID=XYZ;Data Source=XYZ;Use Procedure for Prepare=1;" _
        , _
        "Auto Translate=True;Packet Size=4096;Workstation ID=MKUMAR72;Use Encryption for Data=False;Tag with column collation when possib" _
        , "le=False;TableStyleName=TableStyleMedium8;Initial Catalog=ecompass"), Destination:=Sheets("XYZ").Range("$A$1")).QueryTable
 
With MyTable
     .Name="MyNameForThisTable"
     .CommandType.......................................