Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Mar 2003
    Location
    Los Angeles, California, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries to External Data on a Template? Help??? (Excel 2000 (9.0.3821

    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?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    Mar 2003
    Location
    Los Angeles, California, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries to External Data on a Template? Help??? (Excel 2000 (9.0.3821

    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

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries to External Data on a Template? Help??? (Excel 2000 (9.0.3821

    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>).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •