Hi,
thanks for the help dave, i feel like i'm closer to the solution now...
i tryed this:
*** CODE ********************************
Option Explicit
Private Function GetValue()
Const PATH As String = "n:\client rep report delivery\bayer\july04dm\sales reps"
Const FILE As String = "BAYER_ED_A101.XLS"
Const SHEET As String = "Territory_Summary"
Const REF As String = "C11"
' Retrieves a value from a closed workbook
Dim arg As String
' Make sure the file exists
If Dir(PATH & "\" & FILE) = "" Then
GetValue = "File Not Found"
Exit Function
End If
' Create the argument
arg = "'" & PATH & "[" & FILE & "]" & SHEET & "'!$c$11"
' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function
*****************************************
i get an error message that there is an error in my formula.
i also tryed this and got the same message...
*****Code************************************
Public Sub GetDataFromClosedFile()
Dim filepath As String
Dim fileName As String
Dim sheetName As String
Dim strg As String
filepath = "n:\client rep report delivery\bayer\july04dm\sales reps"
fileName = "BAYER_ED_A101.XLS"
sheetName = "Territory_Summary"
strg = "'" & filepath & "\[" & fileName & "]" & sheetName & "'!$c$11"
MsgBox ExecuteExcel4Macro(strg)
End Sub
**************************************************
Bilal
First, functions called from worksheet formulas can't update other cells.
They can return something to the cell with the function.
=calculatethis()
worked for me if the other workbook was open. But not if that other workbook
was closed.
If you want to retrieve a value from a closed workbook in VBA, you can use one
of the techniques at John Walkenbach's site:
http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.
If you want to retrieve a value from a closed workbook from a worksheet cell,
you could use a UDF that Harlan Grove wrote:
http://www.google.com/[email protected]
You may have to parse your value to pass it to Harlan's UDF, though.
Billy wrote:
Hi, that did work for the simple example like:
calculateThis = Evaluate("=sum(1+1)")
however when i try to use this solution to evaluate a formula that
refrences another workbook like this:
calculateThis = Evaluate("='C:\[book1.xls]Sheet1'!$A$1")
...It returnes error 2023
question #2
also another thing i noticed with UDF's that i can't explain is when i
try to say rangeA.value = rangeX.value the code simply stops and i
have no idea why no error message or anything.
Thank you
Bilal
Maybe:
Option Explicit
Function calculateThis(Cell_A As Range)
calculateThis = Evaluate(Cell_A.Value)
End Function
Billy wrote:
Hi, I'm new to UDF but not VBA.
I'm trying to take the value from cell A and copy it over to cell B.
-The value in cell A is a formula that generates a formula.
example cell A value: ="=sum(200)"
the actual formula is more complicated than this however this is the
main idea for the sake of keeping it simple.
-The UDF looks like this:
Function calculateThis(Cell_A As Range)
calculateThis = Cell_A.Value
End Function
i've also tryed
calculateThis = Cell_A.formula
calculateThis.value = Cell_A.formula (this one just stops the code
execution)
-Cell B looks like this: = calculateThis(A1)
-the result i get is: =sum(200)
-the result i need is: 200
i need to get the answer and not the formula.
I kind of know what's happening but not sure how to fix it.
I hope my explanation was okay, any help would be appreciated.
thank you
Bilal
