How do i export CATIA tree (containing sub-assemblies) to Excel sheet using VB script ?

You can use direct method in assembly design module.

-->Analyze-->Bill of Material-->Define Format (for optional options)-->>Save as-->File format as .xls

Here is your macro,

Sub CATMain()
Dim productDocument1 As Document
Set productDocument1 = CATIA.ActiveDocument
'Input box to select txt or xls
Dim exportFormat As String
exportFormat = Inputbox ("Please choose format to export the tree as._ Type either 'xls' or 'txt'")
IF exportFormat <> "xls" THEN
IF exportFormat <> "txt" THEN
MsgBox "Did not enter txt or xls. Program cancelled please retry macro."
Else
'Input box to enter name of file
Dim partName As String
partName = Inputbox ("Please enter the file name.")
'Input box to enter file location
Dim oLocation As String
oLocation = "C:\Macro Files\"
productDocument1.ExportData oLocation & partName & "." & _
exportFormat,"txt"
End If
End If
End Sub

Before runing as macro, change your file location on 6th line from bottom and enter extension as txt also xls format is not working.