How to disable in Excel automatic refresh connection calling SQL stored procedure?

Question

Whenever I change windows of opened workbooks and come back to MyFile.xlsb where I have defined external connection, the refresh of all pivot tables in MyFile.xlsb runs automatically. Needless to add it is very annoying feature. How to disable it? How to run refresh all only on demand. Important note. This problem occurs only on computers of the users I distribute MyFile.xlsb. On my computer it works ok.

I have defined the external connection as a reference to stored procedure in SQL.

Connection string:

Provider=SQLOLEDB.1;
Integrated Security=SSPI;
Persist Security Info=True;
Initial Catalog=MyDataBase;
Data Source=MyServerName;
Use Procedure for Prepare=1;
Auto Translate=True;
Packet Size=4096;
Workstation ID=MyWorkstationID;
Use Encryption for Data=False;
Tag with column collation when possible=False

Here is SQL stored procedure I call.

CREATE PROCEDURE [dbo].[MyProcedure]
AS
BEGIN

-- part one, show user what he has to see
SELECT *
FROM [dbo].[MyView] 
ORDER BY 1

-- part two, get user data
INSERT INTO dbo.My_other_table_logins_history
SELECT 
GETDATE(), 
ORIGINAL_LOGIN()
END

The concept of using this procedure is explained here: SQL procedure from Excel run from connection properties with user login as parameter

Check to see if there is a macro in the workbook that refreshes all. — rwking
– rwking, Commented Sep 7, 2015 at 13:37
@rwking no there is none. I disabled all events. I also unchecked everything in usage pane. — Przemyslaw Remin
– Przemyslaw Remin, Commented Sep 7, 2015 at 13:53
Have a look here: msdn.microsoft.com/en-us/library/office/ff821120.aspx You can disable automatic refresh. — Maciej Los
– Maciej Los, Commented Sep 9, 2015 at 20:25

paul bica · Accepted Answer · 2015-09-11 21:37:15Z

1

+50

Try running this:

Option Explicit

Public Sub disableAutoRefreshConnection()
    Dim cnn As WorkbookConnection

    For Each cnn In ActiveWorkbook.Connections
        With cnn.OLEDBConnection
            .BackgroundQuery = False
            If .Refreshing Then .CancelRefresh
            .EnableRefresh = False
            .RefreshOnFileOpen = False
            .RefreshPeriod = 0
        End With
    Next
End Sub

BackgroundQuery    Default: True;     If queries are asynchronous

CancelRefresh                         Cancels refresh operations in progress

EnableRefresh      Default: True;     If connection can be refreshed by the user

RefreshOnFileOpen  Default: False;    If it auto-updates each time workbook is opened

RefreshPeriod      Default: 0;        Number of minutes between refreshes

edited Sep 11, 2015 at 21:37

answered Sep 9, 2015 at 21:24

paul bica

10.8k4 gold badges27 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Przemyslaw Remin Over a year ago

It works. I think the key line of your code is .EnableRefresh = False Any ideas why refreshing procedure runs automatically?

paul bica Over a year ago

I'd need to duplicate you're setup and monitor settings to see when it gets turned on, so I'll do some tests, but in the meantime you could disable each line, one at the time to confirm the one you mentioned. Also you can change it to act only on "MyServerName" instead of looping through all existing connections (if you have more than this one)

paul bica Over a year ago

I duplicated your setup and used other connections as well but I wasn't able to see the issue (I used a small db). My guess is that one of the setting was changed from the default values - I added more info to the answer as a reference

Collectives™ on Stack Overflow

How to disable in Excel automatic refresh connection calling SQL stored procedure?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related