Importing / Exporting data from a Separate Instance of Excel workbook

PassSystems

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

II am working on a spreadsheet that is, by design graphics rich with many userforms. So that I don't hit the dreaded "Insufficient Memory" chestnut I want to manipulate the data while having it in a 'Separate Instance' of Excel.

I want to be able to load and retrieve data to / from the Separate Instance of Excel (file name as retrieved by the following code, for this exercise the FileName will be 'Test Site 1.xlsx').

What is the syntax I use to reference the workbook, sheet and cell to manipulate the data?

The following code allows me to get both the filename with path and the filename only then open the file as a separate instance of excel.

Private Sub DataFile_Click()

Application.ScreenUpdating = False


Dim FileName As Variant
Dim FileNameFull As Variant
Dim xl As New Excel.Application


' 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 it 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

' Opens the client data workbook as a separate instance of Excel

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



Application.ScreenUpdating = True

End Sub
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
If you replace this line
xl.Workbooks.Open FileNameFull, UpdateLinks:=3, ReadOnly:=False

with this
VBA Code:
Dim oWb As Workbook
Set oWb = xl.Workbooks.Open(FileNameFull, UpdateLinks:=3, ReadOnly:=False)

then oWb refers to the workbook in that other instance of Excel.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
My pleasure :)
 

Watch MrExcel Video

Forum statistics

Threads
1,128,088
Messages
5,628,594
Members
416,326
Latest member
NinaChristal

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