Export to excel

[#1]

hi

i am exporting different tables to excel through a macro where in some tables are in one sheet and some are in other sheets

I just cant find the way to move the sheets up or down through macro so that i can place the sheets accordingly in the report

thanks

Peter

[amars]

Hi Peter,

I have written a macro which will export all the object on a sheet(Table, chart & List's) in a excel file leaving one alternate column. It is like this

'****************************************************************************************************************************************************************

sub CopyToXL

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)

' Get TB1

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

'msgbox Cell & iCount

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

'XLSheet.Paste XLSheet.Cells(1,iCount)

iCount= XLSheet.UsedRange.Columns.Count + 1

iCount = iCount + 65

End if

'msgbox iCount

next

end sub

'****************************************************************************************************************************************************************

Try using this one , It is not properly formated , so you will need to add the formating.

Regard's

Amar

[#3]

hi

thanks for the reply

but i still cant find a way to make my sheets up or down , means that i am able to move the sheet 3 before sheet1

[#4]

Not sure if you found a way to sort your worksheets but here is how I am doing it. This is the code that I am using to create a new workbook, add sheets, move sheets and rename them. You may have to modify it slightly to work with any other existing Excel macros that you have created.

set XLApp = CreateObject("Excel.Application") 'Define Object
XLApp.Visible = True 'Visible set as true

set XLDoc = XLApp.Workbooks.Add 'Open new workbook
 'XLDoc.Worksheets(3).Delete 'Delete unneeded sheets
 'XLDoc.Worksheets(2).Delete 'Delete unneeded sheets
 XLDoc.Worksheets.Add 'To Add Additional Sheet
XLDoc.Worksheets("Sheet4").Move, XLDoc.Worksheets("Sheet3") 'Parameter after comma means move param 1 after param 2 (move sheet 4 after sheet 3)
'XLDoc.Worksheets("Sheet3").Move XLDoc.Worksheets("Sheet2") 'no comma means move param 1 before param 2 (in this case it would move sheet 3 before sheet 2)

'Format cells
set table = ActiveDocument.GetSheetObject("CH02") 'Qlikview object that you are pulling data from (in this case chart 2)

'Copy table and paste into Excel
set XLSheet = XLDoc.Worksheets("Sheet1") 'sheet where data should be pasted
table.CopyTableToClipboard true
XLSheet.Paste XLSheet.Range("A1") 'Starting Column in which to paste data
XLSheet.Name = "Chart 2" 'Rename worksheet

'Add additional statements like the section above ('Format cells through the rename statement) to copy and paste additional Qlikview objects into sheets in the same workbook.