CODEWITHSUNDEEP
excelvba
Ivan Chan
Ivan Chan

Reputation: 39

Excel vba using variable as the name of ListObjects

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

Upvotes: 0

Views: 8727

Answers (3)

Sohel
Sohel

Reputation: 676

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

  1. Before the importing of access table I have added a sheet

    ActiveWorkbook.Worksheets.Add

  2. Access Import code: 

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

  3. Rename the sheet. The following code renames the Active sheet to AccessImport

    ActiveSheet.Name = "AccessImport"

Upvotes: 2

6mtg35
6mtg35

Reputation: 31

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

Upvotes: 3

MrExcel
MrExcel

Reputation: 1

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"

Upvotes: 0

Related Questions