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 Before:=Worksheets(M)
End If
Else
If UCase(Worksheets(N).Name) < _
UCase(Worksheets(M).Name) Then
Worksheets(N).Move Before:=Worksheets(M)
End If
End If
Next N
Next M

End Sub

--


Gary


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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 Before:=Worksheets(M)
> End If
> Else
> If UCase(Worksheets(N).Name) < _
> UCase(Worksheets(M).Name) Then
> Worksheets(N).Move Before:=Worksheets(M)
> End If
> End If
> Next N
> Next M
>
> End Sub
>
> --
>
>
> Gary
>
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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

"(E-Mail Removed)" 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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 Before:=Worksheets(M)
> > End If
> > Else
> > If UCase(Worksheets(N).Name) < _
> > UCase(Worksheets(M).Name) Then
> > Worksheets(N).Move Before:=Worksheets(M)
> > End If
> > End If
> > Next N
> > Next M
> >
> > End Sub
> >
> > --
> >
> >
> > Gary
> >
> >
> > <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > >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 );
> > >
>