Problem with Instance Code

[Juriemagic]

Hi good people,

Please help me with modifying this code so that the workbook which is opened, be put on top. Currently every time I have to click the task bar to maximize the workbook. Thank you all greatly!
Dim appXL As New Excel.Application

appXL.Workbooks.Open "G:\All Users\Jurie\Test\Hotel Maintenance V1SE.xlsm"
appXL.Visible = True
Workbooks("Hotel Stay Easy Maintenance Program.xlsm").Close SaveChanges:=True
appXL.ActiveWorkbook.Windows(1).Visible = True

[SamT]

Windows("Hotel Maintenance V1SE").Activate

[Juriemagic]

awww, by now I should know that!..shame on me!!!. SamT, thank you very much, it is appreciated!

[Juriemagic]

Hi SamT,

Nope, not working. What I mean is that ANY change I make on the code, causes the code to work once off. It works the 1st time, as soon as the file is re-saved, this code does not do what it nust, meaning, it opens the file, but fails to bring it to the top. I have tried:

Dim appXL As New Excel.Application

appXL.Workbooks.Open "G:\All Users\Jurie\Test\Hotel Maintenance V1SE.xlsm"
appXL.Visible = True
Workbooks("Hotel Stay Easy Maintenance Program.xlsm").Close SaveChanges:=True
appXL.ActiveWorkbook.Windows(1).Visible = True
Windows("Hotel Maintenance V1SE").Activate

then tried:

Dim appXL As New Excel.Application

appXL.Workbooks.Open "G:\All Users\Jurie\Test\Hotel Maintenance V1SE.xlsm"
appXL.Visible = True
Windows("Hotel Maintenance V1SE").Activate

then tried:
Dim appXL As New Excel.Application

appXL.Workbooks.Open "G:\All Users\Jurie\Test\Hotel Maintenance V1SE.xlsm"
Windows("Hotel Maintenance V1SE").Activate

All these changes only works the first time, as soon as the file is re-saved, it simply refuses to do it...and what gets me is that the file is re-saved exactly as the same name, etc..

[SamT]

What are you using to trigger or run the sub?

[Juriemagic]

I have a large picture on a sheet, (picture resembles what the workbook to be opened is all about), which contains a macro..which is the macro already posted, then, well, the workbook is opened...I hope I answered your question?

[Aflatoon]

This should fail:

Windows("Hotel Maintenance V1SE").Activate
since that workbook is open in the other applicaiton instance. It should be:

appxl.Windows("Hotel Maintenance V1SE").Activate
and you may need to add:

Appactivate appxl.caption

If that still doesn't work, you may need an API call (SetForegroundWindow).

[snb]

Why don't you use:


Sub M_snb()
With GetObject("G:\All Users\Jurie\Test\Hotel Maintenance V1SE.xlsm")
.Windows(1).Visible = True
End With
End Sub

[Juriemagic]

Aflatoon,

I have tried..everything works once only..BUT, I finally got it!!..this is the code:

Dim appXL As New Excel.Application

appXL.Workbooks.Open "G:\All Users\Jurie\Test\Hotel Maintenance V1SE.xlsm"
appXL.Visible = True
Workbooks("Hotel Stay Easy Maintenance Program.xlsm").Activate
ActiveWindow.WindowState = xlMinimized
Workbooks("Hotel Stay Easy Maintenance Program.xlsm").Close SaveChanges:=True
Workbooks("Hotel Maintenance V1SE.xlsm").Activate

The minimizing does it!...Thanx for all your assistance, I really do appreciate it all!!

[Juriemagic]

snb,

thank you for your input. I have also tried your code, but what happens is my workbook, (for some reason), opens haphazardly, meaning, all it's pages seem to load one by one, although extremely fast, but does not look good.


my code in post #9 works every time. Thank you kindly..

[Aflatoon]

Still, unless you have the same workbook open in both instances, this should fail:

Workbooks("Hotel Maintenance V1SE.xlsm").Activate

[snb]

snb,

thank you for your input. I have also tried your code, but what happens is my workbook, (for some reason), opens haphazardly, meaning, all it's pages seem to load one by one, although extremely fast, but does not look good.

I don't know where you put this code; when it is being triggered, but what you say seems nonsense to me.

[Juriemagic]

well, that is what happens...