disable excel alerts in access vba. (1 Viewer)

John Sh

Member
Local time
Today, 09:02
Joined
Feb 8, 2021
Messages
158
I have read many articles and they all say what I am doing should work. It doesn't.
Code:
Private Sub btnOpenFile_Click()
    Excel.Application.DisplayAlerts = False
'    DoCmd.SetWarnings False
    Application.FollowHyperlink Me.txtFileName, True
'    DoCmd.SetWarnings True
    Excel.Application.DisplayAlerts = True
End Sub

The code brings up intelesence while entering. I t compiles without error but crashes when running.
I have tried "setwarnings". It doesn't crash but also has no effect on the excel alerts.
I can successfully open the required excel file after passing the alert so feel all the relevant references have been ticked.
Any suggestions
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 07:02
Joined
May 7, 2009
Messages
16,129
you create a variable that will hold the excel.application:

Dim objExcel As New Excel.Application
objExcel.DisplayAlerts = False
..
..
'later
objExcel.DisplayAlerts = True
 

John Sh

Member
Local time
Today, 09:02
Joined
Feb 8, 2021
Messages
158
Thanks for the response arnelgp but that crashes too.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 07:02
Joined
May 7, 2009
Messages
16,129
what is the whole code from opening the workbook up to closing?
 

John Sh

Member
Local time
Today, 09:02
Joined
Feb 8, 2021
Messages
158
I have a small form that enables the user to browse and then open the required spreadsheet.
This is the entire code for that form.

Code:
Option Compare Database
Option Explicit

Private Sub btnBrowse_Click()
    Dim diag As Office.FileDialog
    Dim item As Variant
    Dim sdir As String
    sdir = Dir(pathLyn)
    ChDrive "O:"
    ChDir pathLyn
    Set diag = Application.FileDialog(msoFileDialogFilePicker)
    diag.AllowMultiSelect = False
    diag.Filters.Clear
    diag.Filters.Add "Excel Spreadsheets", "*.xlc, *.xlsx"
    If diag.show Then
        For Each item In diag.SelectedItems
           Me.txtFileName = item
        Next
    End If
End Sub

Private Sub btnExit_Click()
    DoCmd.Close acForm, Me.Name
    DoCmd.OpenForm "Main Menu"
End Sub

Private Sub btnOpenFile_Click()
    Application.FollowHyperlink Me.txtFileName, True
End Sub
Edit. I just noticed I have *.xlc it should be *.xls but is inconsequential for the problem at hand.
Also I removed the "displayalerts" before I copied the code. They were above and below the "followhyperlink" line.
They were "Excel.application.displayalerts = false / true".

Edit edit.
FYI the file location has been added to the trust centre.

When the spreadsheet is closed the user can either select another or return to the main menu.
If changes have been made the normal save dialogue is opened.

Here is a screen dump of the form and alert.
Screenshot_48.jpg
 
Last edited:

John Sh

Member
Local time
Today, 09:02
Joined
Feb 8, 2021
Messages
158
I have a small form that enables the user to browse and then open the required spreadsheet.
This is the entire code for that form.

Code:
Option Compare Database
Option Explicit

Private Sub btnBrowse_Click()
    Dim diag As Office.FileDialog
    Dim item As Variant
    Dim sdir As String
    sdir = Dir(pathLyn)
    ChDrive "O:"
    ChDir pathLyn
    Set diag = Application.FileDialog(msoFileDialogFilePicker)
    diag.AllowMultiSelect = False
    diag.Filters.Clear
    diag.Filters.Add "Excel Spreadsheets", "*.xlc, *.xlsx"
    If diag.show Then
        For Each item In diag.SelectedItems
           Me.txtFileName = item
        Next
    End If
End Sub

Private Sub btnExit_Click()
    DoCmd.Close acForm, Me.Name
    DoCmd.OpenForm "Main Menu"
End Sub

Private Sub btnOpenFile_Click()
    Application.FollowHyperlink Me.txtFileName, True
End Sub

When the spreadsheet is closed the user can either select another or return to the main menu.
If changes have been made the normal save dialogue is opened.

H
 

John Sh

Member
Local time
Today, 09:02
Joined
Feb 8, 2021
Messages
158
I stuffed up a bit there. Here is the correct code for the calling sub.
Code:
Private Sub btnOpenFile_Click()
    Excel.Application.DisplayAlerts = False
    Application.FollowHyperlink Me.txtFileName, True
    Excel.Application.DisplayAlerts = False
End Sub

I have also added the file location to the trust centre.
Below is a screen dump of the form and alert.

Screenshot_48.jpg
 

cheekybuddha

AWF VIP
Local time
Today, 00:02
Joined
Jul 21, 2014
Messages
1,004
Did you read the article in the link I posted?

All you would do is copy the function PS_OpenItem() in to a new standard module (name the module anything other than 'PS_OpenItem').

Also copy the PS_Execute() function from this post which is linked in the above post.

Then replace your three lines of code with something like:

Code:
Private Sub btnOpenFile_Click()
  If Len(Me.txtFileName & vbNullString) Then      ' Check there is actually a path to open
    PS_Openitem Me.txtFileName
  End If
End Sub
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 07:02
Joined
May 7, 2009
Messages
16,129
you are opening the workbook using FollowHyperlink?
you need excel automation to be able to set the DisplayWarnings property.
 

John Sh

Member
Local time
Today, 09:02
Joined
Feb 8, 2021
Messages
158
you are opening the workbook using FollowHyperlink?
you need excel automation to be able to set the DisplayWarnings property.
Yes. I'm in the process of implementing that code. It's a bit above my level of understanding but looks simple enough.
I haven't implemented excel automation as yet, I'll try that also and let you know what works.
Thank you for your time so far. It is much appreciated.
John

My apologies for the multiple posts, I got a bit tangled up.
 

John Sh

Member
Local time
Today, 09:02
Joined
Feb 8, 2021
Messages
158
you are opening the workbook using FollowHyperlink?
you need excel automation to be able to set the DisplayWarnings property.
I've tried Daniel's code and it works fine but the brief blue shell window is a bit disconcerting for the unknowing.
What do you mean by "Excel automation"?
My knowledge of Access is growing but my knowledge of Excel is very basic.
 

cheekybuddha

AWF VIP
Local time
Today, 00:02
Joined
Jul 21, 2014
Messages
1,004
I've tried Daniel's code and it works fine but the brief blue shell window is a bit disconcerting for the unknowing.

Did you notice there are two versions of the same function in the second link I posted:
The issue with the above is that it generates a visible PowerShell window that the end-user will see. Luckily, there a a small change we can make to avoid this. By switching from using .Exec to .Run we can silently run PowerShell commands. As such, our procedure becomes

However, IIRC from a quick test, the second version also flashed a powershell window :(
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 07:02
Joined
May 7, 2009
Messages
16,129
No, the link to daniel is not excel automation.

copy this in a module:
Code:
Public Function getExcel() As Object
    Static xl As Object     'Excel.Application
    If xl Is Nothing Then
        Set xl = CreateObject("excel.application")
    End If
    Set getExcel = xl
End Function

now change your code:
Code:
Private Sub btnOpenFile_Click()
    dim xlApp As Object
    set xlApp = getExcel()
    xlApp.Visible = True
    xlApp.DisplayAlerts = False
    xlApp.Workbooks.Open Me.txtFileName

End Sub
 

John Sh

Member
Local time
Today, 09:02
Joined
Feb 8, 2021
Messages
158
No, the link to daniel is not excel automation.

copy this in a module:
Code:
Public Function getExcel() As Object
    Static xl As Object     'Excel.Application
    If xl Is Nothing Then
        Set xl = CreateObject("excel.application")
    End If
    Set getExcel = xl
End Function

now change your code:
Code:
Private Sub btnOpenFile_Click()
    dim xlApp As Object
    set xlApp = getExcel()
    xlApp.Visible = True
    xlApp.DisplayAlerts = False
    xlApp.Workbooks.Open Me.txtFileName

End Sub
Perfect.
Not only are the alerts gone, the spreadsheet seems to open much faster than using the shell or hyperlink methods.
Thank you
John
 

Users who are viewing this thread

Top Bottom