Exporting to excel

amars — Fri, 10 Jul 2009 12:25:18 GMT

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

Exporting to excel

amars — Fri, 10 Jul 2009 18:06:59 GMT

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

end sub

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

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

Regards

Amar

topic Exporting to excel in QlikView

Exporting to excel

Exporting to excel