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.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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
 
Upvote 0
Hi dataluver, just had a play and the workbook opens as read only, would you know a workaround to make it editable? :unsure:
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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
Back
Top