Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
MT
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)
Could you paste whole sub function?
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
next
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
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
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)
hey thank you. and yes that is very strange 🙂