How to protect the query use for a Pivot that uses 'Get External D

I'm geting external data for a Pivot Table using the 'Get External Data' 
option, this works fine and retrieves the information from a SQL 2000 
database view.

In order to don't retrieve "all" the records, I use a filter (ie. where 
Company equal to Microsoft), that way the User only sees that amount of data 
that he is supposed to see.

The problem I have is that a power User that knows excel will be able to 
edit/remove that filter and see "all" data.

Of course I can create different database views (ie. one for each filter) 
and apply proper access rights to that view (ie. trusted connection) to the 
proper User.

But that means high maintenance.

Is there a way to password protect (or something like that) the actual 
query/filter so no power User can see/change it ?

Or is there another way that you can suggest to approach this problem.

Please advise.

Thanks,

Javier
0
Javier1 (40)
2/3/2005 8:51:02 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
749 Views

Similar Articles

[PageSpeed] 34

Javier

You have a pivot table based on external data, or you have an external data 
table in Excel and pivot table based on that?

How are the legitimate changes to the WHERE clause being made now?  Are you 
only concerned that all the records may be shown, or do you want to limit 
wha't shown to one of a few criteria?

I think this can be done, but depending on how you answer the above, I don't 
know how easy it will be.  You should be able to check the WHERE clause in 
code to make sure it's only returning what you want it to return.

-- 
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Javier wrote:
> I'm geting external data for a Pivot Table using the 'Get External
> Data' option, this works fine and retrieves the information from a
> SQL 2000 database view.
>
> In order to don't retrieve "all" the records, I use a filter (ie.
> where Company equal to Microsoft), that way the User only sees that
> amount of data that he is supposed to see.
>
> The problem I have is that a power User that knows excel will be able
> to edit/remove that filter and see "all" data.
>
> Of course I can create different database views (ie. one for each
> filter) and apply proper access rights to that view (ie. trusted
> connection) to the proper User.
>
> But that means high maintenance.
>
> Is there a way to password protect (or something like that) the actual
> query/filter so no power User can see/change it ?
>
> Or is there another way that you can suggest to approach this problem.
>
> Please advise.
>
> Thanks,
>
> Javier 


0
dkusleika (31)
2/3/2005 10:02:19 PM
This is what I did:

1) I create a database view on the SQL server (very long and complex query)
2) I open Excel, create a PivotTable and indicate "External Data Source" 
then click "Get Data", choose a DSN, select View, bring all fields, Filter by 
"Company = Microsoft"

I want to distribute that Excel, but the problem is that anyone can edit the 
Filter and get all the data.

Once the Filter (where clause) is defined inside Excel, I want to be able to 
password protect that screen so no User can change it. 

Regards,

Javier
 

"Dick Kusleika" wrote:

> Javier
> 
> You have a pivot table based on external data, or you have an external data 
> table in Excel and pivot table based on that?
> 
> How are the legitimate changes to the WHERE clause being made now?  Are you 
> only concerned that all the records may be shown, or do you want to limit 
> wha't shown to one of a few criteria?
> 
> I think this can be done, but depending on how you answer the above, I don't 
> know how easy it will be.  You should be able to check the WHERE clause in 
> code to make sure it's only returning what you want it to return.
> 
> -- 
> Dick Kusleika
> Excel MVP
> Daily Dose of Excel
> www.dicks-blog.com
> 
> Javier wrote:
> > I'm geting external data for a Pivot Table using the 'Get External
> > Data' option, this works fine and retrieves the information from a
> > SQL 2000 database view.
> >
> > In order to don't retrieve "all" the records, I use a filter (ie.
> > where Company equal to Microsoft), that way the User only sees that
> > amount of data that he is supposed to see.
> >
> > The problem I have is that a power User that knows excel will be able
> > to edit/remove that filter and see "all" data.
> >
> > Of course I can create different database views (ie. one for each
> > filter) and apply proper access rights to that view (ie. trusted
> > connection) to the proper User.
> >
> > But that means high maintenance.
> >
> > Is there a way to password protect (or something like that) the actual
> > query/filter so no power User can see/change it ?
> >
> > Or is there another way that you can suggest to approach this problem.
> >
> > Please advise.
> >
> > Thanks,
> >
> > Javier 
> 
> 
> 
0
Javier1 (40)
2/3/2005 10:55:01 PM
Is this done in Access or SQL?  What version of Office are you running.  In 
Excel 2003 you can actually password protect any editions to your 
spreadsheet.  I don't know how off the top of my head, but I bet if you went 
to an Excel Forum they'd be able to help you there better.  This is an Access 
forum, and it doesn't seem like your question applies to Access.  Good Luck..

"Javier" wrote:

> This is what I did:
> 
> 1) I create a database view on the SQL server (very long and complex query)
> 2) I open Excel, create a PivotTable and indicate "External Data Source" 
> then click "Get Data", choose a DSN, select View, bring all fields, Filter by 
> "Company = Microsoft"
> 
> I want to distribute that Excel, but the problem is that anyone can edit the 
> Filter and get all the data.
> 
> Once the Filter (where clause) is defined inside Excel, I want to be able to 
> password protect that screen so no User can change it. 
> 
> Regards,
> 
> Javier
>  
> 
> "Dick Kusleika" wrote:
> 
> > Javier
> > 
> > You have a pivot table based on external data, or you have an external data 
> > table in Excel and pivot table based on that?
> > 
> > How are the legitimate changes to the WHERE clause being made now?  Are you 
> > only concerned that all the records may be shown, or do you want to limit 
> > wha't shown to one of a few criteria?
> > 
> > I think this can be done, but depending on how you answer the above, I don't 
> > know how easy it will be.  You should be able to check the WHERE clause in 
> > code to make sure it's only returning what you want it to return.
> > 
> > -- 
> > Dick Kusleika
> > Excel MVP
> > Daily Dose of Excel
> > www.dicks-blog.com
> > 
> > Javier wrote:
> > > I'm geting external data for a Pivot Table using the 'Get External
> > > Data' option, this works fine and retrieves the information from a
> > > SQL 2000 database view.
> > >
> > > In order to don't retrieve "all" the records, I use a filter (ie.
> > > where Company equal to Microsoft), that way the User only sees that
> > > amount of data that he is supposed to see.
> > >
> > > The problem I have is that a power User that knows excel will be able
> > > to edit/remove that filter and see "all" data.
> > >
> > > Of course I can create different database views (ie. one for each
> > > filter) and apply proper access rights to that view (ie. trusted
> > > connection) to the proper User.
> > >
> > > But that means high maintenance.
> > >
> > > Is there a way to password protect (or something like that) the actual
> > > query/filter so no power User can see/change it ?
> > >
> > > Or is there another way that you can suggest to approach this problem.
> > >
> > > Please advise.
> > >
> > > Thanks,
> > >
> > > Javier 
> > 
> > 
> > 
0
2/3/2005 11:07:04 PM
I geting the data from SQL, but the same steps are done in side Excel for 
both SQL or MS-Access, so the question is the same for either one.

I'm using Excel 2003-SP1

I thought this was an Excel forum, did not know is only for MS-Access, do 
you have a link/URL for a Excel forum ?
-------------------------------

"Micah Chaney" wrote:

> Is this done in Access or SQL?  What version of Office are you running.  In 
> Excel 2003 you can actually password protect any editions to your 
> spreadsheet.  I don't know how off the top of my head, but I bet if you went 
> to an Excel Forum they'd be able to help you there better.  This is an Access 
> forum, and it doesn't seem like your question applies to Access.  Good Luck..
> 
> "Javier" wrote:
> 
> > This is what I did:
> > 
> > 1) I create a database view on the SQL server (very long and complex query)
> > 2) I open Excel, create a PivotTable and indicate "External Data Source" 
> > then click "Get Data", choose a DSN, select View, bring all fields, Filter by 
> > "Company = Microsoft"
> > 
> > I want to distribute that Excel, but the problem is that anyone can edit the 
> > Filter and get all the data.
> > 
> > Once the Filter (where clause) is defined inside Excel, I want to be able to 
> > password protect that screen so no User can change it. 
> > 
> > Regards,
> > 
> > Javier
> >  
> > 
> > "Dick Kusleika" wrote:
> > 
> > > Javier
> > > 
> > > You have a pivot table based on external data, or you have an external data 
> > > table in Excel and pivot table based on that?
> > > 
> > > How are the legitimate changes to the WHERE clause being made now?  Are you 
> > > only concerned that all the records may be shown, or do you want to limit 
> > > wha't shown to one of a few criteria?
> > > 
> > > I think this can be done, but depending on how you answer the above, I don't 
> > > know how easy it will be.  You should be able to check the WHERE clause in 
> > > code to make sure it's only returning what you want it to return.
> > > 
> > > -- 
> > > Dick Kusleika
> > > Excel MVP
> > > Daily Dose of Excel
> > > www.dicks-blog.com
> > > 
> > > Javier wrote:
> > > > I'm geting external data for a Pivot Table using the 'Get External
> > > > Data' option, this works fine and retrieves the information from a
> > > > SQL 2000 database view.
> > > >
> > > > In order to don't retrieve "all" the records, I use a filter (ie.
> > > > where Company equal to Microsoft), that way the User only sees that
> > > > amount of data that he is supposed to see.
> > > >
> > > > The problem I have is that a power User that knows excel will be able
> > > > to edit/remove that filter and see "all" data.
> > > >
> > > > Of course I can create different database views (ie. one for each
> > > > filter) and apply proper access rights to that view (ie. trusted
> > > > connection) to the proper User.
> > > >
> > > > But that means high maintenance.
> > > >
> > > > Is there a way to password protect (or something like that) the actual
> > > > query/filter so no power User can see/change it ?
> > > >
> > > > Or is there another way that you can suggest to approach this problem.
> > > >
> > > > Please advise.
> > > >
> > > > Thanks,
> > > >
> > > > Javier 
> > > 
> > > 
> > > 
0
Javier1 (40)
2/4/2005 1:49:05 PM
Javier

I don't think there's any built-in way to do what you want.

One option is to disable the Wizard control.  If the user can't get to the 
wizard, then he can't easily change the external data query.  You might 
disable those controls in the Workbook_Activate and _Deactivate events.

Private Sub Workbook_WindowActivate(ByVal Wn As Window)

    Dim ctlWizard As CommandBarControls
    Dim ctlIndex As CommandBarControl

    Set ctlWizard = Application.CommandBars.FindControls(, 457)

    For Each ctlIndex In ctlWizard
        ctlIndex.Enabled = False
    Next ctlIndex

End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)

    Dim ctlWizard As CommandBarControls
    Dim ctlIndex As CommandBarControl

    Set ctlWizard = Application.CommandBars.FindControls(, 457)

    For Each ctlIndex In ctlWizard
        ctlIndex.Enabled = True
    Next ctlIndex

End Sub

Another tact may be to test the CommandText property of the PT every time 
the worksheet is calculated (you can use the PT events if you have XP or 
newer, but you have to use the Calculate event for earlier versions)

Private Sub Worksheet_Calculate()

    Dim sSql As String
    Dim pc As PivotCache

    'Hardcode the sql you want
    sSql = "SELECT TblTest.TestId, TblTest.TestName, " & _
        "TblTest.TestNum FROM `C:\Dick\Temp`.TblTest TblTest"

    Set pc = Me.PivotTables(1).PivotCache

    'Make sure the sql wasn't changed
    If pc.CommandText <> sSql Then
        pc.CommandText = sSql
        pc.Refresh
    End If

End Sub

This example uses an Access database because I don't have SQLServer loaded 
here.  If you know what your CommandText property is supposed to look like, 
then use it.  If not, go to the Immediate Window and type

?Sheet1.PivotTables(1).PivotCache.CommandText

and see what it returns.

There's really know reason that you can't use both of these methods 
together.  No matter what you do, if the user cracks your vba password, this 
is all worthless.  If the data is so critical that you can't afford to have 
someone else see it, then unfortunately you can't use Excel because the 
protection just isn't strong enough.  It sounds like you just need to thwart 
some efforts by mischievous users though.  These two methods should help in 
that regard.

-- 
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Javier wrote:
> This is what I did:
>
> 1) I create a database view on the SQL server (very long and complex
> query) 2) I open Excel, create a PivotTable and indicate "External
> Data Source" then click "Get Data", choose a DSN, select View, bring
> all fields, Filter by "Company = Microsoft"
>
> I want to distribute that Excel, but the problem is that anyone can
> edit the Filter and get all the data.
>
> Once the Filter (where clause) is defined inside Excel, I want to be
> able to password protect that screen so no User can change it.
>
> Regards,
>
> Javier
>
>
> "Dick Kusleika" wrote:
>
>> Javier
>>
>> You have a pivot table based on external data, or you have an
>> external data table in Excel and pivot table based on that?
>>
>> How are the legitimate changes to the WHERE clause being made now?
>> Are you only concerned that all the records may be shown, or do you
>> want to limit wha't shown to one of a few criteria?
>>
>> I think this can be done, but depending on how you answer the above,
>> I don't know how easy it will be.  You should be able to check the
>> WHERE clause in code to make sure it's only returning what you want
>> it to return.
>>
>> --
>> Dick Kusleika
>> Excel MVP
>> Daily Dose of Excel
>> www.dicks-blog.com
>>
>> Javier wrote:
>>> I'm geting external data for a Pivot Table using the 'Get External
>>> Data' option, this works fine and retrieves the information from a
>>> SQL 2000 database view.
>>>
>>> In order to don't retrieve "all" the records, I use a filter (ie.
>>> where Company equal to Microsoft), that way the User only sees that
>>> amount of data that he is supposed to see.
>>>
>>> The problem I have is that a power User that knows excel will be
>>> able to edit/remove that filter and see "all" data.
>>>
>>> Of course I can create different database views (ie. one for each
>>> filter) and apply proper access rights to that view (ie. trusted
>>> connection) to the proper User.
>>>
>>> But that means high maintenance.
>>>
>>> Is there a way to password protect (or something like that) the
>>> actual query/filter so no power User can see/change it ?
>>>
>>> Or is there another way that you can suggest to approach this
>>> problem.
>>>
>>> Please advise.
>>>
>>> Thanks,
>>>
>>> Javier 


0
dkusleika (31)
2/4/2005 2:13:51 PM
Actually I realized that whatever we do in the Excel, the User can open a new 
Excel and do a PivotTable-GetExternalData and point to the SQL database-view 
and get "all data". I have to put the protection on the database-view itself. 
I will do this by adding on the WHERE clause a condition based on the 
SYSTEM_USER (the Domain\NT-LoginID of the person accessing the database-view)
Thanks all for your help !!

"Dick Kusleika" wrote:

> Javier
> 
> I don't think there's any built-in way to do what you want.
> 
> One option is to disable the Wizard control.  If the user can't get to the 
> wizard, then he can't easily change the external data query.  You might 
> disable those controls in the Workbook_Activate and _Deactivate events.
> 
> Private Sub Workbook_WindowActivate(ByVal Wn As Window)
> 
>     Dim ctlWizard As CommandBarControls
>     Dim ctlIndex As CommandBarControl
> 
>     Set ctlWizard = Application.CommandBars.FindControls(, 457)
> 
>     For Each ctlIndex In ctlWizard
>         ctlIndex.Enabled = False
>     Next ctlIndex
> 
> End Sub
> 
> Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
> 
>     Dim ctlWizard As CommandBarControls
>     Dim ctlIndex As CommandBarControl
> 
>     Set ctlWizard = Application.CommandBars.FindControls(, 457)
> 
>     For Each ctlIndex In ctlWizard
>         ctlIndex.Enabled = True
>     Next ctlIndex
> 
> End Sub
> 
> Another tact may be to test the CommandText property of the PT every time 
> the worksheet is calculated (you can use the PT events if you have XP or 
> newer, but you have to use the Calculate event for earlier versions)
> 
> Private Sub Worksheet_Calculate()
> 
>     Dim sSql As String
>     Dim pc As PivotCache
> 
>     'Hardcode the sql you want
>     sSql = "SELECT TblTest.TestId, TblTest.TestName, " & _
>         "TblTest.TestNum FROM `C:\Dick\Temp`.TblTest TblTest"
> 
>     Set pc = Me.PivotTables(1).PivotCache
> 
>     'Make sure the sql wasn't changed
>     If pc.CommandText <> sSql Then
>         pc.CommandText = sSql
>         pc.Refresh
>     End If
> 
> End Sub
> 
> This example uses an Access database because I don't have SQLServer loaded 
> here.  If you know what your CommandText property is supposed to look like, 
> then use it.  If not, go to the Immediate Window and type
> 
> ?Sheet1.PivotTables(1).PivotCache.CommandText
> 
> and see what it returns.
> 
> There's really know reason that you can't use both of these methods 
> together.  No matter what you do, if the user cracks your vba password, this 
> is all worthless.  If the data is so critical that you can't afford to have 
> someone else see it, then unfortunately you can't use Excel because the 
> protection just isn't strong enough.  It sounds like you just need to thwart 
> some efforts by mischievous users though.  These two methods should help in 
> that regard.
> 
> -- 
> Dick Kusleika
> Excel MVP
> Daily Dose of Excel
> www.dicks-blog.com
> 
> Javier wrote:
> > This is what I did:
> >
> > 1) I create a database view on the SQL server (very long and complex
> > query) 2) I open Excel, create a PivotTable and indicate "External
> > Data Source" then click "Get Data", choose a DSN, select View, bring
> > all fields, Filter by "Company = Microsoft"
> >
> > I want to distribute that Excel, but the problem is that anyone can
> > edit the Filter and get all the data.
> >
> > Once the Filter (where clause) is defined inside Excel, I want to be
> > able to password protect that screen so no User can change it.
> >
> > Regards,
> >
> > Javier
> >
> >
> > "Dick Kusleika" wrote:
> >
> >> Javier
> >>
> >> You have a pivot table based on external data, or you have an
> >> external data table in Excel and pivot table based on that?
> >>
> >> How are the legitimate changes to the WHERE clause being made now?
> >> Are you only concerned that all the records may be shown, or do you
> >> want to limit wha't shown to one of a few criteria?
> >>
> >> I think this can be done, but depending on how you answer the above,
> >> I don't know how easy it will be.  You should be able to check the
> >> WHERE clause in code to make sure it's only returning what you want
> >> it to return.
> >>
> >> --
> >> Dick Kusleika
> >> Excel MVP
> >> Daily Dose of Excel
> >> www.dicks-blog.com
> >>
> >> Javier wrote:
> >>> I'm geting external data for a Pivot Table using the 'Get External
> >>> Data' option, this works fine and retrieves the information from a
> >>> SQL 2000 database view.
> >>>
> >>> In order to don't retrieve "all" the records, I use a filter (ie.
> >>> where Company equal to Microsoft), that way the User only sees that
> >>> amount of data that he is supposed to see.
> >>>
> >>> The problem I have is that a power User that knows excel will be
> >>> able to edit/remove that filter and see "all" data.
> >>>
> >>> Of course I can create different database views (ie. one for each
> >>> filter) and apply proper access rights to that view (ie. trusted
> >>> connection) to the proper User.
> >>>
> >>> But that means high maintenance.
> >>>
> >>> Is there a way to password protect (or something like that) the
> >>> actual query/filter so no power User can see/change it ?
> >>>
> >>> Or is there another way that you can suggest to approach this
> >>> problem.
> >>>
> >>> Please advise.
> >>>
> >>> Thanks,
> >>>
> >>> Javier 
> 
> 
> 
0
Javier1 (40)
2/4/2005 3:09:01 PM
Reply:

Similar Artilces:

Current Date in Query
Hi All, Hoping you can help. I'm using the following to get current age in a query: NewCurrAge: (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date())))\12 & " yrs " & (DateDiff("m",[DOB],Date())+(Day([DOB])>Day(Date()))) Mod 12 & " mos" My BIG problem is that I also want to pull certain ages, especially 9 to 13 for one query and 14 to 17 in another query. So, in the same query for criteria I used: Between 9 yrs 0 mos And 13 yrs 12 mos Error: the expression contains invalid syntax. you entered an operator witho...

Problem: Attribute "border" exists, but can not be used for this element
hi asp.net 3.5 when validating my site I get the error mention in the subject of this post, here is the source code generating the problem. I mean thisis the source code taken from the browser: <span><input type="image" name="ctl00$Login2$LoginView1$Login1$Submit" id="ctl00_Login2_LoginView1_Login1_Submit" src="Images/Go.gif" alt="alt text is here" onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions(&quot;ctl00$Login2$LoginView1$Login1$Submit&quot;, &quot;&quot;, true, &...

Pivot Table Totals When Using Pages
Dear fellow Excel 2003 users, I have a pivot table that lists hours of several employees. A manager asked if there was a way to view just the non-billable hours. No sweat, I added a page field and selected non-billable and the sum of hours and the grand total showed just the non-billable hours. However, he was hoping to see the data section of the pivot table show just the non-billable hours with a column next to it showing the percentage of non-billable compared to the grand total of billable and non-billable. Problem: after scouring Google and the Help file, I cannot find a way to kee...

Regularly uses up all it's memory
This is a Dell L700 CXE, 700 MHZ, 128 MB RAM In normal operation, W98SE uses all but about 4 MB free RAM. I look at my process utilities, see nothing unusual or abnormal. I run few installs, have few that start with windows, same apps that I use on other machines, but they have more memory. I used to see some articles by experts when W98 was current that that was normal and expected, not harmful. Comment? ms On 01/23/2010 03:29 PM, ms wrote: > This is a Dell L700 CXE, 700 MHZ, 128 MB RAM > > In normal operation, W98SE uses all but about 4 MB free RAM. I look...

Create Email From Access W/Information From Form or Query in
I have designed Databases for many years now, but never attempted this: I need to know how to create an email with information located in the active form or off of a query. I would like to have the email automatically generated with the To Address, The Subject Line and the Body of the Text. I would like that email to open but not to send until the operator has a chance to make modifications. (one record per email). -- Mark Matzke Hi Mark, You could try the following to start: Place a button on a form and place the following behind the on click event Dim objApp as New Outlook.Applic...

KB917422, get error code 0x80080008
when i search in add/remove programs the program is not there...how do i fix this problem or do I just go ahead and reinstall it as is? I'm not very puter illeterate and can use some help here? -- Theresaft Always state your full Windows version (e.g., Windows XP Service Pack 1) when posting in a forum or newsgroup. Please do so in your next reply. What problem? KB917422 was released years ago (i.e., 08 August 2006). -- ~Robear Dyer (PA Bear) MS MVP-IE, Mail, Security, Windows Client - since 2002 www.banthecheck.com theresaft wrote: > when i search in add/remov...

protecting outlined data
I want to be able to use the expand/collapse function in outlines when the worksheet is protected. Copy this macro in a normal module It will run automatic when you open the workbook You can also use the workbook open event Sub auto_open() With Worksheets("sheet1") .Protect Password:="rbelecki", userinterfaceonly:=True .EnableOutlining = True End With End Sub Look in the VBA help for more information about protect(userinterfaceonly) -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "rbelecki" <rbelecki@l...

Pivot Charts
Hi all, I'd like my pivot chart to do 2 things: 1) If I select an item from one of my page field dropdowns, I'd like the dropdown items on another page field to filter according to what was selected. Example: if the first page field is "Teams" and I choose a team from the list, I want the other page field "Players" to offer only the players from that team. Possible? 2) I used the macro recorder to automatically replace one data item in the pivot chart with another. Example (football): I assigned a macro to a button called "Pass Attempts", which will...

curly quotes became straight, can't get curly bakc
something changed curly quotes to straight, and now I can't resume curly quotes. I assume you've already checked your AutoCorrect options to make sure replace straight quotes with curly quotes is checked, so let's look at the other simple fixes first. Have you recently changed to a new font? Some fonts don't support curly quotes. What version of Word are you using? Jerry "John Worsley Simpson" wrote: > something changed curly quotes to straight, and now I can't resume curly > quotes. And in some fonts it's really hard to recogni...

Password Protect Outlook #2
I work in an offcie environment and sometimes have to leave my PC unattended for just a few minutes. Unfortunately some confidential emails have been read by my staff and I need a way of safeguarding them other than turning off the PC, is there any way I can password protect access into my Outlook 2000, so all I do is close Outlook. (Sorry still operating under W95) Many thanks You can set a password on a Personal Folders File. If the folder list isn't visible in Outlook, click View | Folder List. Right-click the root folder (usually "Personal Folders" or "Outlook T...

Using Photo Watermarks
I wish to use a colour photo as a watermark in the design of a DVD insert when using Publisher 2000. However, as the colours are strong, the title text (in black) in the foreground is difficult to read. How can I make the colours paler so as to make the title text more legible? It seems to be possible to do this when using clip art and a single colour, but not photos. The only possible solution I have found is to convert the photo to grey scale, and then use the colour palette to choose the intensity of the grey. BUT - I want to retain the colours. All suggestions gratefully received. Sp...

Quicken Password protected
I just got Money 2004 Deluxe, I am trying to import my data from Quicken and the program says there is a password for my quicken account that is required, but I never had a password. No way to get past this. Any ideas? If your data file is Q04, Money04 can only deal with Q03 or older. Otherwise, who knows? "Stephen Weiss" <stephenaweiss@comcast.net> wrote in message news:012f01c3cb22$9a957ac0$a101280a@phx.gbl... > I just got Money 2004 Deluxe, I am trying to import my > data from Quicken and the program says there is a password > for my quicken account that is req...

I get smtp 5.7.1 errors when sending to specific addresses
Hello all, For some reason, when sending to specific external e-mail addresses, users get the error: recipient@recipientdomain.com on 2/8/2005 5:12 PM You do not have permission to send to this recipient. For assistance, contact your system administrator. <myserver.mydomain.Com #5.7.1 smtp;550 5.7.1 Access denied> In 50% of the cases, we can send it after we flush the DNS cache on the server and workstation. Sometimes that does not fix the problem though Does anyone have any idea what may be causing this? Thanks in advance for any advice Have you trie...

How to I use ** without Excel thinking I want to type a formula?
I am using Excel (Office 2003) to create a table and I need to use two of these: * to indicate significant results for a research study. How do I tell Excel that I just want to display this symbol after a three digit number without it thinking I want to type a formula? Turn off Lotus transition under tools>options>transition and uncheck transition formula entry Regards, Peo Sjoblom "Buff" wrote: > I am using Excel (Office 2003) to create a table and I need to use two of > these: * to indicate significant results for a research study. How do I tell > Excel th...

Pivot Table error message in Excel for Mac 2008
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) When I try to create a Pivot Table Report, I get the following error message: <br><br>&quot;Filename.xlsx cannot be accessed. The file may be read-only, or you may be trying to access a read-only location. Or, the server the document is stored on may not be responding&quot; <br><br>The document is stored on my C: drive and is not read-only. I have tried copying my data into a new Excel file with the same result. <br><br>Any ideas? You've wondered into the Excel for Mac forum. go ...

Using an external reference to a filename as a date
Hi, I am just new to this forum and am in great need of help :-) I am trying to create an external reference to an excel file called... 'OS orders 15-06-04.xls'. The formula I am wanting to use (shortene for simplicity) is... =VLOOKUP($a$1,[OS orders 15-06-04.xls]OS Sales orders'!$I:$S,4,false) I am trying to create a macro that will write this formula, copy th formula over a wide range of blank cells, then copy and paste th values for presentation with other information. My problem is... the date in the external references filename change everyday 'OS orders *15-06-04*.xls...

How to protect 2. axis in an Excel Chart to stay if data changes
A Pivot table is created on Tab1 in an Excel workbook. On Tab2 there is a Line Chart created with data from Tab 1 using 2 axis. If data in Tab1 change the 2nd axis on Tab 2 disappears, jumps ober and overrules axis 1. How can I protect chart area with 2 axis being able to change referring data in the pivot table. ...

Password protection on split database
I have a database which is split into front end and back end. The front end is password protected but the backend isn't Is it possible to protect both with the same password? I've tried protecting the backend with a password but when I open the front end it can't "see" the backend. Any ideas? Thanks Tony Put a password on the backend, then, after opening the front-end, use the Linked Table Manager to update the links (just like you would if you had moved the backend). You should be prompted for the 'missing' password during this process. You should only...

grouping dates in pivot table
I have an outstanding checklist which contains two accounts (account 2110 and 2189) I have created a pivot table using the date of the check on the left, the account on top and the amounts as the data. Example Account 2110 2189 Date 7/3/04 105 176 7/6/04 116 218 8/3/04 786 197 8/7/04 214 767 I am trying to group the dates so it will read total amount of outstanding checks for the month. Example: Account 2110 2189 Date July '04 ...

pivot table empty cell
I need to have an empty cell be in the pivot table. I have multiple pivot tables I have generated for a report. I have created a worksheet that reads the data from the pivot table (so it looks nicer). I have tried to check the box in options that says to put a 0 or another symbol (*) in an empty cell and it doesn't work. I want people to be able to input their own data and refresh the pivot table. If I have four response options (sometimes, always, never, usually) and the new data does not have data for one of the options then the table that reads the data gets messed up with its numb...

query question 03-11-08
greetings Lets say I have 2 tables: the 1st table has a field, ID1, with records a,b,c,d... and the 2nd table has a field, ID2, with records p,q,r,s... What is a good way to make a query with Fields ID1 and ID2 that show all combinations of 2 records, 1 from each table? Like this: ID1 ID2 a p a q a r a s ....etc b p b q ....etc thank you -- cinnie On Mon, 10 Mar 2008 20:41:01 -0700, cinnie <cinnie@discussions.microsoft.com> wrote: >greetings > >Lets say I have 2 tables: the 1st table has a field, ID1, with records >a,b,c,d... ...

Protection
Hi everyone OK my problem is I am using an offie computer that everyone has access to and we cannot - and do not want to - change this. Is there a way that I can put a password for my profile on Microsoft outlook so that no one can read the new AND the old messages found in my inbox? You can put a password on your PST file, presuming you use a PST file... Emad Kamel wrote: > Hi everyone, > OK my problem is I am using an offie computer that everyone has > access to and we cannot - and do not want to - change this. Is there > a way that I can put a password for my profile on Micr...

change email account outlook uses
To change the email account I use for Outlook, the instructions in outlook help say to go to the Tools menu & select Account Settings. But there is no Tools menu in Outlook 2007 on any of the toolbars. Please advise "weekend" <weekend@discussions.microsoft.com> wrote in message news:CBC2D964-B444-42A9-B6F8-DAE5AD2084E5@microsoft.com... > To change the email account I use for Outlook, the instructions in outlook > help say to go to the Tools menu & select Account Settings. But there is > no > Tools menu in Outlook 2007 on any of the toolbars. P...

Duplicates in query
I run my query but instead of listing just the childs name for each social worker, it lists all the childrens names under each social worker. post the sql of your query. View: SQL from the menu and then copy and paste. Without seeing your query, I would guess that you have two tables SocialWorkers Clients (children) and that you have put them both into a query, but have not set up a join between the two tables - for instance from SocialWorker.ID to Clients.ChildSocialWorkerID If you don't set up a join on the tables (drag from one field to the corresponding field in the other tabl...

Update Query and record lock violations
I have a local table constructed from two remote sqltables. I add records successfully via an append query in Access 2003. I am attempting to modify date records that have changed in the remote table to the local table (they change occasionally in the sqltable) via update query. I have the query set to pull date and time from the sqltable (there is a common key to both tables that acts as the master key in the local table) and update the date and time in the local table where the date and time are not equal in both tables. It seems to work - it returns exactly the number of records ...