Suppress displayalerts when calling procedure in another workbook

StefanVanSusteren

New Member
Joined
Aug 7, 2018
Messages
8
Hi all,

I am calling a procedure in another workbook with VBA. The workbook is already open. I am trying to suppress all alerts with application.displayalerts = False.
I added the application.displayalerts in both procedures.

But after calling the procedure in the other workbook I am still getting alerts.
To call the procedure I use:

Application.Run "'" & wkb.Name & "'!" & Module & "." & Procedure

Is there a way to suppress the alerts, or a workaround?

Thanks for your help.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,293
Office Version
365
Platform
Windows
Hi
what are you doing inside the other workbook that is prompting the alerts?
which alerts are you seeing?
Did you put Application.DisplayAlerts = False in the procedure that you are calling?
 
Last edited:

StefanVanSusteren

New Member
Joined
Aug 7, 2018
Messages
8
Hi,

Yeah, I put Application.DisplayAlerts = False also in the procedure I am calling.

I am inserting OLEobjects (PDF, Excel)... On all excel files I get the alert: "File in Use".

When the procedure is running from the file itself...no alerts. When called from another workbook...alert for every excel file.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,293
Office Version
365
Platform
Windows
please paste
- the actual line of code that is triggering the alert
into your reply

I will try to recreate etc
thanks
 

StefanVanSusteren

New Member
Joined
Aug 7, 2018
Messages
8
Below is the line of code triggering the alert.
I added the 3 variables that are needed.

Dim File(1) as String
Dim File(2) as String
Dim Icon(U) as String

ActiveSheet.OLEObjects.Add(Filename:=File(1) & File(2), Link:=False, DisplayAsIcon:=True, IconFileName:=Icon(U), IconIndex:=0, IconLabel:=File(2)).Select
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,606
Office Version
2016
Platform
Windows
Below is the line of code triggering the alert.
I added the 3 variables that are needed.

Dim File(1) as String
Dim File(2) as String
Dim Icon(U) as String

ActiveSheet.OLEObjects.Add(Filename:=File(1) & File(2), Link:=False, DisplayAsIcon:=True, IconFileName:=Icon(U), IconIndex:=0, IconLabel:=File(2)).Select
That code won't compile because you are declaring the same File() array twice.
Icon(U) array won't compile either.

Also, the error "File in use" you are getting means the file you are trying to open is alreay open .. revise your code and see if you can find where the file is being opened.

Application.DisplayAlerts won't supress the File in use prompt.
 
Last edited:

StefanVanSusteren

New Member
Joined
Aug 7, 2018
Messages
8
Hi Jaafar,

Dont know what you mean with: "it doesn't compile"
The code is working, beside the "file in use error".
Could you give a suggestion how it should look like?

After checking some things I concluded my issue is not the displayalerts (as you said). But I have no clue what the issue is.

When I am running the code in the "original file". Code works great, no alerts or anything.
When I call the code from another workbook (all it does is calling the same code, but from another workbook) I do get the error.

I ran the code step by step and just before the alert...the workbook that gives the "file in use" alert is 100% NOT open. The 1 line of code that inserts the OLE objects opens the file twice apparently.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,606
Office Version
2016
Platform
Windows
Hi Jaafar,

Dont know what you mean with: "it doesn't compile"
The code is working, beside the "file in use error".
Could you give a suggestion how it should look like?

After checking some things I concluded my issue is not the displayalerts (as you said). But I have no clue what the issue is.

When I am running the code in the "original file". Code works great, no alerts or anything.
When I call the code from another workbook (all it does is calling the same code, but from another workbook) I do get the error.

I ran the code step by step and just before the alert...the workbook that gives the "file in use" alert is 100% NOT open. The 1 line of code that inserts the OLE objects opens the file twice apparently.
Not sure why you are getting that alert prompt then.

I'll be going offline shortly but i'll see if I can recreate the issue and will post back later.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,293
Office Version
365
Platform
Windows
I created a simple procedure and called it from another file - it ran
(note that it activates the workbook and the worksheet where I place the object)
Code:
Sub addobj()
    Windows(ThisWorkbook.Name).Activate
    ThisWorkbook.Sheets("Sheet1").Activate
 
    Dim fName$, iName$, lbl$
    fName = "C:\Test\pdf.pdf"
    iName = "C:\Test\Jalopy.jpg"
    lbl = "Jalopy"
     ActiveSheet.OLEObjects.Add(Filename:=fName, Link:=False,  DisplayAsIcon:=True, IconFileName:=iconName, IconIndex:=0,  IconLabel:=lbl).Select
End Sub
Perhaps one of the strings contains a different value when you run the code from another workbook. It is worth testing what their values are...
- add these lines immediately above ActiveSheet.OLEObjects.Add
- should provide the correct full paths and file names for both files
(replace MsgBox with Debug.Print to print to immediate window if preferred)

Code:
    Dim s$
    MsgBox File(1) & File(2)
    s = Dir(File(1) & File(2))
    MsgBox s
    MsgBox Icon(U)
    s = Dir(Icon(U))
    MsgBox s
 
Last edited:

StefanVanSusteren

New Member
Joined
Aug 7, 2018
Messages
8
The issue is located on my network drive. When I am running the whole thing from my own C-drive, it works perfectly.

So code is working, network drive is doing something strange.

Only solution to automate would be to have a workaround to automatically select "Read only" on the "File in use" alert.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,971
Messages
5,411,581
Members
403,380
Latest member
ifog671

This Week's Hot Topics

Top