INDIRECT.EXT problem with missing worksheets
Ive been using the INDIRECT.EXT function for a while and am finding that I
have a consistent problem when worksheets are missing. Im looking up data in multiple workbooks on the same worksheet name, but find that if the worksheet name does not exist, Excel seems to lock up. There is a message that €œMicrosoft Excel is waiting for another application to complete an OLE action€ that will not go away. When I brute force exit from Excel, a window is displayed asking for me to select another sheet to use instead of the missing worksheet. I dont want to use another sheet if its not there. I'd rather move on or get an error in the cell. In addition, there is an excel workbook with the name YMNQIYASUDYS created each time there is a missing worksheet. Can someone assist? Thanks, Barb Reinhardt |
On Wed, 12 Oct 2005 09:47:03 -0700, "Barb Reinhardt"
wrote: I’ve been using the INDIRECT.EXT function for a while and am finding that I have a consistent problem when worksheets are missing. I’m looking up data in multiple workbooks on the same worksheet name, but find that if the worksheet name does not exist, Excel seems to lock up. There is a message that “Microsoft Excel is waiting for another application to complete an OLE action” that will not go away. When I brute force exit from Excel, a window is displayed asking for me to select another sheet to use instead of the missing worksheet. I don’t want to use another sheet if it’s not there. I'd rather move on or get an error in the cell. In addition, there is an excel workbook with the name YMNQIYASUDYS created each time there is a missing worksheet. Can someone assist? Thanks, Barb Reinhardt That function is part of Laurent Longre's morefunc.xll free add-in. You should first check to see if you have the latest version (check at http://xcell05.free.fr). If you do and are still seeing the problem, navigate to the Forums board at that web site, and post your problem. In the version I have, if a worksheet is missing, the function (as documented) gives a #VALUE! error. --ron |
Ron Rosenfeld wrote...
"Barb Reinhardt" wrote: I've been using the INDIRECT.EXT function for a while and am finding that I have a consistent problem when worksheets are missing. I'm looking up data in multiple workbooks on the same worksheet name, but find that if the worksheet name does not exist, Excel seems to lock up. There is a message that "Microsoft Excel is waiting for another application to complete an OLE action" that will not go away. When I brute force exit from Excel, a window is displayed asking for me to select another sheet to use instead of the missing worksheet. I don't want to use another sheet if it's not there. I'd rather move on or get an error in the cell. In addition, there is an excel workbook with the name YMNQIYASUDYS created each time there is a missing worksheet. .... This looks like the same problem I had a few years ago using Automation and another Excel instance to get data from closed workbooks by entering generated formulas in the second instance. When you enter a cell formula interactively or via macro, if that formula contains token that are syntactically external reference links but the workbook and worksheet combination doesn't exist, Excel *ALWAYS* displays a dialog prompting you to choose another workbook or worksheet. AFAIK, there's no way to disable these dialogs. I finally got around this in my own pull udf by using ExecuteExcel4Macro method calls, which don't cause these dialogs to display when either workbook or worksheet don't exist. Looks like Laurent Longre's INDIRECT.EXT retrogressed from earlier versions. You should first check to see if you have the latest version (check at http://xcell05.free.fr). If you do and are still seeing the problem, navigate to the Forums board at that web site, and post your problem. In the version I have, if a worksheet is missing, the function (as documented) gives a #VALUE! error. I get the same result as the OP using MOREFUNC.XLL version 3.7.1. This was *NOT* the result I used to get using older versions. As I said above, retrogression. Note: bringing up Task Manager while Excel hangs doing this shows two EXCEL.EXE processes, so it seems Laurent Longre's INDIRECT.EXT also uses a separate Excel instance to fetch data from closed workbooks. |
On 12 Oct 2005 11:47:03 -0700, "Harlan Grove" wrote:
Ron Rosenfeld wrote... "Barb Reinhardt" wrote: I've been using the INDIRECT.EXT function for a while and am finding that I have a consistent problem when worksheets are missing. I'm looking up data in multiple workbooks on the same worksheet name, but find that if the worksheet name does not exist, Excel seems to lock up. There is a message that "Microsoft Excel is waiting for another application to complete an OLE action" that will not go away. When I brute force exit from Excel, a window is displayed asking for me to select another sheet to use instead of the missing worksheet. I don't want to use another sheet if it's not there. I'd rather move on or get an error in the cell. In addition, there is an excel workbook with the name YMNQIYASUDYS created each time there is a missing worksheet. ... This looks like the same problem I had a few years ago using Automation and another Excel instance to get data from closed workbooks by entering generated formulas in the second instance. When you enter a cell formula interactively or via macro, if that formula contains token that are syntactically external reference links but the workbook and worksheet combination doesn't exist, Excel *ALWAYS* displays a dialog prompting you to choose another workbook or worksheet. AFAIK, there's no way to disable these dialogs. I finally got around this in my own pull udf by using ExecuteExcel4Macro method calls, which don't cause these dialogs to display when either workbook or worksheet don't exist. Looks like Laurent Longre's INDIRECT.EXT retrogressed from earlier versions. You should first check to see if you have the latest version (check at http://xcell05.free.fr). If you do and are still seeing the problem, navigate to the Forums board at that web site, and post your problem. In the version I have, if a worksheet is missing, the function (as documented) gives a #VALUE! error. I get the same result as the OP using MOREFUNC.XLL version 3.7.1. This was *NOT* the result I used to get using older versions. As I said above, retrogression. Note: bringing up Task Manager while Excel hangs doing this shows two EXCEL.EXE processes, so it seems Laurent Longre's INDIRECT.EXT also uses a separate Excel instance to fetch data from closed workbooks. Interesting. I am running 3.6.2 and, if the workbook does not exist, I get a #VALUE! error. The current version is 3.8; I should download that and try it. --ron |
INDIRECT.EXT problem with missing worksheets
Harlan, Ron, Reinhardt, Harlan Grove a écrit : When you enter a cell formula interactively or via macro, if that formula contains token that are syntactically external reference links but the workbook and worksheet combination doesn't exist, Excel *ALWAYS* displays a dialog prompting you to choose another workbook or worksheet. AFAIK, there's no way to disable these dialogs. I agree with you. I finally got around this in my own pull udf by using ExecuteExcel4Macro method calls, which don't cause these dialogs to display when either workbook or worksheet don't exist. Looks like Laurent Longre's INDIRECT.EXT retrogressed from earlier versions. Indeed. I didn't remember this problem. :-( The bug is fixed now, the next version will come soon (with a lot of new functions!) Cordially, Laurent |
INDIRECT.EXT problem with missing worksheets
On Mon, 17 Oct 2005 01:46:48 +0200, Laurent Longre
wrote: Harlan, Ron, Reinhardt, Harlan Grove a écrit : When you enter a cell formula interactively or via macro, if that formula contains token that are syntactically external reference links but the workbook and worksheet combination doesn't exist, Excel *ALWAYS* displays a dialog prompting you to choose another workbook or worksheet. AFAIK, there's no way to disable these dialogs. I agree with you. I finally got around this in my own pull udf by using ExecuteExcel4Macro method calls, which don't cause these dialogs to display when either workbook or worksheet don't exist. Looks like Laurent Longre's INDIRECT.EXT retrogressed from earlier versions. Indeed. I didn't remember this problem. :-( The bug is fixed now, the next version will come soon (with a lot of new functions!) Cordially, Laurent Thank you for that. Much appreciated --ron |
INDIRECT.EXT problem with missing worksheets
It's done. :-) Laurent Ron Rosenfeld a écrit : On Mon, 17 Oct 2005 01:46:48 +0200, Laurent Longre wrote: Harlan, Ron, Reinhardt, Harlan Grove a écrit : When you enter a cell formula interactively or via macro, if that formula contains token that are syntactically external reference links but the workbook and worksheet combination doesn't exist, Excel *ALWAYS* displays a dialog prompting you to choose another workbook or worksheet. AFAIK, there's no way to disable these dialogs. I agree with you. I finally got around this in my own pull udf by using ExecuteExcel4Macro method calls, which don't cause these dialogs to display when either workbook or worksheet don't exist. Looks like Laurent Longre's INDIRECT.EXT retrogressed from earlier versions. Indeed. I didn't remember this problem. :-( The bug is fixed now, the next version will come soon (with a lot of new functions!) Cordially, Laurent Thank you for that. Much appreciated --ron |
INDIRECT.EXT problem with missing worksheets
On Wed, 19 Oct 2005 00:22:18 +0200, Laurent Longre
wrote: It's done. :-) Laurent Thank you. I will be downloading it shortly. Best, --ron |
All times are GMT +1. The time now is 04:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com