Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    dsk96m is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    13

    Bypass beforeclose event

    So i am pulling data using code out of excel from access.

    When I am done, i use the .quit or .close, but the excel file has a before close event that brings up a special msgbox.

    How do I bypass the before close event. I dont want to save the file or anything, I just want to close excel without the before close event running.



    Any help?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    This is my code that closes workbook without the popup:

    Private Sub btnQuit_Click()
    ActiveWorkbook.Close savechanges:=False
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    dsk96m is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    13
    that does not work for me. here is the code in excel that i am trying to bypass:
    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim bSaveOrNot As String
    On Error GoTo errtrap
    If bChangedAndNotSaved = True Then
        bSaveOrNot = UCase(InputBox("You have made changes to the data, but have not yet saved to the Server, do you want to save now?", , "Y"))
        If bSaveOrNot = "Y" Then
            Call Edoc
        Else
            bchangesMade = False
        End If
    End If
    ...
    I cant change any code in the excel file. I am working in access, pulling data out of the excel form, but i cant close the excel application bc of this before close event in excel.

  4. #4
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Did you try opening the Excel spreadsheet as Read-only?

    J.

  5. #5
    dsk96m is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    13
    I have not, I will give that a try, thank you.

  6. #6
    dsk96m is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    13
    That did not work, i still got that popup. It still runs code even in read only mode.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Does that event ever need to run? You could remove it or comment out the lines.

    EDIT: Ooops! I just caught the comment that OP cannot edit the code. I agree with Bob in next post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    I think if you can't change any code in the Excel file, then you are not going to solve the problem. If it is someone else's code, you might need to get some assistance from them to change some of the code.

    What you would need to do is to have them add in a standard module:

    Code:
    Public bDoNotRun As Boolean
     
    Function SetDoNotRun()
        bDoNotRun = True
    End Function
    And then in their BeforeClose event just have
    Code:
    If blnNoRun = False Then
       ' put their normal code here
     
    End If
    And then to call it from Access, it would be like this:
    Code:
    objXL.Application.Run ("SetDoNotRun")

  9. #9
    dsk96m is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    13
    For my purposes, no it never needs to run, but for the people filling out the form it does.

    Can I comment out the code from access vba so then i can close it and not save it?

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Never tried using code to modify code. Check this http://www.mrexcel.com/forum/showthread.php?t=75489
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    dsk96m is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    13
    Assume for a second I can open the file, but the vba is password protected, etc. It would have to be something i change in the code on the fly.

  12. #12
    dsk96m is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    13
    Well my attempt to edit the excel vb project from access failed. The excel vb project is protected as well.

    Let me clarify what I am doing. So there is this excel spreadsheet that people fill out and send in. This is a corporate form, so it is protected. I have the password to unprotect the sheets, but not the VB Project. What I am trying to do is automate the process of pulling the entered data out of the form into an access table, close excel and move to the next file. Ive gotten everything except to close the file so I can move onto the next one (for the reasons mentioned earlier). The before close event is stopping this entire process.

    Anyone have any ideas. The only option i see is ending the excel process (end task). This i would hate to do, but it might be my only option.

  13. #13
    dsk96m is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    13
    Ok, so the only thing i am left with is killing the process. Here is my question on this. So say I have several spreadsheets open and only want to end the task for the file i was working on. Like if i go to the applications tab of the task manager and find the instance that related to my file, i can hit end task and just close that one. Any ideas? I know you can kill all excel.exe, but I dont want to do that, i just want to close the specific one. Help please

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I don't know any way to identify which Excel instance is the one you want to close or if it is even possible.

    You refer to 'VB project'. Do you really mean VBA code behind Excel and Access? The Excel VBA code is protected?

    I just took another look at the Before Close event. If you are not changing anything, just reading data, why are you prompted to save?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Quote Originally Posted by June7 View Post

    I just took another look at the Before Close event. If you are not changing anything, just reading data, why are you prompted to save?
    The Before Close event does just what it says. It runs when the Excel workbook is closed - regardless of what you do - save or not save. So if there is something there that runs on close, it will run. That is why I was suggesting to try to work with the developer of the spreadsheet to implement a method which would work for both. But it sounds like it isn't going to be possible.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help on an event...
    By allykid in forum Forms
    Replies: 4
    Last Post: 03-15-2011, 11:25 AM
  2. Form_Current Event
    By Bubi in forum Forms
    Replies: 6
    Last Post: 02-22-2011, 11:16 AM
  3. bypass file picker?
    By techneophyte in forum Programming
    Replies: 6
    Last Post: 08-17-2010, 11:12 AM
  4. Bypass startup to allow design
    By cjbuechler in forum Access
    Replies: 1
    Last Post: 06-25-2010, 03:38 AM
  5. Replies: 21
    Last Post: 06-03-2009, 05:54 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