PassSystems
New Member
- Joined
- Sep 19, 2020
- Messages
- 12
- Office Version
- 365
- Platform
- 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
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