R
Rose.Wehland
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
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