VBA : Switching from one Excel instance to another

Hi,

How can I switch from one excel application (instance to another) via VBA?

I have a workbook Book1 opened in an instance.I want my macro to open a new workbook (Book2) in a new instance of Excel (this part is working fine so far),
then copy cells from Book1 to Book2
and then copy cells from Book2 back on Book1 (that's the tricky part for me)

Does anybody know how i can do this ?

Thank you in advance,

Hi,

I ma actually having problems switching from one Excel instance to another: as soon as i create a new instance with
Set MyXL = CreateObject("Excel.Application")
and as i copy from the first instance to this MyXL one, I can't seem to switc back to the first one again...

hi,

here is the code i'm using:i'm trying to launch the solver from one Excel instance to another excel instance, as follows:

Thank you in advance for your help !


Sub SolverLaunch()

Dim i, j As Integer
Dim DebutPlage, FinPlage As Integer
Dim NomSheet As String
Dim WorkBookTM1 As Workbook
Dim LettreTemp As String
Dim NewCode As String

Set WorkBookTM1 = ActiveWorkbook
NomSheet = "my sheet"
FinPlage = 68
DebutPlage = 39

For i = 39 To 68
If (Cells(i, 3) > 0 And Cells(i - 1, 3) < 1) Then
DebutPlage = i
GoTo SuiteDebutPlage
End If
Next i
SuiteDebutPlage:


For i = 39 To 68
If (Cells(i, 3) <= 0 And Cells(i - 1, 3) > 0) Then
FinPlage = i - 1
GoTo SuiteFinPlage
End If
Next i
SuiteFinPlage:



Dim MyXL As Object
Dim MyWorkbook As Workbook
Set MyXL = CreateObject("Excel.Application")
MyXL.Application.Visible = True
MyXL.Visible = True
MyXL.Workbooks.Add
Set MyWorkbook = MyXL.ActiveWorkbook

' Stockage du code du module "exporter" du classeur maitre
With WorkBookTM1.VBProject.VBComponents("ForSolver").CodeModule
NewCode = .Lines(1, .CountOfLines)
End With
With MyWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.DeleteLines 1, .CountOfLines
.AddFromString NewCode
End With

MyWorkbook.ActiveSheet.Select

With MyWorkbook.ActiveSheet.Range("AT28:AT28")
.Formula = "=SUM($R$28:$AC$28)+SUM($AG$28:$AR$28)"
End With

SolverReset


solverok SetCell:=MyWorkbook.ActiveSheet.Range("AT28:AT28"), MaxMinVal:=3, ValueOf:="0", ByChange:=MyWorkbook.ActiveSheet.Range(MyWorkbook.ActiveSheet.Cells(DebutPlage, 9), MyWorkbook.ActiveSheet.Cells(FinPlage, 9))

For j = DebutPlage To FinPlage
SolverAdd CellRef:=MyWorkbook.ActiveSheet.Cells(j, 9), Relation:=3, FormulaText:="0"
Next j

MyWorkbook.ActiveSheet.Range("AT28:AT28").Select

MyXL.Application.Run "Solver.xla!SolverSolve"

' With MyWorkbook
' .Saved = True
' .Close
' End With
' MyXL.Application.Quit
' Set MyXL = Nothing

End Sub


content of ForSolver:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "Feuil1" Then
On Error Resume Next
ThisWorkbook.VBProject.References.AddFromFile "C:\Program Files\Microsoft Office\OFFICE11\Bibliothèque\SOLVER\solver.xla"
End If