Hello Rose,

Looks like you have scared everybody off <g.
Two comments/suggestions.
1. Open the first application | workbook and sort the sheet,
then open the second application | workbook and sort the sheet.

2. Qualify the Key1 source with the parent object...
moWS(evBefore).UsedRange.Sort key1:=moWS(evBefore).Columns("C"), Header:=xlYes
moWS(evafter).UsedRange.Sort key1:=moWS(evafter).Columns("C"), Header:=xlYes

3. Well a third comment...
You can open multiple workbooks in the same application, why not
simplify and open only one?

Regards,
Jim Cone
San Francisco, USA




wrote in message
oups.com
I have a VB 6.0 project (OS is XP SP2) that instantiates two instances
of Excel (2000). I have a workbook opened in each instance. I then
sort both workbooks. The sort on the workbook in the first instance of
Excel works, but the sort on the workbook of the second Excel instance
fails with "Sort method of Range Object Failed". In the example code
below, the "C:\Before\Report.xls" workbook is opened in the first
instance of Excel, and the "C:\After\Report.xls" workbook is opened in
the second instance of Excel. If I switch this around so the first
instance opens the C:\After workbook and the second instance opens the
C:\Before workbook, the second instance still fails. So, it isn't the
workbook, or the code that is actually doing the sort. It appears to
be a problem with having two instances of Excel open, but I don't know
why that would be. Can anybody tell me how I can get the sort to work
with the second instance of Excel?


Here is an example of code that fails:
Option Explicit
Option Base 1


Dim moXLApp(2) As Excel.Application
Dim moWS(2) As Excel.Worksheet
Enum eBeforeOrAfter
evBefore = 1
evafter = 2
End Enum


Sub main()
Set moXLApp(evBefore) = New Excel.Application
Set moXLApp(evafter) = New Excel.Application


moXLApp(evBefore).Workbooks.Open "C:\Before\report.xls"
moXLApp(evafter).Workbooks.Open "C:\After\report.xls"


Set moWS(evBefore) = moXLApp(evBefore).Workbooks(1).Worksheets(1)
Set moWS(evafter) = moXLApp(evafter).Workbooks(1).Worksheets(1)
moWS(evBefore).Activate
moWS(evafter).Activate


moWS(evBefore).UsedRange.Sort key1:=Columns("C"), Header:=xlYes
moWS(evafter).UsedRange.Sort key1:=Columns("C"), Header:=xlYes
End Sub