Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2006

    Unanswered: Eliminate hardcoded Excel filename

    I'm using Access 2003. In my database, I have a link to an Excel spreadsheet and I treat it like a table. The problem I am having is that I seem to be locked into this file at the location I first specified it to be (actually, it wasn't me; it was the developer before me). For example, if I link to it in C:\MyDirectory\file.xls how can I change this location? If I right click, no properties come up for it. If I hold my mouse over the file name where its listed (Table), I get a tooltips-type pop-up that says the location. But how can I change this? Furthermore, my ultimate goal is to have this location specified externally to my .mdb so I don't have to recompile should the location change.

    Is any of this possible?


  2. #2
    Join Date
    Sep 2004
    Charlotte, NC
    To change the source of this linked file, go to Tools and then Database Utilities, and then Linked Table Manager. When the Linked Table Manager popup window appears, click in the check box to select the file that you want to change the link for and also click in the check box next to "Always prompt for new location" and then click OK. You will then be able to specify a different file and locaction to link to.


  3. #3
    Join Date
    Dec 2004
    How often do you expect the file name and location to change?

    If it's rarely, secure the menus so that only an administrator can get to the Database Utilities options. There are good walkthroughs here and at that show you how to set up menu security.

    If it changes as often as the user does, I would prompt the user for the file name and location and use code to import the file when it's needed.

    Something like:

    Dim mySpreadsheet as String
    mySpreadsheetPathName= myForm.myUserInputTextBox
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_mySpreadsheet", mySpreadsheetPathName , True >
    You'll have to add all of the error handling - ie, what to do if the file isn't where the user says and how to deal with import errors (all tricks I learned from the gurus, here) - but once the spreadsheet is in your database as a table, you can do whatever you need to while the users can continue playing with the spreadsheet itself.

  4. #4
    Join Date
    Jul 2006
    Thank you for your replies.

    You may be hearing more from me on this later, or may not be. Currently, this project is pending customer approval. I posted a new thread this a.m. asking for advice on estimating my time. Maybe I'll never even get to work on this..., sad - it seemed interesting!

    Thanks again.

Posting Permissions

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