Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Oct 2006
    Location
    Colorado Springs, Colorado, USA
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    not refreshing data from network drive (2003)

    Client has two spreadsheets, 1 master and one with refresh data. When she has them on her local drive, the data refresh works fine. When she puts them on a network drive and accesses them from there, the data does not refresh. This behavior is the same for everyone else who wants to use the spreadsheets as well. She said it used to work ok a few months ago. Any reasons why the refresh might have stopped working?

    Thanks
    -Torry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: not refreshing data from network drive (2003)

    How are the workbooks linked? By formulas, or by using MS Query?

  3. #3
    Star Lounger
    Join Date
    Oct 2006
    Location
    Colorado Springs, Colorado, USA
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: not refreshing data from network drive (2003)

    by query

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: not refreshing data from network drive (2003)

    Did the client edit the properties of the query table(s) after moving the workbooks? The path to the source workbook is hard-coded, not relative.

  5. #5
    Star Lounger
    Join Date
    Oct 2006
    Location
    Colorado Springs, Colorado, USA
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: not refreshing data from network drive (2003)

    This sounds like it may be the right solution, unfortunately neither the client or myself know where to go to do this. Could you walk me through it?
    Thanks,
    -Torry

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: not refreshing data from network drive (2003)

    Activate the worksheet with the linked table.
    Activate the Visual Basic Editor (Alt+F11).
    Activate the Immediate Window (Ctrl+G).
    Type
    <code>
    ? ActiveSheet.QueryTables(1).Connection
    </code>
    and press Enter. You should see something like this (details will vary, of course):
    <code>
    ODBC;DSN=Excel files;DBQ=C:TestMaster.xls;DefaultDir=C:Test;Drive rId=790;MaxBufferSize=2048;PageTimeout=5;
    </code>
    You can change the path by creating an instruction like this (you can use copy/paste, then edit it):
    <code>
    ActiveSheet.QueryTables(1).Connection = "ODBC;DSN=Excel files;DBQ=H:ProdMaster.xls;DefaultDir=H:Prod;Drive rId=790;MaxBufferSize=2048;PageTimeout=5;"
    </code>
    and pressing Enter anywhere in the line (notice the quotes surrounding the connection string).

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: not refreshing data from network drive (2003)

    You can use a little utility I created together with Dick Kusleika some time ago:

    http://www.jkp-ads.com/download.asp Look for a file named QueryManager.zip.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Star Lounger
    Join Date
    Oct 2006
    Location
    Colorado Springs, Colorado, USA
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: not refreshing data from network drive (2003)

    She has tried your fix and checked it several times, but it still isn't working. I sent her the utility Jan posted to see if that helps. Any other suggestions?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: not refreshing data from network drive (2003)

    No, I'm out of ideas.

Posting Permissions

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