Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get data from closed workbook for use in a variable (no copying)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get data from closed workbook for use in a variable (no copying)
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get data from closed workbook for use in a variable (no copying)
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 -- jb "Bob Zimski" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get data from closed workbook for use in a variable (no copyin
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" wrote: 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to pass variable between closed workbook | Excel Programming | |||
Getting data from a closed workbook | Excel Programming | |||
loop through a column on a workbook copying data on each row to another workbook, then copy data back to the original workbook | Excel Programming | |||
PUT data in closed workbook | Excel Programming | |||
Copying Data from closed workbooks | Excel Programming |