Data Query Issue (2003)

Topic

New Reply

I have a view on a SQL Server 2000 database that returns a list of companies to be worked on. All of the people in the department have read/write access to the database.

I have an Excel spreadsheet that uses Microsoft Query to pull the data from this view. The spreadsheet is stored in a folder that is read/write for everyone in the department.

The spreadsheet has the following macro:
Private Sub Workbook_Open()

Sheets(“Untouched”).QueryTables(1).Refresh

ThisWorkbook.Save

End Sub

Issue
I always get the current list whenever I open the spreadsheet. However, a few people in the department appear to get ‘old’ data. When they call to complain, I open the spreadsheet and get the current data. If they try again, after I have opened/closed the spreadsheet, they then get the current data. I know that they are opening the correct spreadsheet because I watched them open it, and saw the incorrect results.

I’ve never seen this situation with Data Query and am at a loss as to why the data is not current for everyone.

Any suggestions?

T

Reply | Quote

Replies

Stupid question: do they open the workbook with macros enabled? If macro security is set to High, or if it is set to Medium and they click No in the macro security prompt, the data will not be refreshed.

Reply | Quote

I’m not sure, I’ll have to find out.

I have my macro security set to Low.

Reply | Quote

If it is set to low, macros will always be executed, without asking you for permission. For this reason, it is recommended to set it to Medium.
If you tick the check box “Trust all installed add-ins and templates” in the Trusted Sources tab of Tools | Macro | Security, you won’t be nagged to allow macros in Personal.xls etc.

Reply | Quote

In addition to Hans’ comments, see the thread starting at post 603,335, particularly Jan Karel’s Star Post in that thread.

Reply | Quote