Results 1 to 11 of 11
  1. #1
    jj1 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2014
    Posts
    128

    run time error 3011 could not find object

    I am trying to add run the
    Code:
    Private Sub Command40_Click()
    Dim oExcel As New Excel.Application
       Dim oWb As Excel.Workbook
       Dim oSheet As Excel.Worksheet
       Dim fileName As String
       
       oExcel.Workbooks.Open (CurrentProject.Path & "\" & "TrainingPlanExcel.xlsX")
       oExcel.Visible = True
       Set oWb = oExcel.Workbooks(1)
       Set oSheet = oExcel.ActiveWorkbook.Worksheets("sheet1")
       oSheet.Unprotect ("qwerty")
       
       Set dbMyDatabase = CurrentDb()
       
       oSheet.Cells(3, 3) = Forms!employeepillarteamskillsselect!DeptName
       fileName = "F:\Training-F620\New Folder\R Database\TrainingPlanExcel.xlsX"
       DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "CLASSTYPE", fileName, True, ""
       
       oSheet.Protect ("qwerty")
            Set oSheet = Nothing
            Set oWb = Nothing
            Set oExcel = Nothing
    End Sub
    on shared network drive. I keep on getting error 3011. I don't know why. It's exporting the query fine in C drive (local drive).

  2. #2
    Gina Maylone is offline Always learning
    Windows 7 64bit Access 2013
    Join Date
    Jun 2013
    Location
    Afton, MN
    Posts
    544
    It could be the dash in Training-F620 throwing it off. Try renaming the directory to TrainingF620 instead.

    HTH

  3. #3
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    right below the procedure first line line you need to put

    On Error Goto 0

    And then find which line is causing this issue. When you find the line, tell us here.

    other possibilities include
    you dont have priv's to modify files at the location. Does the file already exist? Can you export to the network drive when the file doesnt exist.
    Last edited by Perceptus; 02-17-2016 at 02:27 PM. Reason: added more

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Don't need the last comma and quotes.
    Code:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "CLASSTYPE", fileName, True, ""
    The main problem is that "acSpreadsheetTypeExcel9" is Excel 2000 format (xls). Change it to
    Code:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "CLASSTYPE", fileName, True
    Also don't need this line
    Code:
    Set dbMyDatabase = CurrentDb()
    See :
    http://access-excel.tips/access-vba-...erspreadsheet/
    http://ss64.com/access/acspreadsheettype.html

  5. #5
    jj1 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2014
    Posts
    128
    The problem is in
    Code:
     fileName = "F:\Training-F620\New Folder\R Database\TrainingPlanExcel.xlsX"
       DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "CLASSTYPE", fileName, True, ""

  6. #6
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    I would check your permissions on the network share. If this works for your local drive,but not on the shared. You need Read/Write/Modify.

    Also take the X from the end of filename. Per SSanfu it should be xls

  7. #7
    jj1 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2014
    Posts
    128
    Quote Originally Posted by ssanfu View Post
    Don't need the last comma and quotes.
    Code:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "CLASSTYPE", fileName, True, ""
    The main problem is that "acSpreadsheetTypeExcel9" is Excel 2000 format (xls). Change it to
    Code:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "CLASSTYPE", fileName, True
    Also don't need this line
    Code:
    Set dbMyDatabase = CurrentDb()
    See :
    http://access-excel.tips/access-vba-...erspreadsheet/
    http://ss64.com/access/acspreadsheettype.html
    it is still giving me run time error 3125 " is not a valid name. Make sure it does not include invalid punctuation and that it is not too long.
    this is what I use now.
    Code:
    Dim dbMyDatabase As DAO.Database   
       Dim oExcel As New Excel.Application
       Dim oWb As Excel.Workbook
       Dim oSheet As Excel.Worksheet
       Dim fileName As String
    
    
    
    
       oExcel.Workbooks.Open (CurrentProject.Path & "\" & "book1.xls")
       oExcel.Visible = True
       Set oWb = oExcel.Workbooks(1)
       Set oSheet = oExcel.ActiveWorkbook.Worksheets("sheet1")
       oSheet.Unprotect ("qwerty")
       
       'Set dbMyDatabase = CurrentDb()
       
       oSheet.Cells(3, 3) = Forms!employeepillarteamskillsselect!DeptName
       fileName = "C:\Users\Desktop\Database\Book1.xls"
       DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "query2", fileName, True
       
       oSheet.Protect ("qwerty")
            Set oSheet = Nothing
            Set oWb = Nothing
            Set oExcel = Nothing
          
          dbMyDatabase.Close

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You MUST match the file extension with the "spreadsheetType" argument.
    You are mixing them.
    Code:
       fileName = "C:\Users\Desktop\Database\Book1.xls"
       DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "query2", fileName, True
    If the file extension is XLS, you would use "acSpreadsheetTypeExcel9" (Excel 2000 format)

    If the file extension is XLSX, you would use "acSpreadsheetTypeExcel12xml" (Excel 2010 format)


    You can read from either XLS or XLSX.

    You are opening a Excel 2000 spreadsheet and writing 1 piece of data to the worksheet.
    You don't close the workbook.

    THEN you create and write to a different spreadsheet (with the SAME name) in a different path???


    Why do you have this line?
    Code:
     dbMyDatabase.Close
    It will give you an error because there is no object "dbMyDatabase"!




    Are the two two lines in your module (actually EVERY module)
    Code:
    Option Compare Database
    Option Explicit
    If not, they should be.

  9. #9
    jj1 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2014
    Posts
    128
    I am stuck. It still gives me error 3125 is not a valid name.

    code looks like this now:
    Code:
    Private Sub Command40_Click()
    Dim oExcel As New Excel.Application
       Dim oWb As Excel.Workbook
       Dim oSheet As Excel.Worksheet
       Dim fileName As StringoExcel.Workbooks.Open (CurrentProject.Path & "\" & "book1.xls")  
       oExcel.Visible = True
       Set oWb = oExcel.Workbooks(1)
       Set oSheet = oExcel.ActiveWorkbook.Worksheets("sheet1")
       oSheet.Unprotect ("qwerty")fileName = "C:\Users\Desktop\Database\Book1.xls"   
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "jj", fileName, True
    Set oSheet = Nothing
            Set oWb = Nothing
            Set oExcel = Nothing
    End Sub
    It transfers one simple query or simple tables to excel. But does not work on the crosstab query I want to transfer.
    Access also starts running the query. It only brakes at
    Code:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "jj", fileName, True

  10. #10
    mguilbeault is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    3
    I have encountered this problem when trying to import more than 65,000 records using the Transferspreadsheet command. Although I have read many posts that say this should not be a problem with Access 2010 I still cannot get around this problem. Perhaps that’s what you’re running into.
    Quote Originally Posted by jj1 View Post
    I am stuck. It still gives me error 3125 is not a valid name.

    code looks like this now:
    Code:
    Private Sub Command40_Click()
    Dim oExcel As New Excel.Application
       Dim oWb As Excel.Workbook
       Dim oSheet As Excel.Worksheet
       Dim fileName As StringoExcel.Workbooks.Open (CurrentProject.Path & "\" & "book1.xls")  
       oExcel.Visible = True
       Set oWb = oExcel.Workbooks(1)
       Set oSheet = oExcel.ActiveWorkbook.Worksheets("sheet1")
       oSheet.Unprotect ("qwerty")fileName = "C:\Users\Desktop\Database\Book1.xls"   
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "jj", fileName, True
    Set oSheet = Nothing
            Set oWb = Nothing
            Set oExcel = Nothing
    End Sub
    It transfers one simple query or simple tables to excel. But does not work on the crosstab query I want to transfer.
    Access also starts running the query. It only brakes at
    Code:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "jj", fileName, True

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You say you have
    Code:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "jj", fileName, True
    What is "jj"??? It should be a table or query name.


    If you execute this
    Code:
    Private Sub Command40_Click()
        Dim fileName As String
        
        fileName = "C:\myBook1.xls"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "jj", fileName, True
        
    End Sub
    , is the excel file created? (it works for me)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 12-10-2018, 05:24 PM
  2. Access 2013 Error 3011 cannot find object
    By etorasso in forum Access
    Replies: 2
    Last Post: 10-05-2015, 09:51 AM
  3. Replies: 3
    Last Post: 07-29-2014, 01:01 PM
  4. Unable to Find Object Error
    By MHernan1 in forum Access
    Replies: 1
    Last Post: 08-15-2013, 04:11 PM
  5. Replies: 3
    Last Post: 03-03-2013, 12:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums