Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Updating master workbook from source that may/may not exist
I am using Excel 2003 and have a master workbook that has many
references (links) to various other workbooks that are in Excel 4.0 format. These source files are created from a query each month. Depending on the time of the year the source workbook may not exist yet. I wrote a function (fileexists) in VBA that checks for the exisatence of the source file. If it exists the function returns true and then the I use the link to pull in the value to the master workbook. If it doesn't exist I put a space in the cell instead. Here's an example from a cell in the master workbook: =IF(fileexists("managerstmtmo01.xls"),'[managerstmtmo01.xls]managerstmtmo01'!$D$10," ") This all works great except I've noticed one problem. If the cell in my master workbook was once updated because the source file existed, but then I've renamed or deleted the source file, the value it originally pulled in from the source file remains in the master workbook's cell. The only way I can remove the previous value is to edit each cell's function. When I do that Excel tries to update the link value and I cancel that, then the cell is blank. Is there a way to have Excel recalculate each cell and put a space in the cell if the source file doesn't exist? When I open the master workbook I have it Update the links then it tells me there are links that can't be updated (because the source file doesn't exist). Regardless of either telling Excel to continue or edit the links and not update them, the same thing happens. The cells in the master workbook are not changed. I've also set Update Remote References to yes and Save External Link Values to no on the Calculations tab of Options. Can I do what I'm trying to do or is there another way to accomplish this? Thanks. |
#2
|
|||
|
|||
wrote in message ups.com... I am using Excel 2003 and have a master workbook that has many references (links) to various other workbooks that are in Excel 4.0 format. These source files are created from a query each month. Depending on the time of the year the source workbook may not exist yet. I wrote a function (fileexists) in VBA that checks for the exisatence of the source file. If it exists the function returns true and then the I use the link to pull in the value to the master workbook. If it doesn't exist I put a space in the cell instead. Here's an example from a cell in the master workbook: =IF(fileexists("managerstmtmo01.xls"),'[managerstmtmo01.xls]managerstmtmo01' !$D$10," ") This all works great except I've noticed one problem. If the cell in my master workbook was once updated because the source file existed, but then I've renamed or deleted the source file, the value it originally pulled in from the source file remains in the master workbook's cell. The only way I can remove the previous value is to edit each cell's function. When I do that Excel tries to update the link value and I cancel that, then the cell is blank. Is there a way to have Excel recalculate each cell and put a space in the cell if the source file doesn't exist? When I open the master workbook I have it Update the links then it tells me there are links that can't be updated (because the source file doesn't exist). Regardless of either telling Excel to continue or edit the links and not update them, the same thing happens. The cells in the master workbook are not changed. I've also set Update Remote References to yes and Save External Link Values to no on the Calculations tab of Options. Can I do what I'm trying to do or is there another way to accomplish this? Thanks. Why not create a User Defined Function that does this? In the example, you have to enter the text "managerstmtmo01.xls" twice. You could write a function that takes workbook name, worksheet name, row number and column number as parameters. Build your reference from within the function itself and use Application.Evaluate to get the value in the external sheet. I found something that made it possible to refer to external sheets even if they are not open. You have to use the ExecuteExcel4Macro to do so. Search the Excel newsgroups for this word and I think you will find something useful 7Fredrik |
#3
|
|||
|
|||
I actually started to do this after I posted this question yesterday. I
seem to be running up against another problem now. I have the following procedures/functions in my workbook. I'm using ExecuteExcel4Macro because I don't know if the source workbook will be open when this workbook is open. Here's what I have: --------------------------------------------------------------------------------------------- Option Explicit Function FileExists(fname) As Boolean ' Return TRUE if the file exists in current directory Dim Sep As String Dim ThisDocsFullName As String Dim ThisDocsPath As String Dim SearchDocsFullName As String On Error Resume Next Sep = Application.PathSeparator ThisDocsFullName = Application.ActiveWorkbook.FullName ThisDocsPath = Left(ThisDocsFullName, InStrRev(ThisDocsFullName, Sep)) SearchDocsFullName = ThisDocsPath & fname FileExists = Dir(SearchDocsFullName) < "" End Function Sub TestFileExists() Dim name As String Dim sheet As String Dim ref As String Dim test As String name = "managerstmtmo01.xls" sheet = "managerstmtmo01" ref = "D10" test = GetSourceData(name, sheet, ref) MsgBox test End Sub Private Function GetValue(path, file, sheet, ref) ' Return a value from a closed workbook Dim arg As String arg = "'" & path & "[" & file & "]" & sheet & "'!" & _ Range(ref).Range("A1").Address(, , xlR1C1) GetValue = ExecuteExcel4Macro(arg) End Function Function GetSourceData(fname, sname, cell) As String ' Return value from source file if exists, otherwise return error Dim Sep As String Dim ThisDocsFullName As String Dim ThisDocsPath As String Dim name, sheet, ref As String GetSourceData = "" Sep = Application.PathSeparator ThisDocsFullName = Application.ActiveWorkbook.FullName ThisDocsPath = Left(ThisDocsFullName, InStrRev(ThisDocsFullName, Sep)) If FileExists(name) Then GetSourceData = GetValue(ThisDocsPath, fname, sname, cell) End If End Function --------------------------------------------------------------------------------------------- When I run the TestFileExists procedure everything works fine. It returns the value from the spreadsheet. However, when I put the function in a cell in the workbook like this: =getsourcedata("managerstmtmo01.xls","managerstmtm o01","D10") it returns a #VALUE! error. After debugging both the TestFileExists procedure and the getsourcedata function it seems like there's a problem with what's being returned from ExecuteExcel4Macro. When run through the procedure I can display the value of GetValue and it's the correct data. But when I run just the getsourcedata function I can't see what the GetValue value is. I get an "Out of Context" message for the value. Any idea what may be causing this problem? Thanks. |
#4
|
|||
|
|||
I will read your previous post carefully. Here is the mail that I was
talking about /Fredrik "I recently found out how to read values from a sheet in another closed workbook using the GetValue function I have pasted below. It builds a string and then calls ExecuteExcel4Macro to return the value from the other sheet. In my application, all the values in the sheet being read are integers and I am trying to store the values I read as integers so I can do math operations on them. However, if I try to cast the return value as an integer I get a type mismatch error. (example: temp = Val(GetValue(.....)) returns an error). Can someone please advise me on how I can access these values as integers. Thanks! This is a minor adaptation I based on John Walkenbach's GetValue() function so that works cross-platform: Public Function GetValue(Path, File, Sheet, Ref) As Variant 'Based on John Walkenbach's GetValue function: 'http://www.j-walk.com/ss/excel /tips/tip82.htm Const sTEMPLATE As String = "'&P[&F]&S'!&R" Dim sSEP As String Dim sArg As String sSEP = Application.PathSeparator If Right(Path, 1) < sSEP Then Path = Path & sSEP If Dir(Path & File) = "" Then GetValue = "File Not Found" Else With Application sArg = .Substitute(.Substitute(.Subst itute(.Substitute( _ sTEMPLATE, "&R", Range(Ref).Address(True, True, xlR1C1)), _ "&S", Sheet), "&F", File), "&P", Path) End With GetValue = ExecuteExcel4Macro(sArg) End If End Function Note that it uses an XL4M command. It cannot be used from the worksheet, but works fine when called by a macro." |
#5
|
|||
|
|||
wrote in message oups.com... I actually started to do this after I posted this question yesterday. I seem to be running up against another problem now. I have the following procedures/functions in my workbook. I'm using ExecuteExcel4Macro because I don't know if the source workbook will be open when this workbook is open. Here's what I have: -------------------------------------------------------------------------- ------------------- Option Explicit Function FileExists(fname) As Boolean ' Return TRUE if the file exists in current directory Dim Sep As String Dim ThisDocsFullName As String Dim ThisDocsPath As String Dim SearchDocsFullName As String On Error Resume Next Sep = Application.PathSeparator ThisDocsFullName = Application.ActiveWorkbook.FullName ThisDocsPath = Left(ThisDocsFullName, InStrRev(ThisDocsFullName, Sep)) SearchDocsFullName = ThisDocsPath & fname FileExists = Dir(SearchDocsFullName) < "" End Function Sub TestFileExists() Dim name As String Dim sheet As String Dim ref As String Dim test As String name = "managerstmtmo01.xls" sheet = "managerstmtmo01" ref = "D10" test = GetSourceData(name, sheet, ref) MsgBox test End Sub Private Function GetValue(path, file, sheet, ref) ' Return a value from a closed workbook Dim arg As String arg = "'" & path & "[" & file & "]" & sheet & "'!" & _ Range(ref).Range("A1").Address(, , xlR1C1) GetValue = ExecuteExcel4Macro(arg) End Function Function GetSourceData(fname, sname, cell) As String ' Return value from source file if exists, otherwise return error Dim Sep As String Dim ThisDocsFullName As String Dim ThisDocsPath As String Dim name, sheet, ref As String GetSourceData = "" Sep = Application.PathSeparator ThisDocsFullName = Application.ActiveWorkbook.FullName ThisDocsPath = Left(ThisDocsFullName, InStrRev(ThisDocsFullName, Sep)) If FileExists(name) Then GetSourceData = GetValue(ThisDocsPath, fname, sname, cell) End If End Function -------------------------------------------------------------------------- ------------------- When I run the TestFileExists procedure everything works fine. It returns the value from the spreadsheet. However, when I put the function in a cell in the workbook like this: =getsourcedata("managerstmtmo01.xls","managerstmtm o01","D10") it returns a #VALUE! error. After debugging both the TestFileExists procedure and the getsourcedata function it seems like there's a problem with what's being returned from ExecuteExcel4Macro. When run through the procedure I can display the value of GetValue and it's the correct data. But when I run just the getsourcedata function I can't see what the GetValue value is. I get an "Out of Context" message for the value. Any idea what may be causing this problem? Thanks. Your code is very differnt from what I should have written. First of all, you should change this line: Private Function GetValue(path, file, sheet, ref) to something like this Private Function GetValue(ByVal path As String , ByVal file As String , ByVal sheet As String , ref) As Variant IO don't quite understand how you use ref. Is it a range?? What does the code below do? Range(ref).Range("A1").Address(, , xlR1C1) In addition you should use an ErrorHandler Private Function GetValue(... On Error GoTo ErrHandler 'Your code here GetValue = whatever exit function ErrHandler GetValue = Err.Description End Function /Fredrik |
#6
|
|||
|
|||
Okay - I changed my GetValue function to read the way you suggested and
I get this error returned: "Application-defined or object-defined error" when I execute the GetSourceData function. But everything works fine when I run the TestFileExists procedure that calls GetSourceData. The error is coming from the ExecuteExcel4Macro(arg) line. Is there a reason this returns an error when called from a function put in a cell and works when called from a function that's called from a procedure? Thanks. |
#7
|
|||
|
|||
wrote in message oups.com... Okay - I changed my GetValue function to read the way you suggested and I get this error returned: "Application-defined or object-defined error" when I execute the GetSourceData function. But everything works fine when I run the TestFileExists procedure that calls GetSourceData. The error is coming from the ExecuteExcel4Macro(arg) line. Is there a reason this returns an error when called from a function put in a cell and works when called from a function that's called from a procedure? Thanks. None that I can think of right now. Can you show the value of arg when you get the error? /Fredrik |
#8
|
|||
|
|||
Here's my modified function:
Private Function GetValue(ByVal path As String, ByVal file As String, ByVal sheet As String, ref) As Variant ' Return a value from a workbook Const template As String = "'&P[&F]&S'!&R" Dim arg As String Dim sep As String On Error GoTo ErrHandler sep = Application.PathSeparator If Right(path, 1) < sep Then path = path & sep If Dir(path & file) = "" Then GetValue = "File Not Found" Else With Application arg = .Substitute(.Substitute(.Substitute(.Substitute( _ template, "&R", Range(ref).Address(True, True, xlR1C1)), _ "&S", sheet), "&F", file), "&P", path) End With GetValue = ExecuteExcel4Macro(arg) End If Exit Function ErrHandler: GetValue = Err.Description End Function And this is the value of arg when called from a procedu "'C:\Documents and Settings\jlewis\My Documents\Financials\[managerstmtmo01.xls]managerstmtmo01'!R10C4" And this is the value of arg when called from a function within the cell: "'C:\Documents and Settings\jlewis\My Documents\Financials\[managerstmtmo01.xls]managerstmtmo01'!R10C4" Looks pretty much the same to me. In the documentation for ExecuteExcel4Macro it says that the function cannot be used from the worksheet (because it uses the XL4M command) but works fine when called by a macro. Is there a problem calling it from another function that's used from the worksheet? Thanks. |
#9
|
|||
|
|||
wrote in message oups.com... Here's my modified function: Private Function GetValue(ByVal path As String, ByVal file As String, ByVal sheet As String, ref) As Variant ' Return a value from a workbook Const template As String = "'&P[&F]&S'!&R" Dim arg As String Dim sep As String On Error GoTo ErrHandler sep = Application.PathSeparator If Right(path, 1) < sep Then path = path & sep If Dir(path & file) = "" Then GetValue = "File Not Found" Else With Application arg = .Substitute(.Substitute(.Substitute(.Substitute( _ template, "&R", Range(ref).Address(True, True, xlR1C1)), _ "&S", sheet), "&F", file), "&P", path) End With GetValue = ExecuteExcel4Macro(arg) End If Exit Function ErrHandler: GetValue = Err.Description End Function And this is the value of arg when called from a procedu "'C:\Documents and Settings\jlewis\My Documents\Financials\[managerstmtmo01.xls]managerstmtmo01'!R10C4" And this is the value of arg when called from a function within the cell: "'C:\Documents and Settings\jlewis\My Documents\Financials\[managerstmtmo01.xls]managerstmtmo01'!R10C4" Looks pretty much the same to me. In the documentation for ExecuteExcel4Macro it says that the function cannot be used from the worksheet (because it uses the XL4M command) but works fine when called by a macro. Is there a problem calling it from another function that's used from the worksheet? Thanks. Some things can't be called from worksheets. I need to see if I can find anything. The idea with this code is that the file doesn't have to be open, right? /Fredrik |
#10
|
|||
|
|||
I don't know if this sheds any light on any of this but the error I
told you about I got while debugging. When I try to run the function without debugging I get this error returned: Method 'ExecuteExcel4Macro' of object '_Global' failed Not sure why the error is different depending on if I'm debugging or just executing the function. Maybe this helps??? |
#11
|
|||
|
|||
wrote in message oups.com... I don't know if this sheds any light on any of this but the error I told you about I got while debugging. When I try to run the function without debugging I get this error returned: Method 'ExecuteExcel4Macro' of object '_Global' failed Not sure why the error is different depending on if I'm debugging or just executing the function. Maybe this helps??? Maybe. I shall try to make a small macro to do something similar. I have made a macro for a friend where I use the Evaluate function instead. It works well but the sheets have to be open for it to work. I now feel I have some more time since I now have a new job. It's so nice not being unemployed. /Fredrik |
#12
|
|||
|
|||
Thanks - I appreciate all your help. As for the "being unemployed" I
understand. Been there, done that and I agree - not as nice as being employed. |
#13
|
|||
|
|||
I haven't followed this thread too closely, but that UDF based on John
Walkenbach's code won't work if it's called by a formula in a worksheet. But you may want to look at how Harlan Grove worked around using =indirect() with closed workbooks. http://www.google.com/groups?selm=sH...wsranger. com Fredrik Wahlgren wrote: wrote in message oups.com... I don't know if this sheds any light on any of this but the error I told you about I got while debugging. When I try to run the function without debugging I get this error returned: Method 'ExecuteExcel4Macro' of object '_Global' failed Not sure why the error is different depending on if I'm debugging or just executing the function. Maybe this helps??? Maybe. I shall try to make a small macro to do something similar. I have made a macro for a friend where I use the Evaluate function instead. It works well but the sheets have to be open for it to work. I now feel I have some more time since I now have a new job. It's so nice not being unemployed. /Fredrik -- Dave Peterson |
#14
|
|||
|
|||
I thought this function you pointed me to would work but I'm still
having problems. I have my function set almost exactly as it is in the post. I had to change the comparison to ErrRef to ErrValue and it executed the rest of the code. Unfortunately when it came to the "For Each c In r" the value being returned by xlapp.ExecuteExcel4Macro is "empty" and I'm not getting my value. This is what I have: Function pull(xref As String) As Variant Dim xlapp As Object, xlwb As Workbook Dim b As String, r As Range, c As Range, n As Long, arg As String pull = Evaluate(xref) If CStr(pull) = CStr(CVErr(xlErrValue)) Then On Error GoTo CleanUp Set xlapp = CreateObject("Excel.Application") Set xlwb = xlapp.Workbooks.Add On Error Resume Next n = InStr(InStr(1, xref, "]") + 1, xref, "!") b = Mid(xref, 1, n) Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1)) If r Is Nothing Then pull = xlapp.ExecuteExcel4Macro(xref) Else For Each c In r c.Value = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1)) Next c pull = r.Value End If CleanUp: If Not xlwb Is Nothing Then xlwb.Close 0 If Not xlapp Is Nothing Then xlapp.Quit Set xlapp = Nothing End If End Function And I call the function in my cell with this: =pull(""&F1&"\["&C1&"]"&D1&"'!D10") where F1 references the path, c1 references the file and d1 references the sheet name. Any reason this returns "empty"? Thanks. |
#15
|
|||
|
|||
And as a follow-up this new function does work when I call it from a
procedure (as did the other function). Neither function works when called from a cell in a workbook. |
#16
|
|||
|
|||
wrote in message ups.com... And as a follow-up this new function does work when I call it from a procedure (as did the other function). Neither function works when called from a cell in a workbook. If the same string is evaluated by ExecuteExcel4Macro, that settles it. It seems as if this function only works when called from a procedure /Fredrik |
#17
|
|||
|
|||
That function works ok for me when called from a worksheet cell.
What did your formula look like? " wrote: I thought this function you pointed me to would work but I'm still having problems. I have my function set almost exactly as it is in the post. I had to change the comparison to ErrRef to ErrValue and it executed the rest of the code. Unfortunately when it came to the "For Each c In r" the value being returned by xlapp.ExecuteExcel4Macro is "empty" and I'm not getting my value. This is what I have: Function pull(xref As String) As Variant Dim xlapp As Object, xlwb As Workbook Dim b As String, r As Range, c As Range, n As Long, arg As String pull = Evaluate(xref) If CStr(pull) = CStr(CVErr(xlErrValue)) Then On Error GoTo CleanUp Set xlapp = CreateObject("Excel.Application") Set xlwb = xlapp.Workbooks.Add On Error Resume Next n = InStr(InStr(1, xref, "]") + 1, xref, "!") b = Mid(xref, 1, n) Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1)) If r Is Nothing Then pull = xlapp.ExecuteExcel4Macro(xref) Else For Each c In r c.Value = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1)) Next c pull = r.Value End If CleanUp: If Not xlwb Is Nothing Then xlwb.Close 0 If Not xlapp Is Nothing Then xlapp.Quit Set xlapp = Nothing End If End Function And I call the function in my cell with this: =pull(""&F1&"\["&C1&"]"&D1&"'!D10") where F1 references the path, c1 references the file and d1 references the sheet name. Any reason this returns "empty"? Thanks. -- Dave Peterson |
#18
|
|||
|
|||
"Dave Peterson" wrote in message ... That function works ok for me when called from a worksheet cell. What did your formula look like? I have missed something. This has been a very long thread. 7Fredrik |
#19
|
|||
|
|||
I had suggested that Harlan Grove's =pull() UDF works from a cell on a
worksheet. http://www.google.com/groups?selm=sH...wsranger. com The OP said it didn't work for him. Fredrik Wahlgren wrote: "Dave Peterson" wrote in message ... That function works ok for me when called from a worksheet cell. What did your formula look like? I have missed something. This has been a very long thread. 7Fredrik -- Dave Peterson |
#20
|
|||
|
|||
Okay - I think I finally got it to work. It actually was something
simple - I had double quotation marks around the parameter being passed to the pull function - which is wrong. It should be a single quotation mark at the beginning around the path, file name and sheet name, ending before the ! and cell reference. Once I took out the double quotation marks it works fine. I misread it on the previous post. I just have one other question - in Harlan Groves function he is checking for the cell to have a #REF error in it before pulling the value from another workbook. Any reason to do this? This was also giving me some problems at first - sometimes I would have a #REF error and sometimes I would have a #VALUE error. Any reason to have to check for these errors at all? Once agin, thanks to all who have helped me with this. |
#21
|
|||
|
|||
Harlan checks to see if the returned value is #ref. If it doesn't return that
error, then it means that the workbook is already open and you don't need his function. But even worse, I gave you a link to an older version (sorry). http://groups.google.co.uk/groups?se...wsranger.co m should work better. " wrote: Okay - I think I finally got it to work. It actually was something simple - I had double quotation marks around the parameter being passed to the pull function - which is wrong. It should be a single quotation mark at the beginning around the path, file name and sheet name, ending before the ! and cell reference. Once I took out the double quotation marks it works fine. I misread it on the previous post. I just have one other question - in Harlan Groves function he is checking for the cell to have a #REF error in it before pulling the value from another workbook. Any reason to do this? This was also giving me some problems at first - sometimes I would have a #REF error and sometimes I would have a #VALUE error. Any reason to have to check for these errors at all? Once agin, thanks to all who have helped me with this. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple workbook user's with Master workbook - all password protected | Excel Discussion (Misc queries) | |||
Master Workbook used as my template? | New Users to Excel | |||
Winn98SE, Excel2000: ODBC query opens the source workbook | Excel Discussion (Misc queries) | |||
Updating multiple worksheets in a large workbook | Excel Discussion (Misc queries) | |||
XL2003 Destination and Source Open but not updating | Excel Discussion (Misc queries) |