Excel vba using variable as the name of ListObjects

Question

Function update_avgcpu_data(Server_hostname)

Dim rpt_name As String
rpt_name = Server_hostname & "avgcpu"
MsgBox rpt_name
With ThisWorkbook.Sheets(Server_hostname).ListObjects.Add(SourceType:=0, Source:= _
    "ODBC;DSN=localtest;",Destination:=ThisWorkbook.Sheets(Server_hostname).Range("$A$1")).QueryTable
    .CommandText = "SELECT cpu_avg_statistics_0.LOGDATE as 'Date of Month', cpu_avg_statistics_0.CPU as 'CPU Utilization %' FROM test.cpu_avg_statistics cpu_avg_statistics_0 WHERE (cpu_avg_statistics_0.SERVER_NAME='" & Server_hostname & "') AND (cpu_avg_statistics_0.LOGDATE between '2012-02-01' and '2012-02-05') ORDER BY cpu_avg_statistics_0.LOGDATE"
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = rpt_name
    .Refresh BackgroundQuery:=False
    End With
    End Function

I use the above function to run the query, the error code is run-time error 1004 , application-defined or object-defined error.When press debug , it stopped at the line .ListObject.DisplayName = rpt_name. Please help

Totally uneducated guess, change ".ListObject.DisplayName" to ".DisplayName". — mkingston
– mkingston, Commented Mar 26, 2012 at 4:37
And if that isn't the problem, what line is the error happening on? — mischab1
– mischab1, Commented Mar 26, 2012 at 22:24

6mtg35 · Accepted Answer · 2014-01-24 19:32:03Z

I ran into the same problem when I used a macro to create essentially the same script you noted. After a hour or two of debugging, I came up with this:

Short answer is that you have an object already named rpt_name. Since you cannot have two objects with the same name excel throws an error.

Long answer I got this from the VBA help function: "If an attempt is made to set the Name property to a name already used by another ListObject object, a run-time error is thrown."

Longer answer for me my error was that I was creating a new table everytime I ran my macro and the macro tried to use the same name for the new table. Of course the first time I ran the macro it worked because it didn't have a duplicate name, but subsequent runs resulted in a crash because of the duplicate name (in my case a table name)

Longest answer at this point I'm guessing that you may have entered the same rpt_name in your message box (third line of your script?) resulting in the error. You'll probably need to compare what was entered into the message box with a list of existing names before you continue with your script...

Ram · Accepted Answer · 2015-08-04 15:05:18Z

2

I was having the same error earlier today. To resolve it I did the following:

Before the importing of access table I have added a sheet
```
ActiveWorkbook.Worksheets.Add
```

Access Import code:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _................
.........
.........

Rename the sheet. The following code renames the Active sheet to AccessImport
```
ActiveSheet.Name = "AccessImport"
```

edited Aug 4, 2015 at 15:05

Ram

3,09110 gold badges43 silver badges57 bronze badges

answered Aug 4, 2015 at 14:44

Sohel

6863 gold badges11 silver badges33 bronze badges

1 Comment

Helfenstein Over a year ago

This suggestion and removing ".ListObject.DisplayName = .." did for me the trick.

MrExcel · Accepted Answer · 2013-03-15 07:05:51Z

0

Probably your variable "Server_hostname" contains spaces or other characters that are not allowed to use for your table display name.

Try the same code but change the value of rpt_name. For example rpt_name = "avgcpu"

answered Mar 15, 2013 at 7:05

MrExcel

1

Collectives™ on Stack Overflow

Excel vba using variable as the name of ListObjects

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related