Excel Macro to Auto Click OK on MsgBox
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 7 of 7

Thread: Excel Macro to Auto Click OK on MsgBox

  1. #1
    Join Date
    Sep 2006
    Location
    Wantagh,NY
    Posts
    151

    Excel Macro to Auto Click OK on MsgBox

    I am trying to create a macro that will automatically click OK on a message box that pops up on excel files. This message says "Document 'XYZ' should be opened in read-only unless you need to save changes" We have our system linked to ODC and we are tying to commit documents. Problem is when it comes to these excel docs, this message box gets in the way and the docs cannot commit unless someone stands there and manually says OK on all the docs.

    Anyway, I know that excel has a global workbook called Personal.xls and that when any other workbook is opened, the Personal.xls workbook is also opened (hidden). Any macros stored in this Personal.xls workbook will execute.

    I have tried many things such as saying

    Application.DisplayAlerts = False
    Application.EnableEvents = False

    etc... and nothing worked

    The only thing I have been able to do sucessfully is to fire an additional message box in front of the one I do not want.

    Any help as to how to say "OK" on this MsgBox is greatly appriciated.

  2. #2
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,946

    Re: Excel Macro to Auto Click OK on MsgBox

    That won't work... You didn't show how you SAVE the file. Look at this:

    Plus, searching the forums would have shown this from the last time

    Code:
    Option Explicit
    
    Public xlAppTemp As Excel.Application
    Public xlWorkBook As Excel.Workbook
    Public xlSheet As Excel.Worksheet
    Dim strDate$
    
    Public Sub GenerateReport()
      On Error GoTo ErrHandler
      
      ' Creating Object for Excel File.....
      Set xlAppTemp = New Excel.Application
      
      ' Making it Invisible and non-Interactive.....
      xlAppTemp.Visible = False
      xlAppTemp.DisplayAlerts = False
        ' Opening Template Excel File.....
      Set xlWorkBook = xlAppTemp.Workbooks.Open(App.Path & "\Book1.xls", , False)
      Set xlSheet = xlWorkBook.Sheets(1)
    
      ' Making Active to Worksheet 1.....
      xlSheet.Activate
    
      ' I am doing lot of things in it, but to provide you with example
      xlSheet.Cells(15, 1) = "This is my report 1"
    
      ' Formating Date to attach with new file name.....
      strDate = Format(Date, "yyyy-mm-dd")
      
      ' Saving excel file with new name on different folder.....
      xlWorkBook.SaveAs App.Path & "\Output" & strDate & ".xls"
    
    Cleanup:
      ' Destroying Objects.....
      Set xlSheet = Nothing
      xlWorkBook.Close SaveChanges:=False
      Set xlWorkBook = Nothing
    'The Visible and DisplayAlerts settings should be reset, as they can affect 'manual' use of Excel too.
      xlAppTemp.Visible = True
      xlAppTemp.DisplayAlerts = True
      xlAppTemp.Quit
      Set xlAppTemp = Nothing
      Exit Sub
    ErrHandler:
    'I presume this section comes after ErrHandler, in which case you will want to close the workbook without changes.
    '(save happens just above if no error occurs)
      xlWorkBook.Close SaveChanges:=False
      Set xlWorkBook = Nothing
    
    'The Visible and DisplayAlerts settings should be reset, as they can affect 'manual' use of Excel too.
      xlAppTemp.Visible = True
      xlAppTemp.DisplayAlerts = True
    
      xlAppTemp.Quit
      Set xlAppTemp = Nothing
    End Sub
    
    Private Sub Command1_Click()
      Call GenerateReport
      Beep
    End Sub
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Sep 2006
    Location
    Wantagh,NY
    Posts
    151

    Re: Excel Macro to Auto Click OK on MsgBox

    First of all Thank You. Maybe I am confused but all I am looking to do have that message box automatically click OK so that the files can be committed into Oracle Document Capture. As of now all of the Excel Docs are getting queued b/c no one is there to press OK. Again it is the "D you wish to Open in Read Only" msgBox and I want to say Yes on all of them. Since they are all defaulting to "Read-Only" then no changes to these Docs are ever going to be made until after they are commited into ODC (Oracle Document Capture)

    As it stands now the only thing I have is an Auto_Open() Function in the Personal.xls workbook.

    From Auto_Open I call the WorkBook_Open() of the Personal.xls. It is here that I am trying to say that for all Workbooks Opened, Click OK on the message box.

  4. #4
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,946

    Re: Excel Macro to Auto Click OK on MsgBox

    Sounds like an Oracle question. Try their support site.

    Also, using Excel to open a DB or do the work of a DB? What's the purpose of using Excel?
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  5. #5
    Join Date
    Jun 2010
    Location
    Germany
    Posts
    2,583

    Re: Excel Macro to Auto Click OK on MsgBox

    Do I understand you right that you open these files using a macro and don't want to write to them anyway? In that case try setting the ReadOnly parameter in the call to Workbooks.Open to True, then Excel won't ask.

    HTH
    I was thrown out of college for cheating on the metaphysics exam; I looked into the soul of the boy sitting next to me.

    This is a snakeskin jacket! And for me it's a symbol of my individuality, and my belief... in personal freedom.

  6. #6
    Join Date
    Sep 2006
    Location
    Wantagh,NY
    Posts
    151

    Re: Excel Macro to Auto Click OK on MsgBox

    Quote Originally Posted by dglienna View Post
    Sounds like an Oracle question. Try their support site.

    Also, using Excel to open a DB or do the work of a DB? What's the purpose of using Excel?

    We are not using excel to open DB. We have docs being sent to us from other companies. We are using Oracle Document Capture to listen for them. Oracle document capture, upon receiving these docs, opens them up, "captures them in pdf format" and then commits it to UCM Database. Again the problem is that when Oracle Document Capture receives a doc with read only recommended, the message box comes up and waits for user input and the docs are queuing. We want to say OK on these msgboxes so that they are all opened in read only so that Oracle Document Capture can proceed as normal and do its "capture process" and commit the doc to the DB.

  7. #7
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,946

    Re: Excel Macro to Auto Click OK on MsgBox

    How about set the attributes to NOT be PROTECTED (you can always turn it back off). ATTRIB /? should give some hints.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

Posting Permissions

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


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center