查找Excel电子表格的模板路径 [英] Finding an Excel Spreadsheet's template path

问题描述

我有一个带有VBA代码("xltm"文件)的Excel电子表格模板.

I have an Excel spreadsheet template with VBA code (an 'xltm' file).

我想在模板打开的电子表格中找到模板的路径.

I want to find the path of the template in the spreadsheet that is opened by the template.

问题示例:

• 用户通过双击"C:\ My Stuff"中的模板文件打开一个新的电子表格.
• 他们填写单元格，然后在询问他们要怎么称呼之后单击创建文本文件的按钮.
• 然后代码使用Application.ActiveWorkbook.path将文本文件保存在打开电子表格的位置.
• 这会导致权限错误，因为电子表格尚未保存，因此没有路径.

• A user opens a new spreadsheet from the template file in 'C:\My Stuff' by double clicking it.
• They fill in the cells, then click a button that creates a text file after asking them what to call it.
• The code then uses Application.ActiveWorkbook.path to save the text file in the same place as the spreadsheet is open.
• This causes a permission error, as the spreadsheet hasn't yet been saved, and as such, doesn't have a path.

是否可以找到原始模板的文件路径?在示例C:\ My Stuff中.

Is there a way to find the original template's file path? In the example C:\My Stuff.

我可以让用户在创建文本文件之前保存文件，或者我可以只使用xlsm文件，但是模板可以最大程度地减少混乱文件格式的机会.我不想让他们每次都保存文件.他们可能会复制并粘贴大多数数据，然后只需要文本文件，而不是每次都使用不同的电子表格.

I could make the user save the file before the text file is created, or I could just use an xlsm file but a template will minimise the chance of messing up the formatting of the file. I don't want to ask them to save the file each time. They'll probably copy and paste most data in, then just want the text file, not a different spreadsheet each time.

推荐答案

所以我的问题是:有没有办法找到原始模板的文件路径?在示例中，这将是C:\ My Stuff.

So my question is: Is there a way to find the original template's file path? In the example this would be C:\My Stuff.

您不能.

我确定这不是您要找的答案，但不幸的是，这是答案.

I am sure this is not the answer you were looking for but unfortunately this is the answer.

如果双击资源管理器中的模板以创建文件，则AFAIK不能像使用oDoc.AttachedTemplate

If you double clicked the template in explorer to create a file then AFAIK you cannot find that path or template name like you can do in MS Word using oDoc.AttachedTemplate

替代

在您的模板中，将此代码放入ThisWorkbook

In your template, put this code in ThisWorkbook

Private Sub Workbook_Open()
    ChDir "C:\Blah Blah" '<~~ Change this to the templates path
End Sub

然后在通过双击模板创建的新工作簿中，可以使用CurDir获取该路径.

And then from the new workbook that you created by double clicking the template you can use CurDir to get that path.

上述方法的缺点

1. Workbook_Open()代码被带到新文件中
2. 如果用户更改模板的位置，那么您将找不到正确的路径

1. The Workbook_Open() code gets carried over to the new file
2. If the user changes the location of the template then you will not get the right path

这篇关于查找Excel电子表格的模板路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！