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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

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,118,862
Messages
5,574,715
Members
412,613
Latest member
EFRATA
Top