Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    68

    Unanswered: TransferSpreadsheet can't find file

    Hello, I am trying to import excel data into my database using the DoCmd.Transferspreadsheet command. When I run the code and enter the path, I am getting the following error: "The Microsoft Jet database engine could not find the object '-1'. Make sure the object exists and that you spell t's name and the path name correctly." I know that the names are spelled correctly, but I can't figure out why the file can not be located. Your help is appreciated.

    Here is my code:

    unction ImportTrainingData()
    Dim dbPath As String
    Dim gotTable As Boolean

    gotTable = False
    dbPath = InputBox("Enter Location of Spreadsheet" + "(drive:\path\)", "Location of Spreadsheet")
    If dbPath <> "" Then

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblTrainerTimeTracking", dbPath + "TrainerTimeTracking.xls", , True
    MsgBox "Spreadsheet has been imported...", vbExclamation, "Import Data"
    DoCmd.OpenQuery "AppendTimeTrackingData", acNormal, acEdit
    MsgBox "Data has been added to Time Tracking Table...", vbExclamation, "Data Added"
    gotTable = True

    If gotTable = False Then
    MsgBox "No data was imported, please check your path...", vbCritical, "Import Tables Fail"
    ImportExcel = False
    Else
    MsgBox "Your data has been imported and updated successfully...", vbExclamation, "Import Successful"
    ImportExcel = True
    End If

    Else
    MsgBox "Import has been canceled by user", vbCritical, "Operation Canceled"
    ImportExcel = False
    End If

    End Function

  2. #2
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    If you are using access 2000 then the code you want to use is "&" instead of "+" when you are connecting two field together
    e.g.
    dbase = "c:\whatever\" & fieldname

  3. #3
    Join Date
    Jun 2004
    Posts
    92
    Quote Originally Posted by palto1
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblTrainerTimeTracking", dbPath + "TrainerTimeTracking.xls", , True
    Change this to:

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblTrainerTimeTracking", dbPath + "TrainerTimeTracking.xls", True

    You put too many commas.

  4. #4
    Join Date
    Mar 2004
    Posts
    68

    Still can't find file with TransferSpreadsheet

    Thanks for your responses. However, neither of those suggestions resolved the issue. I am still getting a run-time error message stating that the object '-1' could not be found. Where is it getting the '-1' from? Does it not recognize the file name?

    The strange thing is that I've used this code before with success and for some reason, it's not working now.

    I'm at a loss!

    thanks for your help.

  5. #5
    Join Date
    Jun 2004
    Posts
    92
    Quote Originally Posted by palto1
    Thanks for your responses. However, neither of those suggestions resolved the issue. I am still getting a run-time error message stating that the object '-1' could not be found. Where is it getting the '-1' from? Does it not recognize the file name?

    The strange thing is that I've used this code before with success and for some reason, it's not working now.

    I'm at a loss!

    thanks for your help.
    I don't know what to tell you. The code change that I suggested got it to work perfectly for me.

    Can you post your db, or the part of it that this is effecting so I can see what you have?
    Last edited by sionus; 07-29-04 at 10:03.

  6. #6
    Join Date
    Jun 2004
    Posts
    92
    Quote Originally Posted by palto1
    MsgBox "Spreadsheet has been imported...", vbExclamation, "Import Data"
    DoCmd.OpenQuery "AppendTimeTrackingData", acNormal, acEdit
    Do you even get to this msgbox when you run it? Also, what does your query do? The act of importing the excel file appends the tbl already.

    This is what I am running as a function, and it works correctly. I commented out the query since I don't have it.

    Code:
    Public Function ImportTrainingData()
    Dim dbPath As String
    Dim gotTable As Boolean
    
    gotTable = False
    dbPath = InputBox("Enter Location of Spreadsheet" + "(drive:\path\)", "Location of Spreadsheet")
    If dbPath <> "" Then
    
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblTrainerTimeTracking", dbPath + "TrainerTimeTracking.xls", True
    MsgBox "Spreadsheet has been imported...", vbExclamation, "Import Data"
    'DoCmd.OpenQuery "AppendTimeTrackingData", acNormal, acEdit
    MsgBox "Data has been added to Time Tracking Table...", vbExclamation, "Data Added"
    gotTable = True
    
    If gotTable = False Then
    MsgBox "No data was imported, please check your path...", vbCritical, "Import Tables Fail"
    ImportExcel = False
    Else
    MsgBox "Your data has been imported and updated successfully...", vbExclamation, "Import Successful"
    ImportExcel = True
    End If
    
    Else
    MsgBox "Import has been canceled by user", vbCritical, "Operation Canceled"
    ImportExcel = False
    End If
    
    End Function
    Last edited by sionus; 07-29-04 at 11:22.

  7. #7
    Join Date
    Jun 2004
    Posts
    92
    Quote Originally Posted by ChrisGolden
    If you are using access 2000 then the code you want to use is "&" instead of "+" when you are connecting two field together
    e.g.
    dbase = "c:\whatever\" & fieldname
    BTW, if the two expressions are strings, then the + operator does concatenation. If they are integers then it does addition.

  8. #8
    Join Date
    Mar 2004
    Posts
    68

    Unhappy Still getting the error message...

    No, I don't get the msgbox "Spreadhseet has been imported ..."...

    The code stops at the DoCmd.TransferSpreadsheet line. Your code was cut off above, but I think we have the same thing:

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblTrainerTimeTracking", dbPath + "TrainertimeTracking.xls", True

    I am using Access 2002, does that make a difference?

  9. #9
    Join Date
    Jun 2004
    Posts
    92
    No, it doesn't make a difference. I am using it too and tried in 2000 and 2002 db formats and worked in both. How are you activating the function? I simply created a button and put in the onclick event: Call ImportTrainingData

    Also, try it from the root directory of your drive i.e. c:\, and try to just hard code a button to do the command:

    Code:
    DoCmd.TransferSpreadsheet , , "tblTrainerTimeTracking", "c:\TrainerTimeTracking.xls", True
    It won't give you any message boxes, but after you click it check the table and it should append the records.

    Another idea as to what is causing the problem is a the fields you are importing or the table. Again, for testing, just make a two column table and then two columns of info in the spreadsheet and try to import it. That is what I am doing to test the code.

    It's has to be something else other then the code, because it is correct.

Posting Permissions

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