Importing / Exporting data from a Separate Instance of Excel workbook

[PassSystems]

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

 

[GWteB]

If you replace this line
xl.Workbooks.Open FileNameFull, UpdateLinks:=3, ReadOnly:=False

with this

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.

 

[PassSystems]

Thank you so much GWteB...

 

[GWteB]

My pleasure