Exporting to excel

[amars]

Hi all,

I want to export all the object's of a sheet to excel. I want to identify the no of column's occupied by an object when exporting to excel. So if someone could tell me that. Thank's in advance.

Regards

Amar

[amars]

Hi all,

Now I have written a macro to export all object's (i.e. list, table, chart's) to excel . Each object on a new column. The macro is like this

'------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

sub CopyToXL

' Set Excel App

dim iCount

dim ObjName

dim ObjCaption

iCount=65

set XLApp = CreateObject("Excel.Application") ' Define Object

XLApp.Visible = True 'Visible set as true

set XLDoc = XLApp.Workbooks.Add 'Open new workbook

'Take the List of Object's in Excel

set XLSheet = XLDoc.Worksheets(1)

Shtobj = ActiveDocument.ActiveSheet.GetSheetObjects

for i = lBound(Shtobj) to uBound(Shtobj)

set SheetObj = ActiveDocument.GetSheetObject(Shtobj(i).GetObjectId)

ObjName = Shtobj(i).GetObjectId

ObjCaption = Shtobj(i).GetCaption.Name.v

If Mid(ObjName,10,2)="LB" or Mid(ObjName,10,2)="TB" or Mid(ObjName,10,2)="CH" Then

'ObjCaption = SheetObj.Caption

If(((iCount-65)/26) > 1 ) Then

Cell = chr(((iCount-65)/26) + 64) & Chr(((iCount -65) Mod 26) + 65)

else

Cell = Chr(iCount)

End if

SheetObj.CopyTableToClipboard true

XLSheet.Range(Cell& "1") = ObjCaption

XLSheet.Range(Cell& "1").Font.Bold = True

XLSheet.Paste XLSheet.Range(Cell& "2")

iCount= XLSheet.UsedRange.Columns.Count + 1

iCount = iCount + 65

End if

next

end sub

'------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I want to add excel formatting to it , if some one knows please tell me .

Regards

Amar