Move WorkSheet within Workbook using ActiveX in VBScript

[spcheng7]

Hi all,
I want to have a function that move the first workseet
to the last position within the same workbook, that is
for example, i have [ 1 2 3 4 5 ] worksheets in a workbook
A. Then I want to have a new order e.g. [ 2 3 4 5 1].

I have visited msdn and found that I should use a Move
method of WorkSheet object. But this object has two
optional parameters , "Before" and "After". Only either one
of them can be specified when I call this method.

Now, my question is what is the correct syntax in
VBScript since VBscript does not support optional parameters
Can anyone give me an example ? Thank you very much.

Tony

 

[tsuji]

Hello spcheng7,

In vbs, [before] or [after] uses its position and name its object worksheet. Suppose your workbook is d:\test.xls. Here is how sheet moves.

set oxl=createobject("Excel.Application")
oxl.visible=true
oxl.workbooks.open "d:\test.xls"
set owb=oxl.activeworkbook

'move the first to [after] the last
owb.sheets(1).move ,owb.sheets(owb.sheets.count)
'move the now second sheet to [before] the first
owb.sheets(2).move owb.sheets(1)

'do other things
'close, clean up etc here omitted.

regards - tsuji

 

[spcheng7]

You have saved me , tsuji
I have solved my problem. Thanks