Get data from closed workbook for use in a variable (no copying)

[Bob Zimski]

All the threads on this subject are about copying data from a closed workbook
to an active workbook. What I would like to do is just pickup the value of a
specific cell in a specific sheet in a closed workbook and use that value in
a variable for processing purposes. What can I use to do this?

Thanks

Bob

 

[RB Smissaert]

This should do the job:


Sub test()

MsgBox GetValueFromWB("C:\", "WB_Value_Test.xls", "Sheet1", "B2")

End Sub

Function GetValueFromWB(strPath As String, _
strFile As String, _
strSheet As String, _
strRef As String) As Variant

'Retrieves a value from a closed workbook
'----------------------------------------
Dim strArg As String

'make sure we have the trailing backslash
'----------------------------------------
If Right$(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If

'Make sure the file exists
'-------------------------
If bFileExists(strPath & strFile) = False Then
GetValueFromWB = "File Not Found"
Exit Function
End If

'Create the argument
'-------------------
strArg = "'" & strPath & "[" & strFile & "]" & strSheet & "'!" & _
Range(strRef).Range("A1").Address(, , xlR1C1)

'Execute an XLM macro
'--------------------
GetValueFromWB = ExecuteExcel4Macro(strArg)

End Function

Function bFileExists(ByVal sFile As String) As Boolean

Dim lAttr As Long

On Error Resume Next
lAttr = GetAttr(sFile)
bFileExists = (Err.Number = 0) And ((lAttr And vbDirectory) = 0)
On Error GoTo 0

End Function


RBS

 

[john]

You can do it using Formula.

Give this a try & see if works for you.

Sub GetValue()
Dim mydata As String
'data location & range to copy
mydata = "='C:\[Mybook.xls]Sheet1'!$B$2" '<< change as required

'link to worksheet
With ThisWorkbook.Worksheets(1).Range("B2:") '<< change as required
.Formula = mydata

'convert formula to text
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
End Sub

 

[Bob Zimski]

It works like a charm.

Wow, I guess there is not direct function.

I needed to do this because I wanted to store specifc defaults in a separate
workbook for various macros I have where things may be different based onthe
user.

Thanks much!

Bob

This should do the job:


Sub test()

MsgBox GetValueFromWB("C:\", "WB_Value_Test.xls", "Sheet1", "B2")

End Sub

Function GetValueFromWB(strPath As String, _
strFile As String, _
strSheet As String, _
strRef As String) As Variant

'Retrieves a value from a closed workbook
'----------------------------------------
Dim strArg As String

'make sure we have the trailing backslash
'----------------------------------------
If Right$(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If

'Make sure the file exists
'-------------------------
If bFileExists(strPath & strFile) = False Then
GetValueFromWB = "File Not Found"
Exit Function
End If

'Create the argument
'-------------------
strArg = "'" & strPath & "[" & strFile & "]" & strSheet & "'!" & _
Range(strRef).Range("A1").Address(, , xlR1C1)

'Execute an XLM macro
'--------------------
GetValueFromWB = ExecuteExcel4Macro(strArg)

End Function

Function bFileExists(ByVal sFile As String) As Boolean

Dim lAttr As Long

On Error Resume Next
lAttr = GetAttr(sFile)
bFileExists = (Err.Number = 0) And ((lAttr And vbDirectory) = 0)
On Error GoTo 0

End Function


RBS

All the threads on this subject are about copying data from a closed
workbook
to an active workbook. What I would like to do is just pickup the value of
a
specific cell in a specific sheet in a closed workbook and use that value
in
a variable for processing purposes. What can I use to do this?

Thanks

Bob