Suppress displayalerts when calling procedure in another workbook

[StefanVanSusteren]

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.

 

[Yongle]

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?

 

[StefanVanSusteren]

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]

please paste
- the actual line of code that is triggering the alert
into your reply

I will try to recreate etc
thanks

 

[StefanVanSusteren]

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]

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.

 

[StefanVanSusteren]

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]

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]

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)

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)

    Dim s$
    MsgBox File(1) & File(2)
    s = Dir(File(1) & File(2))
    MsgBox s
    MsgBox Icon(U)
    s = Dir(Icon(U))
    MsgBox s

 

[StefanVanSusteren]

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.