topic Re: move worksheet macro in QlikView

move worksheet macro

Mon, 28 Jan 2013 09:46:11 GMT

hi everyone,

I want to move a worksheet in an excel sheet to the start position. right now it is always at the second so my worksheets are in this order:

701 , Total, 704,708 and I want it in this order Total, 701, 704, 708.

ActiveDocument.Fields("#Filiale_KBS").Select ""

ActiveDocument.GetSheetObject("CH23").Restore

ActiveDocument.GetSheetObject("CH23").CopyTableToClipboard true

set XLSheet = XLDOC.Worksheets.Add

XLSheet.Paste XLSheet.Range("A1")

XLSheet.Cells.EntireColumn.AutoFit

XLSheet.Name = "Gesamt"

XLSheet.Move , XLDoc.Sheets(1)

ActiveDocument.GetApplication.WaitForIdle

ActiveDocument.ClearCache

this is moving the sheet total to second position and XLDoc.Sheets(0) doesnt work. does anyone have an idea?

regards,

Re: move worksheet macro

Mon, 28 Jan 2013 10:00:08 GMT

Could you paste whole sub function?

Re: move worksheet macro

Mon, 28 Jan 2013 10:01:29 GMT

sub send()

set Filiale = ActiveDocument.Fields("#Filiale_KBS").getSelectedValues

set XLApp = CreateObject("Excel.Application")

set XLDOC = XLApp.Workbooks.Add 'Open ("C:\Users\p0862\Desktop\Filialen.xls")

XLApp.Visible = True

'Const XLPasteValues = -4163

for i=0 to Filiale.Count -1

'msgbox Filiale.Item(i).Text & ""

'Filiale.Item(i).Select

ActiveDocument.Fields("#Filiale_KBS").Select Filiale.Item(i).text

ActiveDocument.GetSheetObject("CH23").Restore

ActiveDocument.GetSheetObject("CH23").CopyTableToClipboard true

if i => 3 then

Set XLSheet = XLDoc.Worksheets.Add

XLSheet.Name = Filiale.Item(i).text

XLSheet.Move , XLDoc.Sheets(XLDoc.Worksheets.Count)

XLSheet.Paste XLSheet.Range("A1")

XLSheet.Cells.EntireColumn.AutoFit

ActiveDocument.GetApplication.WaitForIdle

ActiveDocument.ClearCache

else

set XLSheet = XLDOC.Worksheets("Tabelle" & (i+1))

XLSheet.Paste XLSheet.Range("A1")

XLSheet.Cells.EntireColumn.AutoFit

XLSheet.Name = Filiale.Item(i).Text

ActiveDocument.GetApplication.WaitForIdle

ActiveDocument.ClearCache

end if

ActiveDocument.Fields("#Filiale_KBS").Select ""

ActiveDocument.GetSheetObject("CH23").Restore

ActiveDocument.GetSheetObject("CH23").CopyTableToClipboard true

set XLSheet = XLDOC.Worksheets.Add

XLSheet.Paste XLSheet.Range("A1")

XLSheet.Cells.EntireColumn.AutoFit

XLSheet.Name = "Gesamt"

XLSheet.Move , XLDoc.Sheets(1)

ActiveDocument.GetApplication.WaitForIdle

ActiveDocument.ClearCache

set XLApp = Nothing

set XLDOC = Nothing

set XLSheet = Nothing

'ab.close

'objExcel.quit

'obj.SendToExcel

end sub

Re: move worksheet macro

Mon, 28 Jan 2013 10:03:34 GMT

maybe thats helpfull

if i select less than 3 stores it works perfectly but if I select more than 3 stores it is always beeing shown on the second position

regards

Re: move worksheet macro

Mon, 28 Jan 2013 10:20:50 GMT

i konw, it is a little bit strange:/ it should add new sheet on the left:/

part solution is to move the sheet

try :

XLDOC.Sheets("Gesamt").Move XLDOC.Sheets(1)

Re: move worksheet macro

Mon, 28 Jan 2013 10:26:18 GMT

hey thank you. and yes that is very strange 🙂