Open workbook in existing Excel session

[mnehlig]

Hi,

I would like to open a workbook in an existing Excel session with a
macro/ VBA instead of creating a new session.

Background:
I copy certain cells from workbook B to workbook A. This task is
initiated by a macro which is part of the open workbook A. The macro
opens workbook B, copies the cells (or the content of the cells) from
Workbook B to A and closes workbook B again. So far, so good. However,
some cells in the source workbook B cause a runtime error while
copying. The reason seems to be that those cells contain too many
characters; to be precise: more than exactly 910 (!). This means that
cells which contain more than 910 characters cannot be copied this way.
Now I have read somewhere that this limit can be bypassed by opening
both workbooks between which you want to copy in one single Excel
session.

So, does anybody Know how you manage to open a second Excel workbook in
an already existing Excel session by VBA/ macro?

Thanks for any hint on that!!!

Cheers
Markus

 

[ward376]

How are you opening the workbook now? Are you instantiating an instance
of Excel?

The workbook will open in the same instance by default, you'd have to
explicitly tell it to open in a new instance.

Example
Workbooks.Open Filename:= _
"C:\Documents and Settings\yadayada.xls"

 

[mnehlig]

Example
Workbooks.Open Filename:= _
"C:\Documents and Settings\yadayada.xls"

Does your example open another instance? How do I recognize whether
there are one or more Excel instances running?

Thanx
Markus

 

[ward376]

The included example opens the workbook in the same instance.

 

[mnehlig]

Then I have to look for another solution. This is the code I am using:

Sub copyTest()
Dim fileName, wbkZiel As Workbook, wbkQuelle As Workbook
fileName = Application.GetOpenFilename()
Set wbkZiel = ThisWorkbook: Set wbkQuelle = Workbooks.Open(fileName)
wbkZiel.Worksheets(1).Range("A2") = wbkQuelle.Worksheets(1).Range("A2")
wbkQuelle.Close
Set wbkZiel = Nothing
Set wbkQuelle = Nothing
End Sub

Thank you for your help.

 

[ward376]

Your code works flawlessly on my machine with xl 2003 and even in xl
2007 beta. I tried with 2658 characters including letters, numbers and
puncuation/operators etc. What is the error number you're getting?