none
Web Query XMLImport Problem RRS feed

  • Question

  • Windows XP, Excel 2003 SP3 - Running a web query and dumping the data to a range in a worksheet. Works perfectly. However, the formulas in adjacent columns are being thrown off.  But only the column immediately to the left and the column immediately to the right of the imported range are affected, by the row numbers in the formulas shifting (ex: In row 3, column Z, the formula "=M3 + N3" changes to "=M85 + N85", where 85 corresponds to the number of rows that were just imported).

    <code>

    ActiveWorkbook.XMLImport URL:=strURL, ImportMap:=Nothing, Overwrite:=True, Destination:=TargetWorksheet.Range("D1")

    </code>

    Let's say that columns A, B and C have formulas, columns D, E, and F are the XML import columns, and columns G, H and I have more formulas. After the import happens and columns D, E, and F are populated, the formulas in columns C and G (only) have shifted. I even tried leaving a one column gap between the formula columns and the XML import columns, but I still see this formula shift in one column on the left and one column on the right.

    I've stepped through it and the code works just fine, except for the strange formula shifting.

    Has anyone seen this before?

    Thanks

    Thursday, November 17, 2011 2:11 AM