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 |
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 |