ODBC Connection

sfisher · #1 (**permalink**) 03-25-04, 16:33

Hi All,

I have an issue here...I have 9 reports/5 tabs each that I have to run
each week... In order to save myself a bunch of work I have created an ODBC connection to our SQL server database and am using Microsoft Query to get data and putting it into already formatted pivot tables. I have it set to refresh on open. The problem is I need to turn this function off, because I do not want the client to hit the refresh button, because if they do, an error pops up, and then a SQL Server login box pops up with our IP address to the database and the username. I would like to be able to open these reports on my end, refresh the data, save WITHOUT the connection and e-mail to the client. I have been trying for a few hours to figure this out....

Any thoughts?

DavidCoutts · #2 (**permalink**) 03-26-04, 04:59

I have an idea

try changing this to suit your needs

Code:

Private Sub Workbook_Open()
If Environ("Username") = "dcoutts" Then
    Worksheets(1).PivotTables(1).PivotCache.RefreshOnFileOpen = True
End If
End Sub

this searches for my username and runs the code if this is the case
You can put it in the Workbook open Event.
A draw back will be that your users will be asked if they wan't to enable macros or not.

Hope this helps

David

Internet Services

Web Development

Digital Marketing

Consumer Tech