Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change relative to absolute references in a block of cells | Excel Discussion (Misc queries) | |||
formula references change when data entry sheet is modified | Excel Worksheet Functions | |||
Can't change name of querytable. | Excel Programming | |||
Changing only source file of pre-existing text import QueryTable? | Excel Programming | |||
Using Querytable.add to import data from a closed workbook | Excel Programming |