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

Thanks for the useful help. Your suggestion number 2 works. I know
very well that .Columns needed to be qualified, I just wasn't seeing it
when reviewing my code.

Suggestion #1 doesn't work in my case. My program is a workbook
compare program. I have two directories that contains workbooks to be
compared, a "\before" directory and a "\after" directory. Suggestion
#1 would mean I'd have to instantiate Excel for every workbook I'm
processing.

Suggestion #3 isn't viable for my process because the workbook I have
open in the first instance of Excel has the same name as the workbook
in the second instance of Excel. Excel doesn't allow two workbooks of
the same name open at the same time even if they are in separate
directories.

My program compares each cell in the "before" workbook to each cell in
the "after" workbook and flags differences by cell.

Thanks again for your help!