Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2011
    Posts
    4

    Unanswered: 2007 EXCEL Data Connections Q

    I am currently working with Excel and trying to establish a data connection from an Access Database. I have this process working properly, but the problem I am having is that once the data is imported, it throws off my formulas. This happens most noticeably when the connection is refreshed and new data is imported.

    The most specific issue I have seen occur is that it impacts which cell the formulas reference. For example, it is supposed to be a statement such as "=If(C5=4)"...but once i import the data, it will change the "C5" to the first cell after the last piece of new data, which throws everything off.

    Any thoughts?

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    I assume you are importing into a query table. You can change the query table's settings so that the rows are not deleted.

  3. #3
    Join Date
    Sep 2011
    Posts
    4
    You mean so that when I import it it will not delete rows in excel? How is this done?

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    If you right click on the query table > Data range properties, you should be able to see a setting to Overwrite existing cells with new data & clear unused cells.

  5. #5
    Join Date
    Sep 2011
    Posts
    4
    This is not a feature in the version I have, which is 2007. Thanks for the feedback! Any other ideas anybody?

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    It is a feature in 2007.

    I've attached a screenshot of what I mean. The screenshot was taken while I was using Excel 2007.
    When you right click on the cell, you have to make sure that it is a cell within the query table - not just any old cell on the worksheet - otherwise you won't see the "data range properties" option in the menu. The example I used for the screenshot uses a query table to import a text file - this was just for convenience - I know you're not importing a text file, but the same property applies to your situation too.

    If you don't have this option then you're not using a query table. If that's the case then you'll need to give us more information about how you are importing the data. I do know a workaround which can be applied to your formulas to prevent it happening, but it's really ugly so I'm hesitant to suggest it - it'd be much better to fix the import itself.
    Attached Thumbnails Attached Thumbnails Query Table.jpg  
    Last edited by Colin Legg; 09-11-11 at 19:06.

  7. #7
    Join Date
    Sep 2011
    Posts
    4

    Think I Got It

    3 things:

    1) I was checking for that in Access, not Excel

    2) I had to first select "Table" after I right-clicked to locate the "External Data Properties" option

    3) You rock!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •