Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Location
    Royal Oak, MI
    Posts
    2

    Unanswered: DoCmd.TransferSpreadsheet - Variable Filename Path

    I've built an MS Access 2000 application that imports from Excel 2000. However, I want the Filename path to be entered by the user into a Text box on a Form - versus a fixed path stored in the Macro of VB Code. So, from a Form, I've added a Text box called [ImportLocation] for the user to enter the filename's path. I've got a command button called "ImportMacro" linked to the following VB Code:

    Private Sub ImportMacro_Click()
    On Error GoToErr_ImportMacro_Click()

    Dim ITS_Dump As String

    DoCmd.TransferSpreadsheet acImport, 8, "ITS_Dump", [ImportLocation].Value, [Yes],""

    Exit ImportMacro_Click:
    Exit Sub

    Upon execution, I get the following message:

    "Can't find the field 'I' referred to in your expression."

    Any ideas? Thanks in advance!

  2. #2
    Join Date
    Mar 2004
    Location
    Adelaide, Australia
    Posts
    32
    I have done a similar thing where I export to excel instead of importing as you are trying to do.

    I used a Save dialog to get the filename for the exported file. It works by calling the standard windows open/save dialog box. It looks a little more professional than asking the user to type in a path & filename. Plus, it limits the chances of the user typing the path/filename incorrectly.

    Have a look here -> http://www.mvps.org/access/api/api0001.htm , it shows you an example of how to call the open/save dialog box.

    HTH.
    Regards,
    Rod.

    Programmer response #5 - "Even though it doesn't work, how does it feel?"

  3. #3
    Join Date
    Apr 2004
    Location
    Royal Oak, MI
    Posts
    2

    Cool Got it working

    The problem was in my code. There's not a lot of information about when to use Quotes vs. Brackets in various levels of Access. In this case it was trial & error. Regardless, the problem was solved by changing the brackets around my Table Name to Quotes (ie: From [ITS_Dump] to "ITS_Dump"). The correct code is as follows:

    DoCmd.TransferSpreadsheet acImport, 8, "ITS_Dump", [ImportLocation].Value, [Yes],""

    The VB Code now refers to a path I can designate/change in a Text box on my form, versus hiding a fixed path within the VB Code or Transfer Spreadsheet Macro.
    Last edited by druyver; 04-12-04 at 18:09.

Posting Permissions

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