Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 workbooks open at same time break macros
I have a workbook that has many custom functions built into it. One
of the employees here found a bug that I am not sure how to resolve. The spreadsheet is used to do estimates for the services we sell. So we have a template and do a file-save-as to keep record of all the estimates we have sent out. If you have two of the excel estimate files open at the same time then the the functions get all messed up and give you the wrong results in both sheets. Can someone advise me on where I might start to track down this problem. I assume it has something to do with the way the functions are setup and maybe even how the variables are declared within each function, but I am not sure. I should note one test I did. I double clicked to open excel then do file-open to open the first file. Next I double clicked to open excel again then did file-open to open the second file. This method seems to work fine, but does not seem like a good solution. It is too easy to forget that you have to start excel a second time. Plus I would like to better understand the problem. Thanks in advance. Bk |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 workbooks open at same time break macros
Well... without actually seeing the code it's impossible to really sa
exactly what's going on, but you're probably exactly right. There ar ways to point to certain objects that are not specific to any certai object. For example, you can make changes to "ActiveWorkbook." Thi is something that works fine if you know for sure that when your cod is run the workbook you want will definitely be the active one. Ther are plenty of other cases I could mention that would work or not wor depending on the currently active workbook or worksheet. Unless yo feel like posting your code I don't know what to say beyond that othe than to make it more specific. - Piku -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 workbooks open at same time break macros
Hi
you're probably not referencing the workbook/worksheet You may post the relevant part of your macro -- Regards Frank Kabel Frankfurt, Germany Brian Kranson wrote: I have a workbook that has many custom functions built into it. One of the employees here found a bug that I am not sure how to resolve. The spreadsheet is used to do estimates for the services we sell. So we have a template and do a file-save-as to keep record of all the estimates we have sent out. If you have two of the excel estimate files open at the same time then the the functions get all messed up and give you the wrong results in both sheets. Can someone advise me on where I might start to track down this problem. I assume it has something to do with the way the functions are setup and maybe even how the variables are declared within each function, but I am not sure. I should note one test I did. I double clicked to open excel then do file-open to open the first file. Next I double clicked to open excel again then did file-open to open the second file. This method seems to work fine, but does not seem like a good solution. It is too easy to forget that you have to start excel a second time. Plus I would like to better understand the problem. Thanks in advance. Bk |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 workbooks open at same time break macros
Brian,
Just to add to the good advice you've already received. The referencing of objects (workbooks, worksheets, ranges, etc.) may be ambiguous in the code. Statements like: ActiveSheet, ActiveCell, are based upon whichever sheet or cell is currently in focus. You may need to consider tightening up the code so that all of the objects are explicitly defined within their collections. This isn't to say ActiveSheet is bad, but used improperly, it can lead to unexpected results. One very helpful concept is the use of "ThisWorkbook". It specificly refers to the workbook that owns the macro code that is running. For example the following line will refer to only one cell, regardless of how many other workbooks are open: ThisWorkbook.Worksheets(1).Range("A1").Value Whereas the following lines can refer to cell A1 in many possible worksheets or workbooks: ActiveSheet.Range("A1").Value Range("A1").Value Another thought, is that if you can't solve the conflicts (although I would expect that you could), you may want to put some code in the workbook that checks to see as the workbook is opened if the current Excel session already has a similar workbook open. If so, warn the user, instruct them to open it in another Excel session, and close the second copy of the workbook. Personally, I would fix the code instead of using this crutch but it is another layer of protection. How many lines of code and how many functions are we talking? Troy "Brian Kranson" wrote in message om... I have a workbook that has many custom functions built into it. One of the employees here found a bug that I am not sure how to resolve. The spreadsheet is used to do estimates for the services we sell. So we have a template and do a file-save-as to keep record of all the estimates we have sent out. If you have two of the excel estimate files open at the same time then the the functions get all messed up and give you the wrong results in both sheets. Can someone advise me on where I might start to track down this problem. I assume it has something to do with the way the functions are setup and maybe even how the variables are declared within each function, but I am not sure. I should note one test I did. I double clicked to open excel then do file-open to open the first file. Next I double clicked to open excel again then did file-open to open the second file. This method seems to work fine, but does not seem like a good solution. It is too easy to forget that you have to start excel a second time. Plus I would like to better understand the problem. Thanks in advance. Bk |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 workbooks open at same time break macros
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Break a link between workbooks when there is no "break" option | Excel Discussion (Misc queries) | |||
Can't Open Two Workbooks at the Same Time | Excel Discussion (Misc queries) | |||
Keeping 2 Excel workbooks open at the same time | Excel Discussion (Misc queries) | |||
Excel 2003 Workbooks.Open with CorruptLoad=xlRepairFile fails on Excel 5.0/95 file due to Chart, with Error 1004 Method 'Open' of object 'Workbooks' failed | Excel Programming | |||
Can I have a loop to open a set of workbooks get some data, close it one a time. | Excel Programming |