PDA

View Full Version : Opening Workbooks in 'new' Excel Windows



xluser2007
02-27-2008, 05:34 PM
Hi All,

I have a macro that opens up 7 workbooks without updating links. I want it to open up 35 workbooks. As the workbooks are bulky, and i wanto open them ALL by clicking a button, Excel crashes.

However if i try to do this task manually, I open 7 spreadsheets from Windows Explorer. Then Open up a new Excel i.e. Start>Programs>Excel etc, then open up 7 more and so forth they open up without causing excel memory strain in opening in teh same window.

I have put together a sample code for opeinig 2 spreadsheets (A and B) below in the SAME window.

Sub Open_Links_and_recalculate_workbook()

' Open SpreadsheetA.xls WITHOUT updating workbook

Workbooks.Open Filename:= _
"C:\CSpreadsheetA.xls" _
, UpdateLinks:=0

' Open SpreadsheetB.xls WITHOUT updating workbook

Workbooks.Open Filename:= _
"C:\CSpreadsheetA.xls" _
, UpdateLinks:=0

End Sub
Could anyone please help me to open up B in a completely new Excel (in line with manual methodology above). Guru guidance is required :doh:.

regards,

trampzju
02-27-2008, 08:28 PM
Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")
xlApp.workbooks.open("your file")

I tried this on my pc, but no Excel window showed. Then i checked in Task Manager which showed a new Excel process started. I hope this can work fine on your pc.

tstav
02-28-2008, 12:03 AM
Hi xluser2007
First create the Excel instances, make them VISIBLE (otherwise they won't be) and finally open the files you want in each of these Excel objects.

Here is the code:
Sub ExcelInstances()
Dim xlApp1, xlApp2 As Object
Set xlApp1 = CreateObject("Excel.Application")
xlApp1.Visible = True
xlApp1.Workbooks.Open Filename:="C:\1.xls"
Set xlApp2 = CreateObject("Excel.Application")
xlApp2.Visible = True
xlApp2.Workbooks.Open Filename:="C:\2.xls"
End Sub

trampzju
02-28-2008, 05:25 AM
Now i know why i couldn't see them. Thx

xluser2007
02-28-2008, 05:34 AM
Hi trampzju and tstav,

Thanks for your fantastic suggestions.

I have been testing them and they have worked really well.

Much apprecaited. :hi:

xluser2007
02-28-2008, 10:45 PM
Hi All,

When I was testing yesterday, the codes posted by tstav and trampzju worked.

However, I want to make a slight change to teh code.

In my current workbook (in which I'm going to write and store the macro to open the 7 new Excel Apps), I have the following macro (as posted before):

Sub Open_Links_and_recalculate_workbook_v1()

' Open SpreadsheetA.xls WITHOUT updating workbook

Workbooks.Open Filename:= _
"C:\CSpreadsheetA.xls" _
, UpdateLinks:=0

Workbooks.Open Filename:= _
"C:\CSpreadsheetB.xls" _
, UpdateLinks:=0


End Sub
I have another macro which has:

Sub Open_Links_and_recalculate_workbook_v2()

' Open SpreadsheetA.xls WITHOUT updating workbook

Workbooks.Open Filename:= _
"C:\CSpreadsheetC.xls" _
, UpdateLinks:=0

Workbooks.Open Filename:= _
"C:\CSpreadsheetD.xls" _
, UpdateLinks:=0


End Sub

The macros are different because they represent specific workbooks to be opened in each new Excel App.

How do I use tstav's macro to "Call" the above macros when opening the new Excel Apps.

e.g.

Sub ExcelInstances()
Dim xlApp1, xlApp2 As Object
Set xlApp1 = CreateObject("Excel.Application")
xlApp1.Visible = True
Call Sub Open_Links_and_recalculate_workbook_v1
Set xlApp2 = CreateObject("Excel.Application")
xlApp2.Visible = True
Sub Open_Links_and_recalculate_workbook_v2
End Sub
This does not open the workbooks in the new Excel App, just in the current one, which cause the original crashing issue.

Any ideas on how to make the above work,

regards

tstav
02-28-2008, 11:54 PM
In your code, add the following calls to your subs

Call Open_Links_and_recalculate_workbook_v1
Call Open_Links_and_recalculate_workbook_v2

which must be slightly changed by adding a few lines (note that you can use the same object name xlApp in all of them)

Sub Open_Links_and_recalculate_workbook_v1()
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
' Open SpreadsheetA.xls WITHOUT updating workbook
xlApp.Workbooks.Open Filename:= _
"C:\CSpreadsheetA.xls", UpdateLinks:=0
xlApp.Workbooks.Open Filename:= _
"C:\CSpreadsheetB.xls", UpdateLinks:=0
End Sub

Sub Open_Links_and_recalculate_workbook_v2()
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
' Open SpreadsheetA.xls WITHOUT updating workbook
xlApp.Workbooks.Open Filename:= _
"C:\CSpreadsheetC.xls", UpdateLinks:=0
xlApp.Workbooks.Open Filename:= _
"C:\CSpreadsheetD.xls", UpdateLinks:=0
End Sub

tstav
02-29-2008, 12:52 AM
As an alternative you can use only one sub without calling your "Open_Links_and_recalculate_workbook_vX" subs.
In this case you should have to
1.create more xlApp objects,
2.move the code from your subs into that sub and
3.call that sub from within your code
which means more coding than the previous option.

.
.
call ExcelInstances
.
.
Sub ExcelInstances()
Dim xlApp1, xlApp2 As Object
Set xlApp1 = CreateObject("Excel.Application")
xlApp1.Visible = True
' Open SpreadsheetA.xls WITHOUT updating workbook
xlApp1.Workbooks.Open Filename:= _
"C:\CSpreadsheetA.xls", UpdateLinks:=0
xlApp1.Workbooks.Open Filename:= _
"C:\CSpreadsheetB.xls", UpdateLinks:=0
Set xlApp2 = CreateObject("Excel.Application")
xlApp2.Visible = True
' Open SpreadsheetA.xls WITHOUT updating workbook
xlApp2.Workbooks.Open Filename:= _
"C:\CSpreadsheetC.xls", UpdateLinks:=0
xlApp2.Workbooks.Open Filename:= _
"C:\CSpreadsheetD.xls", UpdateLinks:=0
End Sub