Solved: New instance of excel

[gsouza]

Back from vacation, Uganda was great. Happy belated New Year.

If i want to open and excel file in a new instance of excel other than the excel application I already have open, how do I do that with vba. thank you for any help you may give.

[Ken Puls]

Hi gsouza,

I just quickly modified a procedure I used to create a Word instance and converted it to an Excel instance. I haven't tested it, but here's the code:

[vba]Sub CreateExcelInstance()
'Macro purpose: Create a Microsoft Excel instance via code
' using late binding. (No references required)

Dim xlApp As Object
Dim wbExcel As Object

'Create a new instance of Excel
Set xlApp = CreateObject("Excel.Application")

'Open your workbook
Set wbExcel = xlApp.Workbooks.Open("C:\test.xls")

'Set the instance of Excel visible. (It's been hiding until now)
xlApp.Visible = True

'Release the workbook and application objects to free up memory
Set wbExcel = Nothing
Set xlApp = Nothing

End Sub[/vba]

One thing of note... if you have a personal.xls file, it may give you a prompt out it already being open. It doesn't like to share. The way around it is to just open a new workbook, without making a totally new instance of Excel.

HTH,

[mvidas]

Actually, Ken, I think when you add it like this the personal.xls file isn't opened.

Your code does work fine.

One other thing to note, if you don't have a workbook to open (like the "C:\test.xls"), change that line to:
set wbExcel = xlApp.Workbooks.Add
To create a blank workbook instead.

Also, a neat trick I learned today, you can avoid the personal.xls sharing issue by changing the IsAddin property to True. You won't easily be able to save personal.xls, but you'll never have another conflict for it.

Matt

[Ken Puls]

Also, a neat trick I learned today, you can avoid the personal.xls sharing issue by changing the IsAddin property to True. You won't easily be able to save personal.xls, but you'll never have another conflict for it.

Hmmm, cool! Didnt' know that. The only time I ever save my personal.xls is from the VBE anyway, as it pretty much just holds code. I think I'll give that a shot.

Thanks for the clarification and follow up on the others too.

[gsouza]

Thanks guys, just got to work , tryed it and it works just great. I deeply appreciate your help.

[gsouza]

How can i bring the new instance of excel to the back, so it is behind the form in the first instance of excel that was orignially showing.

[Ken Puls]

Hi there,

That might be a little trickier. You might be able to use the GetObject method to pull up the current workbook. This is untested, but:

[vba]GetObject (,"Excel.Application")[/vba]

The logic I'm going on here is that without the path argument (omitted by the first comma), it should return the first instance of Excel. If this doesn't work, though, you may have to go the route of using an API and workinh with Hwnd values to pull up the correct window.

Curious, why are you creating a whole new Excel instance anyway? It's the first time I've run into this. I'm not saying it's wrong, I'm just genuinely curious...

[gsouza]

I got an error with GetObject (,"Excel.Application")

the reason why I want to use another instance of excel is on an assembly production floor I have electronic assembly drawings showing on an excel form webbrower. I have the excel application hidden. Someone else wants to have another excel sheet showing. I want this done seperately from my file. I know I can toggle back and forth from his file to mine, hidden, then not hidden then hidden but having problems. I just rather do it this way if i can. It seems be cleaner.

[mvidas]

How can i bring the new instance of excel to the back, so it is behind the form in the first instance of excel that was orignially showing.

This is easier than dealing with getobject (not sure why that would error though..)
Just put "Application.Visible = True" at the end to restore focus to the original excel app.. should work at least

[gsouza]

application.visible=true did not set the focus to the original excel app. the second instance still remains on top.

[mvidas]

Hmmm... try setting it to false, then true? It worked in my testing, though it wasn't exactly like this. Maybe try

Application.Visible = False
Application.Visible = True

If that doesnt work you could always try

Application.WindowState = xlMinimized
Application.WindowState = xlMaximized

Application, in this case, is refering to the app that is running the code

[Ken Puls]

Hi there,

I tried using Matt's suggestion as follows and it worked for me:
[vba]Sub CreateExcelInstance()
'Macro purpose: Create a Microsoft Excel instance via code
' using late binding. (No references required)

Dim xlApp As Object
Dim wbExcel As Object

'Create a new instance of Excel
Set xlApp = CreateObject("Excel.Application")

'Open your workbook
Set wbExcel = xlApp.Workbooks.Open("C:\test.xls")

'Set the instance of Excel visible. (It's been hiding until now)
xlApp.Visible = True

'Release the workbook and application objects to free up memory
Set wbExcel = Nothing
Set xlApp = Nothing

'Reset focus to current workbook
Application.Visible = True
End Sub[/vba]

I don't know if it would be different due to Excel versions, but I'm on 2003. What version are you using?

[gsouza]

I will try it, I am using version 2000, the "Application.WindowState = xlMinimized" and
"Application.WindowState = xlMaximized" works fine though, you see i have it so it maximizes every hour on the hour, the employee fills in their data and it minimizes after they fill in their last cell, then the window maximizes on the next hour. Its working like a charm without your help I would have been stuck. if I make it visible=false that will work also but this way they can maximize the window manually if they need to. Thanks again, this is the best websight I have ever come across, and you guys are great for helping so much. : )

[mvidas]

Thats why we're here, and seeing comments like that make it all the better

Let us know if you need anything else!

[Ken Puls]

Hi gsouza,

I'll echo Matt's thanks for the comments. Indeed, it's always nice to get positive feeback.

As far as the visible=false, I'd highly recommend staying away from that route. The issue I can see with it is that you'd set it to false, which leaves the workbook open, but not visible. Then what if someone comes along and shuts down the PC? Is the data corrupted? Is it saved? Those are IF's that I wouldn't bet on, either way. Too much risk in that method.

I'd encourage you to go with the Minimize/Maximize route. Sure the user can see it, but it's safer in the long run IMHO.

Hope it helps,