change link path for linked spreadsheet

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • franc sutherland
    #1

    change link path for linked spreadsheet

    Hello everyone,

    I am using Access 2003. In my database there is a table which is
    linked to an excel spreadsheet. When I move the mdb file to someone
    else's computer the spreadsheet will be in a different location. A
    location which I can not access from my own PC when designing the
    database, so can not use the Linked Table Manager to update the link.
    I need to find a way of telling the database where the source
    spreadsheet for the link table is.

    Is there a way of using a public constant which would store a string
    which would be the network path to the spreadsheet, which I could
    combine with a startup module which would tell the database where to
    find the source spreadsheet?

    I have tried using the fRefreshLinks function from the Microsoft
    website but this would appear to be designed for re-locating BE mdb's
    rather than spreadsheets and gives me error number 3343, unrecognised
    database format, followed by the existing file and path name for the
    spreadsheet.

    Thanks,

    Franc.

    Tags: None
    • KC-Mass
      #2
      Re: change link path for linked spreadsheet

      Hi Franc

      Store the path in a control on a form and reference that
      when relinking. That way you can edit it and relink where
      you need to.

      Google for code to "Select a directory VBA" and
      "relink Access to excel file VBA".

      You should get everything you need.


      Regards Kevin


      "franc sutherland" <franc.sutherla nd@googlemail.c omwrote in message
      news:96d20bea-b267-4ea6-9fcd-1ba820fa51e4@p5 9g2000hsd.googl egroups.com...
      Hello everyone,
      >
      I am using Access 2003. In my database there is a table which is
      linked to an excel spreadsheet. When I move the mdb file to someone
      else's computer the spreadsheet will be in a different location. A
      location which I can not access from my own PC when designing the
      database, so can not use the Linked Table Manager to update the link.
      I need to find a way of telling the database where the source
      spreadsheet for the link table is.
      >
      Is there a way of using a public constant which would store a string
      which would be the network path to the spreadsheet, which I could
      combine with a startup module which would tell the database where to
      find the source spreadsheet?
      >
      I have tried using the fRefreshLinks function from the Microsoft
      website but this would appear to be designed for re-locating BE mdb's
      rather than spreadsheets and gives me error number 3343, unrecognised
      database format, followed by the existing file and path name for the
      spreadsheet.
      >
      Thanks,
      >
      Franc.
      >

        Comment

        • franc sutherland
          #3
          Re: change link path for linked spreadsheet

          On Oct 22, 5:17 am, "KC-Mass" <connearneyATco mcastDOTnetwrot e:
          Hi Franc
          >
          Store the path in a control on a form and reference that
          when relinking.  That way you can edit it and relink where
          you need to.
          >
          Google for code to "Select a directory VBA" and
          "relink Access to excel file VBA".
          >
          You should get everything you need.
          >
          Regards Kevin
          >
          "franc sutherland" <franc.sutherl. ..@googlemail.c omwrote in message
          >
          news:96d20bea-b267-4ea6-9fcd-1ba820fa51e4@p5 9g2000hsd.googl egroups.com...
          >
          Hello everyone,
          >
          I am using Access 2003.  In my database there is a table which is
          linked to an excel spreadsheet.  When I move the mdb file to someone
          else's computer the spreadsheet will be in a different location.  A
          location which I can not access from my own PC when designing the
          database, so can not use the Linked Table Manager to update the link.
          I need to find a way of telling the database where the source
          spreadsheet for the link table is.
          >
          Is there a way of using a public constant which would store a string
          which would be the network path to the spreadsheet, which I could
          combine with a startup module which would tell the database where to
          find the source spreadsheet?
          >
          I have tried using the fRefreshLinks function from the Microsoft
          website but this would appear to be designed for re-locating BE mdb's
          rather than spreadsheets and gives me error number 3343, unrecognised
          database format, followed by the existing file and path name for the
          spreadsheet.
          >
          Thanks,
          >
          Franc.
          Hi Kevin,

          Thanks for the advice. I found some useful stuff here
          http://p2p.wrox.com/topicindex/31037.htm in case anyone else is on
          this as well.

          All the best,

          Franc.

            Comment

            Previous template Next
            Working...