Excel Macro to Auto Click OK on MsgBox

[gammaman]

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.

[dglienna]

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

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

[gammaman]

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.

[dglienna]

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?

[Eri523]

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

[gammaman]

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.

[dglienna]

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