I am getting this error:
我收到此错误:
This command requires at least two rows of source data. You cannot use the command on a selection in only one row. Try the following:
- If you're using an advanced filter, select a range of cells that contains at least two rows of data. Then click the Advanced Filter command again.
- I you're creating a PivotTable, type a cell reference or select a range that includes at least two rows of data
intermittently on this line of code:
间歇性地在这行代码上:
xlWorkBook.RefreshAll();
There are two worksheets. One has a pivot table and one has raw data. Sometimes there is only one row of data. For multiple rows of data the line of code above always works; however, for only 1 row of data, the code above sometimes works, and sometimes I get the error message above.
有两个工作表。一个有一个数据透视表,一个有原始数据。有时只有一行数据。对于多行数据,上面的代码行始终有效;但是,对于只有1行数据,上面的代码有时会起作用,有时我会收到上面的错误消息。
In addition to this, the worksheet containing the pivot table is not refreshed; however, if I re-open the file, it also does not refresh, unless I explicitly refresh it manually.
除此之外,包含数据透视表的工作表不会刷新;但是,如果我重新打开文件,它也不会刷新,除非我手动显式刷新它。
What is going on here? Why am I getting this error only sometimes?
这里发生了什么?为什么我有时会收到此错误?
Thank you so much for your guidance.
非常感谢你的指导。
if at all helpful, i am including the entire method:
如果有帮助,我包括整个方法:
private void SortandCreateFile(string column, string email, string emailStartPos) {
string replacetext = "";
try {
var valueRange = xlWorkSheet.get_Range(column + emailStartPos, column + range.Rows.Count.ToString());
var deleteRange = valueRange;
xlApp.Visible = false;
int startpos = 0;
int endPos=0;
bool foundStart = false;
Excel.Range rng = xlWorkSheet.get_Range(column + "1", column + range.Rows.Count.ToString());
string tempstring = "d";
int INTemailStartPos = Convert.ToInt16(emailStartPos);
for (int rCnt = INTemailStartPos; rCnt <= rng.Count; rCnt++) {
Excel.Range cell = (Excel.Range)rng[rCnt, 1];
try {
if (cell.Value2 != null)
tempstring = cell.Value2.ToString();
else {
startpos = rCnt;
releaseObject(cell); /////////
break;
}
}
catch (Exception ee)
{
MessageBox.Show(ee.ToString());
}
//grab the text from column link texdtbox
Excel.Range rngLinkColumn;
Excel.Range replacetextcell=null;
if (FormControls.ColumnLink.Length > 0) {
rngLinkColumn = xlWorkSheet.get_Range(FormControls.ColumnLink + "1", FormControls.ColumnLink + range.Rows.Count.ToString());
replacetextcell = (Excel.Range)rngLinkColumn[rCnt, 1];
}
//locate email
if (cell.Value2.ToString() == email ) {
//we found the starting position of the email we want!
//this will tell us which row of data to start from
startpos = rCnt;
if (FormControls.ColumnLink.Length > 0)
replacetext = replacetextcell.Value2.ToString();
releaseObject(cell); /////////
break;
}
releaseObject(cell);
}
int foundstartminusOnE= startpos - 1;
int rngcount = rng.Count + INTemailStartPos;
//delete everything from the top UNTIL the row of the email address that we need
if (startpos != INTemailStartPos) {
deleteRange = xlWorkSheet.get_Range(column + INTemailStartPos.ToString() + ":" + "CF" + foundstartminusONE.ToString(), Type.Missing);
deleteRange = deleteRange.EntireRow;
deleteRange.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
}
for (int rCnt = INTemailStartPos; rCnt <= rng.Count; rCnt++) {
Excel.Range cell = (Excel.Range)rng[rCnt, 1];
try {
if (cell.Value2 != null )
tempstring = cell.Value2.ToString();
else {
endPos = rCnt - 1;
releaseObject(cell);////////
break;
}
}
catch (Exception ee) {
//MessageBox.Show(ee.ToString());
}
//locate email
if (cell.Value2.ToString() != email ) {
//we found where the last email address is that we need
//this is where the issue is occurring i think with the deleting the last row
endPos = rCnt;
releaseObject(cell);////////
break;
}
releaseObject(cell);
}
//delete all the stuff AFTER the email address that we need
if (endPos != 0) {
deleteRange = xlWorkSheet.get_Range(column + endPos + ":" + "CF" + rngcount.ToString(), Type.Missing);
deleteRange = deleteRange.EntireRow;
deleteRange.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
}
//when the user opens the excel file, we want the focus to be here
var rangehome = xlWorkSheet.get_Range(FormControls.FocusOn, FormControls.FocusOn);
xlWorkSheet.Activate();
rangehome.Select();
string filename = xlWorkBook.Path + @"\" + email + ".xlsx";
string fileSubstring = filename.Substring(0, filename.IndexOf(".xlsx"));
string randomfileString = Guid.NewGuid().ToString("N").Substring(0, 10) + ".xlsx";
string targetfilenameRename = fileSubstring + randomfileString;
//((Excel.Worksheet)this.Application.ActiveWorkbook.Sheets[FormControls.WorksheetFocus]).Activate();
//((Excel.Worksheet)Excel.Application.ActiveWorkbook.Sheets[1]).Activate();
Excel.Worksheet xlWorkSheetFocus = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(FormControls.WorksheetFocus);
xlWorkSheetFocus.Activate();
xlWorkBook.SaveAs(targetfilenameRename, Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
try {
xlWorkBook.RefreshAll();
}
catch { }
xlWorkBook.Save();
string targetfile = xlWorkBook.Path + @"\" + FormControls.FileName + " - "
+ email.Substring(0, email.IndexOf("@")) + ".xlsx";
System.IO.File.Copy(targetfilenameRename, targetfile, true);
string body = FormControls.eMailBody;
body = body.Replace("%replacetext%", replacetext);
//replace %replacetext% in body
string targetfileSubstring = targetfile.Substring(0, targetfile.IndexOf(".xlsx"));
string randomString = Guid.NewGuid().ToString("N").Substring(0, 10)+".xlsx";
string targetfileRename = targetfileSubstring+randomString;
while (true) {
try {
SendEmail(targetfile, email, FormControls.eMailSubject, body,FormControls.eMailFrom);
}
catch (Exception ee) {
MessageBox.Show(ee.ToString());
continue;
}
// all is good
break;
}
releaseObject(valueRange);
releaseObject(deleteRange);
File.Copy(targetfile, targetfileRename, true);
}
catch (Exception e) {
MessageBox.Show(e.ToString());
}
finally {
//DisposeMe();
// Release all COM RCWs.
// The "releaseObject" will just "do nothing" if null is passed,
// so no need to check to find out which need to be released.
// The "finally" is run in all cases, even if there was an exception
// in the "try".
// Note: passing "by ref" so afterwords "xlWorkSheet" will
// evaluate to null. See "releaseObject".
releaseObject(range);
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
// The Quit is done in the finally because we always
// want to quit. It is no different than releasing RCWs.
if (xlApp != null) {
xlApp.Quit();
}
releaseObject(xlApp);
}
}
6
The only way I could replicate this error with a pivot table was by attempting to create one off a range that didn't have column headers, just like on the screenshot from Stephan1010's answer.
我可以使用数据透视表复制此错误的唯一方法是尝试在没有列标题的范围内创建一个,就像Stephan1010的答案截图一样。
In the GetPivotData Excel function, pivot fields are referred to by their names (=GETPIVOTDATA("EmailAddress",$A$3)
); thus, it makes sense to disallow a data source that wouldn't have them.
在GetPivotData Excel函数中,透视字段由它们的名称引用(= GETPIVOTDATA(“EmailAddress”,$ A $ 3));因此,禁止不具备它们的数据源是有意义的。
The solution would be to pivot over a ListObject
instead of a Range
- in Excel when you select, say, range $A$1:$C$1
and format as table (from the Ribbon), the table that results will span $A$1:$C$2
; the contents of the first row becomes the column headers and the second row is a valid, empty record. Interesting to note that this happens (the 2-row span) regardless of whether or not you check the "My table has headers" checkbox (the data will be moved to the first row and the table will contain default "Column1"-"Column2"-"Column3" headers if the checkbox is cleared).
当您选择范围$ A $ 1:$ C $ 1并格式化为表格(来自功能区)时,解决方案是在ListObject而不是Range上进行数据透视 - 结果表格将超过$ A $ 1: $ C $ 2;第一行的内容成为列标题,第二行是有效的空记录。有趣的是,无论您是否选中“我的表有标题”复选框,都会发生这种情况(2行跨度)(数据将移动到第一行,表格将包含默认的“Column1” - “Column2” “ - ”如果清除复选框,则为Column3“标题。
In other words, a ListObject
is always a valid data source for a pivot table, while a Range
may not contain enough rows. Also if you don't have column headers and you create a pivot table with range $A$1:$C$2
, the record at $A$1:$C$1
will be used as column headers, which means that first record is lost.
换句话说,ListObject始终是数据透视表的有效数据源,而Range可能不包含足够的行。此外,如果您没有列标题并且创建范围为$ A $ 1:$ C $ 2的数据透视表,则$ A $ 1:$ C $ 1的记录将用作列标题,这意味着第一条记录将丢失。
From the code you have supplied I would presume the pivot table is already present and connected to some [named?] range in a template workbook that contains the macro. Turning your range into a table might be as trivial as selecting format as table from the Ribbon. And then you could have code like this to remove all unnecessary rows while still keeping a valid data source for the pivot table:
从你提供的代码我可以假设数据透视表已经存在并连接到包含宏的模板工作簿中的某个[named?]范围。将范围转换为表可能与从Ribbon中选择格式作为表格一样简单。然后,您可以使用这样的代码删除所有不必要的行,同时仍保留数据透视表的有效数据源:
public void DeleteExtraTableRows(string emailAddress, Excel.ListObject table)
{
try
{
var rowIndex = 0;
var wasDeleted = false;
while (rowIndex <= table.ListRows.Count)
{
if (!wasDeleted) rowIndex++;
var row = table.ListRows[rowIndex];
var range = (Excel.Range)row.Range.Cells[1, 1];
var value = range.Value2;
if (value != null && !string.Equals(emailAddress, value.ToString()))
{
row.Delete();
wasDeleted = true;
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message + "\n\n" + e.StackTrace);
}
}
There is also a possibility that the email
is never found in the loop's if (cell.Value2.ToString() == email )
condition, which would end up deleting all rows from your range - even if the only difference is an extra space at the end of the in-cell value. With the above code, even if all email addresses get deleted the data source remains a valid one for a pivot table that would be connected to it.
还有可能在循环的if(cell.Value2.ToString()== email)条件中找不到电子邮件,这最终会删除范围内的所有行 - 即使唯一的差异是在内嵌值的结束。使用上面的代码,即使删除了所有电子邮件地址,数据源仍然是与其连接的数据透视表的有效数据源。
EDIT: In Excel you turn a Range
into a ListObject
by selecting the range in question and clicking the Format as table Ribbon button, from the Home tab. Alternatively you can create one like this:
编辑:在Excel中,通过选择有问题的范围并单击“主页”选项卡中的“格式为表格功能”按钮,将范围转换为ListObject。或者你可以创建一个这样的:
var range = ((Excel.Range)(worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[3, 1]]));
var table = worksheet.ListObjects.Add(SourceType: Excel.XlListObjectSourceType.xlSrcRange, Source: range,
XlListObjectHasHeaders: Excel.XlYesNoGuess.xlYes);
table.TableStyle = "TableStyleMedium3";
In code, you can access all ListObjects
on a worksheet using the ListObjects
property:
在代码中,您可以使用ListObjects属性访问工作表上的所有ListObject:
var worksheet = (Excel.Worksheet) Globals.ThisAddIn.Application.ActiveSheet;
var tables = worksheet.ListObjects;
Then, you can access a specific ListObject
/table with several different ways:
然后,您可以使用几种不同的方式访问特定的ListObject /表:
var myTable = tables[1];
var myTable = tables.Item["Table1"];
var myTable = tables.OfType().FirstOrDefault(t => t.Name == "Table1");
As rows are added from the table, the actual range it refers to will be expanded accordingly; use myTable.Range
to access the range in question.
当从表中添加行时,它引用的实际范围将相应地扩展;使用myTable.Range访问相关范围。
4
i suppose this situation occurs because of the pivot tables you got. cause refresh all will trigger pivot table's refresh command too. look at the code below. It may give you an idea about it. Its not about 1 row im sure. i checked it everthing works just fine its most posibly caused by pivot tables.
我想这种情况是因为您获得的数据透视表。导致刷新全部也将触发数据透视表的刷新命令。看下面的代码。它可能会让你了解它。它肯定不是一排。我检查了它的翻转工作就好了它最可能由枢轴表引起的。
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open("some.xlsx");
// For each worksheet we got
foreach (Microsoft.Office.Interop.Excel.Worksheet worksheet in xlWorkbook.Sheets)
{ // and each pivot table in each worksheet
foreach (Microsoft.Office.Interop.Excel.PivotTable pivot in worksheet.PivotTables())
{ // disable BackgroundQuery
pivot.PivotTableWizard(BackgroundQuery: false);
}
}
// try to refresh all sheet
try { xlWorkbook.RefreshAll(); } catch { }
// then save
xlWorkbook.Save();
2
The obvious answer seems to be that sometimes you have one row of data as the source for your pivot table and sometimes you don't - even when you think you still do. I have not been able to create a pivot table(or change the source of a pivot table) to one row of data:
显而易见的答案似乎是,有时你有一行数据作为数据透视表的来源,有时候你没有 - 即使你认为你仍然这样做。我无法创建数据透视表(或将数据透视表的源更改为一行数据):
but if you are able to somehow figure out a way to do this then you have found your answer. There is no reason you can't have one row of data as your source just from a practical/theoretical perspective, but it looks like excel tries to prevent that from happening(maybe because the code assumes two rows). So if you do find a way, then it is probably a bug. Good Luck.
但如果你能够以某种方式找到一种方法来做到这一点,那么你已经找到了答案。从实际/理论的角度来看,没有理由不能将一行数据作为源,但看起来excel会尝试防止这种情况发生(可能因为代码假设有两行)。所以如果你找到了办法,那么它可能就是一个错误。祝你好运。