How to close the msgbox from Excel vba when open excel in Access ?

[hvsummer]

I have to open an excel file in access, but whenever I open that file, I have to choose "OK" on popup msgbox from vba excel.

how to turn off this msgbox automatically ?

I know it is imposible to close modal windows within 1 application. But in my situation, I find a way to do this with my code below.

my Code up-till now:

Public Function OpenExcelIdle(strFullFileName As String)
On Error GoTo Err::
Dim xlapp As Object
Set xlapp = CreateObject("Excel.Application")
Dim books As Object
xlapp.EnableEvents = False
xlapp.AutomationSecurity = 3
xlapp.Visible = False
xlapp.DisplayAlerts = False
xlapp.screenupdating = False
 
xlapp.workbooks.Open (strFullFileName)
 
Set books = xlapp.ActiveWorkBook
 
books.Save
books.Close
xlapp.Quit
Exit_F::
Exit Function
Err::
xlapp.DisplayAlerts = True
xlapp.screenupdating = True
xlapp.Quit
Set books = Nothing
Set xlapp = Nothing
Resume Exit_F::
End Function
 

the code disable event trigger to disable any msgbox appear from on_open event.
set to force disable macro that no macro could run.
then even disable screenupdating or DisplayAlerts, so Excel will completely be opened in silence...

Edit: actually my code work well, but I don't know whether any other situations could happen or not, so plz help me improve it, thank ^^

Edit2: == I think we should move this question to insight section.

[zmbd]

Some slight tweaking to your code - removed extra colons and renamed the branch point labels. Tweaked your error handler. Added a return value for the function so that you can at least test in code if the function ran. Ideally, IMHO, in the error trap you should have some sort of user feedback.

Public Function OpenExcelIdle(strFullFileName As String)
   On Error GoTo Errtrap
   Dim xlapp As Object
   Dim books As Object
'
   OpenExcelIdle= True
'
   Set xlapp = CreateObject("Excel.Application")
'
   xlapp.EnableEvents = False
   xlapp.Visible = False
' 
   xlapp.workbooks.Open (strFullFileName)
'
   Set books = xlapp.ActiveWorkBook
' 
   books.Save
   books.Close
'
Exit_f:
   If not books is Nothing Then Set books = Nothing
   If not xlapp is Nothing Then 
      xlapp.Quit
      Set xlapp = Nothing
   end if
'
Exit Function
Errtrap:
   OpenExcelIdle= False
   Resume Exit_F
End Function

Once the events are disabled in the application and the interactive interface is hidden, unless there is a security warning (and you cannot normally disable these via code, not even with the DisplayAlerts set to false); there is no need to disable screen updates, alerts, nor change the macro-security level (no events, no on_open; thus, no trigger for Macro Security)...

As for this being an Insights article, it needs a little more depth. This method, or at least similar methods, can be found in various other blogs, forums, and books so IMHO to be an ISA there needs to be a little more information about application of method and the writing style needs to be more along the lines of a professional journal entry or tutorial... maybe not as formal as a peer reviewed journal but never the less, in that general grammatical style. :)

[hvsummer]

ok, Thank zmbd, this's just a 5mins working code so it can't be beautiful... as your.

btw, I did not learn anything about code, so I don't know how it work, just trap all the case I think that can happen.

my final code really complex now, I did not use "try ... catch", the method that I have read in a couple day very usefull == I think I'll use it later when i post new code here ^^

edit: forgot to mention, to trap Error, I use breakpoint to test overtime, so I'll know what wrong by looking carefully into each variables and debug/msgbox by myself to understand what wrong.. that how I Learn vba now == and it quite usefull for me, I understand all most this vb-language.

[zmbd]

When troubleshooting code; msgbox, debug.print, etc... are good tools to use.

I would suggest however, that one avoids setting the breakpoint (that little red dot :) ) instead using the STOP command. There are some known glitches that can creep in to one's code on the compiler side that are difficult to diagnose and remove when using the breakpoint method and making changes to the code that do not seem to happen when using the STOP command.

The watch window is also a very good tool. I use it quite frequently to verify that variables have reached certain inputs and so forth.

The locals window has also proven extremely useful as one can watch the variables and objects while stepping thru code in debug state.

I do have a little boilerplate of tutorials and resources that I will PM you latter (Monday?) my PC crashed a little while back and I'm still merging my backups from the old system to the new.... different OS versions :)

[hvsummer]

the point is, I use F8 to step-by-step look where code run into, and I trapped error. so it's clearly enuf for me ^^

different OS version, hope you don't use win 10... that OS's [it's ...]
instead improve touch screen in desktop, they moved to "modern UI" which worser than ever....