View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
JackCali JackCali is offline
external usenet poster
 
Posts: 5
Default 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