CODEWITHSUNDEEP
excelvba
user2292941
user2292941

Reputation: 89

Open specific Excel file in new instance of Excel when file is opened

Info:

I have an Excel file named Demo.xlsm

This file contains a userform named UserForm1 which automatically loads when the file is opened.

The workbook named Demo.xlsm is also visible in the background behind the userform when the file is opened.

Problem:

I often have an Excel application already open on my desktop with various workbooks and worksheets containing information I use on a daily basis. As it stands, if I go to open the Demo.xlsm file, it will open in the current Excel application along with all of the other workbook/worksheets I am using.

First: I would like for the Demo.xlsm file to automatically open in an entirely separate instance/Excel application than my other works.

Second: I would like for only the userform to be visible. (I have no need/use for the workbook/worksheets to be visible in the background.)

Third: If its possible to have the 2nd instance of the Excel application to be minimized while still displaying the userform, that would be perfect. (Currently, if I attempt to minimize the 2nd instance of the Excel application, the userform is also minimized)

  Private Sub Workbook_Open()
  Dim objExcel
  Set objExcel = CreateObject("Excel.Application")
  objExcel.Visible = True

  UserForm1.Show

  Application.ScreenUpdating = False
  Set newBook = Workbooks.Open(Demo.xlsm)
  Windows(Demo.xlsm).Visible = False
  Application.ScreenUpdating = True 

  End Sub

I feel that I am not going about this the right way...

Any help with this would be greatly appreciated!

Upvotes: 2

Views: 25285

Answers (4)

Boltie
Boltie

Reputation: 3

Copy a shortcut to Excel and then right-click, select properties and add to the end of the target " - "C:\Demo.xlsm"" (exclude the first and last quotation marks and replace with the real filepath for your file.

Upvotes: 0

juriemagic
juriemagic

Reputation: 41

This is what I do, and it works lovely. I have a sheet with a nice big picture, and the picture has a macro, which is:

Dim appXL As New Excel.Application

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

This opens my file in its own instance and I can close it as I want, it does not interfere with any other open files.

Upvotes: 3

Dave
Dave

Reputation: 21

This worked for me. Copy paste this code into the ThisWorkbook object:

Option Explicit

Dim objExcel As Excel.Application

Dim FileName As String

Public Sub workbook_open()
    FileName = ThisWorkbook.FullName
    If vbReadOnly <> 0 Then
        Exit Sub
    Else
        objExcel.Workbooks.Open FileName:=FileName
        ThisWorkbook.Saved = True
        ThisWorkbook.Close
        objExcel.Quit
    End If
End Sub

Upvotes: 1

PatricK
PatricK

Reputation: 6433

For your Workbook_Open, do something like below should get you close.

If Application.Workbooks.Count > 1 Then
    Call Shell(Application.Path & Application.PathSeparator & "excel.exe " & ThisWorkbook.FullName)
    ThisWorkbook.Close SaveChanges:=False
Else
    UserForm1.Show
End If

I tested this and there is another "Excel.exe" in Task Manager, so it should be right.

Upvotes: 0

Related Questions