Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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.