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

B

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
 
R

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
 
J

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
 
B

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

RB Smissaert said:
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


Bob Zimski said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top