Queries to External Data on a Template? Help??? (Excel 2000 (9.0.3821 SR-1

[PalmAddict]

Hello All,

I have the strangest problem (and it may be me, but hey, I've never seen it) that I could use some help on.

I have created an Excel form for calculating Expenses to be reimbursed that is complaining about external queries when I try to save it as a template and re-open it.

A little evolutionary history first, though:

Document was originally created in Excel 2002, then modified in 2000 SR-1 and eventually saved as a template using the same 2000 SR-1.
It has VBA for the workbook. Very little actually, so here is the code:

VBAProject (Reimbursement Request.xlt)
+Microsoft Excel Objects
-Sheet1 (Sheet1)

Private Sub Worksheet_Change(ByVal Target As Range)
If ((Target.Column <> 4) And (Target.Column <> 5) And (Target.Column <> 7) And (Target.Column <> 8) And (Target.Column <> 9)) Then Exit Sub
If ((Target.Row < 8) Or (Target.Row > 35)) Then Exit Sub
Workbooks(1).RefreshAll
End Sub

--------End of Code--------

When I save this as a template I receive the following warning:

This workbook contains external data. Do you want Microsoft Excel to clear the data before saving the template and then automatically refresh the data whenever the template is opened? (Y/N/C)

Regardless of the answer I give (except Cancel of course), The resulting template file will give me the following upon opening:

Standard Enable Macros Warning

then:

--Query Refresh--
This workbook contains queries to external data that refresh automatically.

Queries are used to import external data into Excel, but harmful queries can be used to access confidential information or write information back to a database.

If you trust the source of this workbook, you can enable automatic query refresh. If you disable automatic query refresh, you can later refresh queries manually, if you are satisfied that the queries are safe.

[ ] Enable automatic query refresh for all workbooks and do not show the warning again

[Enable automatic refresh] [Disable automatic refresh]


-------------End of Warnings------------

There are NO external links, nor queries, nor code of any kind that I can see that points to any file or database ANYWHERE. Is there a technical reason I should be receiving the message even though everything is self contained?

Any assistance in solving this annoying conundrum would be greatly appreciated. Thank you.

Rusty

[pieterse]

I strongly suspect the codeline

Workbooks(1).RefreshAll

Causes the warning to appear.

What happens if you remove that line?

What is it supposed to do if there are no queries?

[PalmAddict]

The Workbook(1).RefreshAll did not solve the problem. It appears that Excel 2000 will force external link references to all ranges in a multi-range pivot table regardless of the fact that all ranges are within the same sheet, let alone workbook. There does not appear to be a way around this as I need the multi-range pivot table for calculation purposes and cannot force the ranges to be local only. The Pivot table calculates the totals of different companies expenses and summarizes them by company in the lower corner of this form for our accounting department. A conditional sum (SUMIF) would not work due to the number of companies that we are dealing with in the limited amount of space there is.

Is there a way to digitally sign this workbook template internally so that our employees don't have to be bothered with all of the warnings and miscellaneous falderal that Microsoft creates each time a work book is opened? A cost of free is preferred. Thanks.

Rusty

[pieterse]

You can create your own digital signature using a small program called selfcert.exe, located in the Office directory. Use that to sign the VBE project. Dunno if it will make the warnings go away though (they will show up the first time in any case, then you can tell XL to start trusting yourself <g>).