Sorting Problem in Excel Through Access VBA

[tbg130]

Hi All,

I have the following function in a VBA App. I've developed in Access. It is to sort the worksheets in an Excel file. It worked great up till recent unrelated changes and now doesn't complete the process. The sheets are named and numbered (1. Name, 2. Name I, 3. Name II etc.). The sort function works except the final order ends up as follows:

2. Name II
1. Name I
3. Name III
4. Name IV
5. Name V
6. Name VI
Etc. etc...

It's just number 1 and 2 that are out of order... Any ideas or solutions would be great appreciated!

Thanks,


Public Sub WorkSheetSort()

On Error GoTo SkipSort

For Each wnull In objbook.Sheets
prevname = ""
For Each w In objbook.Sheets
If prevname <> "" Then
If w.Name < prevname Then
w.Move Before:=objbook.Sheets(wnull.Index)
Else
prevname = w.Name
End If
Else
prevname = w.Name
End If
Next
Next
SkipSort:

End Sub

 

[HughLerwill]

tbg130,

This seems to be working for me (tested from an Excel macro)

Sub sortbysheetname()

On Error GoTo SkipSort

Dim objbook As Workbook
Set objbook = ActiveWorkbook

Dim i%, j%, madeswap As Boolean
For i = 1 To objbook.Sheets.Count
madeswap = False
For j = 1 To objbook.Sheets.Count - 1
If objbook.Sheets(j).Name > objbook.Sheets(j + 1).Name Then
objbook.Sheets(j).Move After:=objbook.Sheets(j + 1)
madeswap = True
End If
Next
If Not madeswap Then Exit For
Next

SkipSort:

End Sub

regards Hugh