Alrighty guys I have rather a brain mangler for you; I'm trying develop a relatively simple add-in for excel that should read in data from Sheet A in a workbook, create a new or update sheet B to contain a simplified version of said data in sheet A at the press of a button. Below is some example code I'm working on:
好吧,伙计们,我宁愿为你做一个大脑笨蛋;我正在尝试为excel开发一个相对简单的加载项,它应该读取工作簿中的工作表A中的数据,创建一个新的或更新工作表B,只需按一下按钮就可以在工作表A中包含所述数据的简化版本。下面是我正在研究的一些示例代码:
Application.SendKeys("{ENTER}"); // Exit edit mode
Excel.Workbook wb = this.Application.ActiveWorkbook;
Excel.Worksheet sheetA = null;
Excel.Worksheet sheetB = null;
foreach (Excel.Worksheet sheet in wb.Worksheets) {
// Assume origin sheet we want to move from is same name as book name
if (sheet.Name == wb.Name)
sheetA = sheet;
// Sheet to move to will be called review. Clean if it exists.
else if (sheet.Name == "Review")
{
sheetB = sheet;
sheetB.Cells.ClearContents();
}
}
// If origin sheet cannot be found, assume it's the first sheet
if (sheetA == null)
sheetA = (Excel.Worksheet)wb.Worksheets[1];
// Add the review sheet after the origin sheet if it doesn't exist
if (sheetB == null)
{
sheetB = wb.Worksheets.Add(After: sheetA);
sheetB.Name = "Review";
}
// Simply copy across the value of the first cell
sheetB.Range["A1"].Value2 = sheetA.Range["A1"].Value2;
Now the outcomes of this code seem to be radically different depending on whether anything is in "edit mode" (cells are being edited) or not. If not, all is well as you'd expect, a new sheet is created in the correct position and the cell populated.
现在这个代码的结果似乎完全不同,这取决于是否有任何东西处于“编辑模式”(正在编辑单元格)。如果没有,一切都如您所期望的那样,在正确的位置创建一个新工作表并填充单元格。
If a cell is being edited though, the edited cell is moved to another worksheet in the workbook. If there is no other sheet to move to a COMException
with HRESULT: 0x800A03EC
is thrown (error unknown).
如果正在编辑单元格,则编辑的单元格将移动到工作簿中的另一个工作表。如果没有其他工作表移动到具有HRESULT的COMException:0x800A03EC被抛出(错误未知)。
This error shows up A LOT and it's really frustrating with it essentially telling you "be damned if I know", so any ideas would be appreciated. The most common thing seems to be "worksheet doesn't exist" which would be the case here, but I can't tell why it wants to move the edited cell in the first place?
这个错误显示了很多,它真的很令人沮丧,因为它基本上告诉你“如果我知道就该被诅咒”,所以任何想法都会受到赞赏。最常见的事情似乎是“工作表不存在”,这就是这里的情况,但我不知道为什么它想要首先移动编辑过的单元格?
1 个解决方案
#1
1
Solution found. Simulating a return key stroke (first line of my example) does exit edit mode, but a delay is required for the application to process it. My implementation worked out as:
找到解决方案模拟返回键笔划(我的示例的第一行)确实退出编辑模式,但应用程序需要延迟处理它。我的实现是:
Application.SendKeys("{ENTER}"); // Exit edit mode
Timer timer = new Timer(100); // 100ms delay
timer.AutoReset = false; // Stop the timer looping and re-executing
timer.Elapsed += new ElapsedEventHandler((Sender, ent) =>
{
// Code you want to execute outside of edit mode
});
timer.Start(); // Start her up!
Hope that helps some lost wandering soul!
希望能帮助一些失去游荡的灵魂!
#1
1
Solution found. Simulating a return key stroke (first line of my example) does exit edit mode, but a delay is required for the application to process it. My implementation worked out as:
找到解决方案模拟返回键笔划(我的示例的第一行)确实退出编辑模式,但应用程序需要延迟处理它。我的实现是:
Application.SendKeys("{ENTER}"); // Exit edit mode
Timer timer = new Timer(100); // 100ms delay
timer.AutoReset = false; // Stop the timer looping and re-executing
timer.Elapsed += new ElapsedEventHandler((Sender, ent) =>
{
// Code you want to execute outside of edit mode
});
timer.Start(); // Start her up!
Hope that helps some lost wandering soul!
希望能帮助一些失去游荡的灵魂!