I want to arrange the worksheets in a Excel worbook in alphabetical
order. I read the worksheet names into a list and sort it. When I try
to rearrange I am having problems. I get a run-time error "Move method
of worksheet class failed"

Any help will be appreciated.
Thanks,
Sunil

Code snippet:

long int numSheets;
CWorksheets Sheets;
CWorksheet wSheet;
Sheets = oBook.get_Worksheets( );
numSheets = Sheets.get_Count( );

for ( int i=1;i<=numSheets;i++ )
{
wSheet = Sheets.get_Item( COleVariant( (short)(i) ) );
wSheet.Activate( );
tmpName = wSheet.get_Name();
//AfxMessageBox( tmpName.c_str() );
if ( ( tmpName != "VE_Dump" ) && ( tmpName != "Summary"
) )
sheetNames.push_back( tmpName );
}

sheetNames.sort( );
//trial to see it it works
wSheet = Sheets.get_Item( COleVariant( (short)(1) ) );
wSheet.Move( COleVariant( (short)(3) ), covOptional );

try chip pearson's code

Sub SortWorksheets()
'Chip Pearson

Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean

SortDescending = False

If ActiveWindow.SelectedSheets.Count = 1 Then
FirstWSToSort = 1
LastWSToSort = Worksheets.Count
Else
With ActiveWindow.SelectedSheets
For N = 2 To .Count
If .Item(N - 1).Index < .Item(N).Index - 1 Then
MsgBox "You cannot sort non-adjacent sheets"
Exit Sub
End If
Next N
FirstWSToSort = .Item(1).Index
LastWSToSort = .Item(.Count).Index
End With
End If

For M = FirstWSToSort To LastWSToSort
For N = M To LastWSToSort
If SortDescending = True Then
If UCase(Worksheets(N).Name) _
UCase(Worksheets(M).Name) Then
Worksheets(N).Move Befo=Worksheets(M)
End If
Else
If UCase(Worksheets(N).Name) < _
UCase(Worksheets(M).Name) Then
Worksheets(N).Move Befo=Worksheets(M)
End If
End If
Next N
Next M

End Sub

--


Gary


wrote in message
oups.com...
I want to arrange the worksheets in a Excel worbook in alphabetical
order. I read the worksheet names into a list and sort it. When I try
to rearrange I am having problems. I get a run-time error "Move method
of worksheet class failed"

Any help will be appreciated.
Thanks,
Sunil

Code snippet:

long int numSheets;
CWorksheets Sheets;
CWorksheet wSheet;
Sheets = oBook.get_Worksheets( );
numSheets = Sheets.get_Count( );

for ( int i=1;i<=numSheets;i++ )
{
wSheet = Sheets.get_Item( COleVariant( (short)(i) ) );
wSheet.Activate( );
tmpName = wSheet.get_Name();
//AfxMessageBox( tmpName.c_str() );
if ( ( tmpName != "VE_Dump" ) && ( tmpName != "Summary"
) )
sheetNames.push_back( tmpName );
}

sheetNames.sort( );
//trial to see it it works
wSheet = Sheets.get_Item( COleVariant( (short)(1) ) );
wSheet.Move( COleVariant( (short)(3) ), covOptional );

Thanks for the Info Gary. But I need to use C++ and not VBA, since I am
running this via an MFC app.

Gary Keramidas wrote:
try chip pearson's code

Sub SortWorksheets()
'Chip Pearson

Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean

SortDescending = False

If ActiveWindow.SelectedSheets.Count = 1 Then
FirstWSToSort = 1
LastWSToSort = Worksheets.Count
Else
With ActiveWindow.SelectedSheets
For N = 2 To .Count
If .Item(N - 1).Index < .Item(N).Index - 1 Then
MsgBox "You cannot sort non-adjacent sheets"
Exit Sub
End If
Next N
FirstWSToSort = .Item(1).Index
LastWSToSort = .Item(.Count).Index
End With
End If

For M = FirstWSToSort To LastWSToSort
For N = M To LastWSToSort
If SortDescending = True Then
If UCase(Worksheets(N).Name) _
UCase(Worksheets(M).Name) Then
Worksheets(N).Move Befo=Worksheets(M)
End If
Else
If UCase(Worksheets(N).Name) < _
UCase(Worksheets(M).Name) Then
Worksheets(N).Move Befo=Worksheets(M)
End If
End If
Next N
Next M

End Sub

--


Gary


wrote in message
oups.com...
I want to arrange the worksheets in a Excel worbook in alphabetical
order. I read the worksheet names into a list and sort it. When I try
to rearrange I am having problems. I get a run-time error "Move method
of worksheet class failed"

Any help will be appreciated.
Thanks,
Sunil

Code snippet:

long int numSheets;
CWorksheets Sheets;
CWorksheet wSheet;
Sheets = oBook.get_Worksheets( );
numSheets = Sheets.get_Count( );

for ( int i=1;i<=numSheets;i++ )
{
wSheet = Sheets.get_Item( COleVariant( (short)(i) ) );
wSheet.Activate( );
tmpName = wSheet.get_Name();
//AfxMessageBox( tmpName.c_str() );
if ( ( tmpName != "VE_Dump" ) && ( tmpName != "Summary"
) )
sheetNames.push_back( tmpName );
}

sheetNames.sort( );
//trial to see it it works
wSheet = Sheets.get_Item( COleVariant( (short)(1) ) );
wSheet.Move( COleVariant( (short)(3) ), covOptional );

Perhaps Chip's macro can help you:
http://www.cpearson.com/excel/sortws.htm

" wrote:

I want to arrange the worksheets in a Excel worbook in alphabetical
order. I read the worksheet names into a list and sort it. When I try
to rearrange I am having problems. I get a run-time error "Move method
of worksheet class failed"

Any help will be appreciated.
Thanks,
Sunil

Code snippet:

long int numSheets;
CWorksheets Sheets;
CWorksheet wSheet;
Sheets = oBook.get_Worksheets( );
numSheets = Sheets.get_Count( );

for ( int i=1;i<=numSheets;i++ )
{
wSheet = Sheets.get_Item( COleVariant( (short)(i) ) );
wSheet.Activate( );
tmpName = wSheet.get_Name();
//AfxMessageBox( tmpName.c_str() );
if ( ( tmpName != "VE_Dump" ) && ( tmpName != "Summary"
) )
sheetNames.push_back( tmpName );
}

sheetNames.sort( );
//trial to see it it works
wSheet = Sheets.get_Item( COleVariant( (short)(1) ) );
wSheet.Move( COleVariant( (short)(3) ), covOptional );

You should be able to translate it easy enough.
Or ask in one the Excel Interop/SDK news groups.

NickHK

wrote in message
oups.com...
Thanks for the Info Gary. But I need to use C++ and not VBA, since I am
running this via an MFC app.

Gary Keramidas wrote:
try chip pearson's code

Sub SortWorksheets()
'Chip Pearson

Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean

SortDescending = False

If ActiveWindow.SelectedSheets.Count = 1 Then
FirstWSToSort = 1
LastWSToSort = Worksheets.Count
Else
With ActiveWindow.SelectedSheets
For N = 2 To .Count
If .Item(N - 1).Index < .Item(N).Index - 1 Then
MsgBox "You cannot sort non-adjacent sheets"
Exit Sub
End If
Next N
FirstWSToSort = .Item(1).Index
LastWSToSort = .Item(.Count).Index
End With
End If

For M = FirstWSToSort To LastWSToSort
For N = M To LastWSToSort
If SortDescending = True Then
If UCase(Worksheets(N).Name) _
UCase(Worksheets(M).Name) Then
Worksheets(N).Move Befo=Worksheets(M)
End If
Else
If UCase(Worksheets(N).Name) < _
UCase(Worksheets(M).Name) Then
Worksheets(N).Move Befo=Worksheets(M)
End If
End If
Next N
Next M

End Sub

--


Gary


wrote in message
oups.com...
I want to arrange the worksheets in a Excel worbook in alphabetical
order. I read the worksheet names into a list and sort it. When I try
to rearrange I am having problems. I get a run-time error "Move method
of worksheet class failed"

Any help will be appreciated.
Thanks,
Sunil

Code snippet:

long int numSheets;
CWorksheets Sheets;
CWorksheet wSheet;
Sheets = oBook.get_Worksheets( );
numSheets = Sheets.get_Count( );

for ( int i=1;i<=numSheets;i++ )
{
wSheet = Sheets.get_Item( COleVariant( (short)(i) ) );
wSheet.Activate( );
tmpName = wSheet.get_Name();
//AfxMessageBox( tmpName.c_str() );
if ( ( tmpName != "VE_Dump" ) && ( tmpName != "Summary"
) )
sheetNames.push_back( tmpName );
}

sheetNames.sort( );
//trial to see it it works
wSheet = Sheets.get_Item( COleVariant( (short)(1) ) );
wSheet.Move( COleVariant( (short)(3) ), covOptional );