PDA

View Full Version : [SOLVED] Find and rename Table in Workbook if it is present



vanhunk
11-02-2016, 04:40 AM
Check if Table is existing in workbook and change its name if it is:

I need to determine with vba if a specific Table name, e.g. "ZA_Table", is already used in the workbook, if it is, I want the code to change the name of the existing Table to "OldTable".

1) How can this be done?
2) How can I determine what the full address of the table is, i.e. sheet name and range?

Range("ZA_Table").Address only gives the range.

Thank you,
Kind Regards
vanhunk

mana
11-02-2016, 05:13 AM
Option Explicit

Sub test()
Dim ws As Worksheet
Dim t As ListObject

On Error Resume Next

For Each ws In Worksheets
Set t = ws.ListObjects("ZA_Table")
If Not t Is Nothing Then
t.Name = "OldTable"
MsgBox t.Range.Address(external:=True)
Exit For
End If
Next

End Sub

vanhunk
11-02-2016, 05:57 AM
@mana:
Thank you very much, your code does the job and you also gave me another idea. Please have a look at the code below (also working fine).

Sub toets2()
Dim Adres As String

On Error Resume Next
Adres = Range("ZA_Table").Address(external:=True)

Range(Adres).ListObject.Name = "OldTable"

MsgBox Adres
End Sub

Regards
vanhunk

vanhunk
11-15-2016, 12:00 AM
Add some checking for existing table and error checking. If the code is adding a table and the table already exists, it just adds a number to it, without warning or informing you. If you reference the new table with the name you gave it, it wont work because of the additional number at the end.

Regards,
vanhunk

Aussiebear
11-15-2016, 12:46 AM
Maybe try this



Option Explicit
Sub test()
Dim ws As Worksheet
Dim ws As Worksheet
Dim t As ListObject
Dim t As ListObject
On Error Resume Next
For Each ws In Worksheets
Set t = ws.ListObjects("ZA_Table")
Set r = ws,ListObjects("OldTable")
If Not t Is Nothing Then
If Not R is Nothing Then
MsgBox "OldTable exists!" VBOkOnly
Exit Sub
Else
t.Name = "OldTable"
MsgBox t.Range.Address (external=:True)
Exit For
End If
End If
End Sub


[/CODE]

snb
11-15-2016, 03:38 AM
Sub M_snb()
If [isref(ZA_Table)] Then ListObjects("ZA_Table").Name = "old_table"
End Sub

vanhunk
11-15-2016, 05:18 AM
Thank you VBAX Guru and snb, I appreciate your inputs!

Regards,
vanhunk