Import data using QueryTable somehow change sheet cells references
I have a workbook that contains five worksheets. The first worksheet have
cells that contain absolute and relative references to the other four
worksheets' cells. When I update the other four worksheets' contents by
using QueryTable to import the data from cvs files, the cells' references in
the first worksheet are changed. For example, orignally a cell contains
"=Sheet2$C#4" but after the update, the cell contains "=Sheet2$AQ$4". Anyone
knows why this happen? Any suggestions are appreciated.
Below is the code that does the importing of data.
for (int i = 1; i <= fileNames.Count; i++) //import data to the four sheets
{
string file = fileNames[i - 1] as string;
string[] split = file.Split(slash);
string name = split[split.Length -
1].Replace(Constants.TextExtension, "");
//get the correct worksheet to import data
XLS.Worksheet sheet = GetWorkSheet(sheets, name);
XLS.Range range = sheet.get_Range("A1",
SYS.Type.Missing);
XLS.QueryTables tables = sheet.QueryTables;
XLS.QueryTable table = tables.Add(Constants.ExcelText +
fileNames[i - 1], range,
SYS.Type.Missing);
table.Name = name;
table.PreserveFormatting = true;
table.RefreshOnFileOpen = false;
table.RefreshStyle = XLS.XlCellInsertionMode.xlInsertDeleteCells;
table.SaveData = true;
table.AdjustColumnWidth = true;
table.RefreshPeriod = 0;
table.TextFilePromptOnRefresh = false;
table.TextFileStartRow = 1;
table.TextFileParseType = XLS.XlTextParsingType.xlDelimited;
table.TextFileOtherDelimiter = Constants.DelimiterSymbol;
table.TextFileColumnDataTypes = GetDataTypeList(file);
table.Refresh(false);
}
thank you
Jack
|