How to convert the table to a regular range of data using VB script

[djanu]

Hi,

I used this to convert the table to a regular range of data in Excel 2003:

Sheets("Temp").ListObjects("List1").Unlist

This no longer works in Excel 2007. Is there any other way to do this in Excel 2007?

Thank you,
djanu

 

[asma410]

The above formula works in 2007 too. When you format your list as a table make sure that your table is called "List1".
To rename your table, select the entire range of you table, Click Design under Table Tools (the new tab that shows up in 2007), Change the name of the table to List1 under Properties>Table Name (first from left)
You should be able to convert your table into a range, same as 'Convert To Range' under Tools. But it does retain the color format you chose when formatting your range as a table. But you can use a simple macro to get rid of the colors too.

 

[djanu]

I am actually importing for XML:

With Sheets("Temp").Range("A1").CurrentRegion
ActiveWorkbook.XmlImport URL:="C:\Temp\Test.xml", ImportMap:=Nothing, _
Overwrite:=True, Destination:=Sheets("Temp").Range("A1")
Sheets("Temp").ListObject("List1").Unlist

Where do I specify table name?

 

[daverunt]

Useful Page:

http://www.jkp-ads.com/Articles/Excel2007TablesVBA.asp?AllComments=True

 

[djanu]

Thank you! It works now:

Private Sub CommandButton1_Click()

'IMPORT XML - This part will import .xml file to Excel

With Sheets("Temp").Range("A1").CurrentRegion
ActiveWorkbook.XmlImport URL:="C:\Temp\Test.xml", ImportMap:=Nothing, _
Overwrite:=True, Destination:=Sheets("Temp").Range("A1")
Sheets("Temp").ListObjects("Table1").Name = "List1"
Sheets("Temp").ListObjects("List1").Unlist
End With

End Sub