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

Import data using QueryTable somehow change sheet cells references
 

If you were refreshing a query table, I think you'd have no problem. But,
since you're adding a query table in the locatioin of a previous one...I
believe Excel is inserting columns to make room for the new QT. As evidenced
by the shift in cell references. You might try altering the data souce in
the Connection property in the existing QT, instead of building a new one.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"JackCali" wrote:

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

Import data using QueryTable somehow change sheet cells refere
 

Ron,

Thank you for your suggestions. I tried them but not working for me. Later
I found out that I was setting the RefreshStyle to xlInsertDeleteCells, I
changed to xlOverwriteCells and the data are impored correctly.

"Ron Coderre" wrote:

If you were refreshing a query table, I think you'd have no problem. But,
since you're adding a query table in the locatioin of a previous one...I
believe Excel is inserting columns to make room for the new QT. As evidenced
by the shift in cell references. You might try altering the data souce in
the Connection property in the existing QT, instead of building a new one.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"JackCali" wrote:

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