VBA code to open multiple workbooks as separate instance of excel

PassSystems

New Member
Joined
Sep 19, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Hi, I am generating a spreadsheet for my business and would like to open multiple workbooks as separate instances of excel.

The reason I want to do this is to remove the potential for memory crash. The spreadsheet is userform, graphic and data heavy so want to ensure I don't come across the dreaded not enough memory error by splitting it up into multiple workbooks. I am running 64bit but still want to split up the workbooks because it opens up other options to improve its usability.

I already have the code set up to obtain both the full file name with path included and the filename only (see below). I am looking for the VBA code to open the file/s in separate instances of excel.

Dim FileName As Variant
Dim FileNameFull As Variant


' Allows the user to select the client data file name, with path and saves it to the cell "B4" in the "Data" worksheet
' Also sets the Me.TextFullFile.Value as the client file name with path information.


FileNameFull = Application.GetOpenFilename(Filefilter:="Excel Files,*.xls*")

If FileNameFull = "False" Then
Me.Text_FileName = "Select Data File Before Continuing"
Else
Me.TextFullFile.Value = FileNameFull
ThisWorkbook.Sheets("Data").Range("B4").Value = Me.TextFullFile.Value
End If


' Removes the path details and saves the file name only to the cell "B3" in the "Data" worksheet
' Also sets the Me.Text_FileName.Value as the client file name without path information.


FileName = Mid(FileNameFull, InStrRev(FileNameFull, "\") + 1)

If FileName = "False" Then
Me.Text_FileName = "Select Data File Before Continuing"
Else
Me.Text_FileName.Value = FileName
ThisWorkbook.Sheets("Data").Range("B3").Value = Me.Text_FileName.Value
End If


Your help would be greatly appreciated.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
L

Legacy 456155

Guest
I think...

VBA Code:
Sub OpenInNewInstance(FileNameFull As String)
    Dim xl As New Excel.Application
    
    xl.Workbooks.Open FileNameFull
    xl.UserControl = True
    xl.Visible = True
End Sub
 

PassSystems

New Member
Joined
Sep 19, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Thank you so much datalover, works absolutely perfectly.

:giggle:
 

PassSystems

New Member
Joined
Sep 19, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi dataluver, just had a play and the workbook opens as read only, would you know a workaround to make it editable? :unsure:
 

PassSystems

New Member
Joined
Sep 19, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Incidentally this works if the user clicks Cancel and not a file.

If FileNameFull = "False" Then
Me.Text_FileName = "Select Data File Before Continuing"
Else
xl.Workbooks.Open FileNameFull
xl.UserControl = True
xl.Visible = True
End If
 
L

Legacy 456155

Guest

ADVERTISEMENT

Hi dataluver, just had a play and the workbook opens as read only, would you know a workaround to make it editable?
I don't know? I'll look at it later. Let me know if you figure it out. Have a good one.
 

PassSystems

New Member
Joined
Sep 19, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Hi dataluver, after a bit of investigation (I should have done this before I asked and not been a lazy ****) the following works...

If FileNameFull = "False" Then
Me.Text_FileName = "Select Data File Before Continuing"
Else
xl.Workbooks.Open FileNameFull, UpdateLinks:=3, ReadOnly:=False
xl.UserControl = True
xl.Visible = True

End If
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,389
Members
416,026
Latest member
melvic69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top