Results 1 to 3 of 3
  1. #1
    Tenmakk is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    35

    Way to detect if Excel spreadsheet is open and to close it (always says "User-type not defined")?

    Hello. I'm a beginning/intermediate user, and I've managed to put together some code from what I've found online. I've tried doing this several ways, but it always seems to require that I use "Dim xlApp As Excel.Application". This causes a "User-type not defined" error. I'm trying do do this in my form's click event. I want the button-click to close the spreadsheet (the one created down the code in the same event) ---WITHOUT-- saving before anything else when the button is clicked. It would work if the spreadsheet was just cleared as well. Here is what is currently there:


    Code:
    Private Sub Transfer_Click()
    
    Dim curPath As String
    
    curPath = CurrentProject.Path & "\OtusBug" & ".xlsx"
    
    DoCmd.RunCommand acCmdSaveRecord
    
    DoCmd.TransferSpreadsheet acExport, 10, "tblOtusTemp", curPath, -1
    Application.FollowHyperlink curPath
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL "delete * from tblOtusTemp"
    DoCmd.SetWarnings True
    
    Me.Requery
    
    End Sub

    Last edited by Tenmakk; 04-19-2016 at 06:02 PM. Reason: Realized it can't be saved and could be just cleared

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You may get that error if you don't have Excel loaded in the REFERENCES.
    in the VBE menu, tools, references.
    check mark Microsoft Excel.

  3. #3
    Tenmakk is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    35
    Thanks. I can work with it now.

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

Similar Threads

  1. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  2. Replies: 3
    Last Post: 11-12-2013, 04:13 PM
  3. Replies: 1
    Last Post: 03-14-2013, 12:39 PM
  4. Replies: 1
    Last Post: 12-14-2012, 12:32 AM
  5. Error: "User-defined type not defined"
    By mastromb in forum Programming
    Replies: 10
    Last Post: 01-08-2010, 02:57 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