Apr 22 2020 12:30 PM
Apr 22 2020 02:28 PM
@AldighaithirMM there's no "good" way of doing this without VBA. Here is a helpful link to tell you about how to do this:
https://exceloffthegrid.com/getting-values-from-a-closed-excel-workbook/
Here is some code to do what you want. Change the values to your book and sheet name, and range desired. Data will be pulled into an array (single value if a single cell passed). There are other methods but I've found this to be the fastest.
Option Explicit
Sub Test_ExtractDataFromClosedFile()
Dim Values As Variant
Values = ExtractDataFromClosedFile("C:\Users\Zack\Desktop\Test.xlsx", "Sheet1", "A1:B10")
Stop
End Sub
Public Function ExtractDataFromClosedFile( _
ByVal FilePath As String, _
ByVal SheetName As String, _
ByVal RangeAddress As String _
) As Variant
Dim Book As Workbook
Dim Sheet As Worksheet
Dim Range As Range
Dim BookOpen As Boolean
Dim BookName As String
Dim Values As Variant
BookName = GetNameFromPath(FilePath)
If IsWorkbookOpen(BookName) Then
BookOpen = True
Set Book = Workbooks(BookName)
Else
Set Book = Workbooks.Open(FilePath, False)
End If
If WorksheetExists(SheetName, Book) Then
ExtractDataFromClosedFile = Book.Worksheets(SheetName).Range(RangeAddress).Value
End If
If Not BookOpen Then
Book.Close False
End If
End Function
Public Function GetNameFromPath( _
ByVal Path As String _
) As String
Dim Position As Long
Position = InStrRev(Path, GetPathSeparatorSafe)
If Position > 0 Then
GetNameFromPath = Mid(Path, Position + 1)
Else
GetNameFromPath = Path
End If
End Function
Public Function GetPathSeparatorSafe()
Dim EnableEvents As Boolean
EnableEvents = Application.EnableEvents
Application.EnableEvents = False
GetPathSeparatorSafe = Application.PathSeparator
Application.EnableEvents = EnableEvents
End Function
Function IsWorkbookOpen( _
ByVal WorkbookName As String _
) As Boolean
On Error Resume Next
IsWorkbookOpen = CBool(Len(Workbooks(WorkbookName).Name) <> 0)
On Error GoTo 0
End Function
Function WorksheetExists( _
ByVal SheetName As String, _
Optional TargetBook As Workbook _
) As Boolean
If TargetBook Is Nothing Then
If ActiveWorkbook Is Nothing Then Exit Function
Set TargetBook = ActiveWorkbook
End If
On Error Resume Next
WorksheetExists = CBool(Len(TargetBook.Worksheets(SheetName).Name) <> 0)
On Error GoTo 0
End Function
HTH
Apr 22 2020 03:53 PM
Apr 22 2020 04:17 PM
Solution@AldighaithirMM sure. Code is in 'Extract Test.xlsm'. Values are from 'Test.xlsx'. Change variables to suit.
Apr 22 2020 04:17 PM
Solution@AldighaithirMM sure. Code is in 'Extract Test.xlsm'. Values are from 'Test.xlsx'. Change variables to suit.