Поиск шаблона таблицы Excel в скрипте vba

Я не думаю, что об этом спрашивали раньше, насколько я могу судить по поиску в Google, и это довольно конкретно.

У меня есть шаблон электронной таблицы Excel со встроенным кодом vba (файл 'xltm'), и я хочу найти путь к шаблону в электронной таблице, которая открывается шаблоном. Если это не имеет смысла, я приведу пример проблемы:

Пользователь открывает новую таблицу из файла шаблона, дважды щелкнув по ней. Файл шаблона находится в "C:\My Stuff" в качестве примера. Они заполняют ячейки, затем нажимают кнопку, которая создает текстовый файл после того, как они спрашивают, как бы они это назвали Затем код использует "Application.ActiveWorkbook.path" для сохранения файла в том же месте, где открыта электронная таблица (C:\My Stuff). Причины ошибки разрешения, так как электронная таблица еще не сохранена и поэтому не имеет пути. Итак, мой вопрос: есть ли способ найти путь к файлу оригинального шаблона? В примере это будет C:\My Stuff.

Я мог бы заставить пользователя сохранить файл перед созданием текстового файла, или я мог бы просто использовать файл xlsm. Но шаблон уменьшит вероятность того, что они испортят форматирование файла, и я не хочу просить их сохранять файл каждый раз, они, вероятно, скопируют и вставят большинство данных, а затем просто захотят текст файл, а не разные таблицы каждый раз.

Надеюсь, что это имеет смысл, и спасибо за любые ответы заранее.

Источник

4 ответа

Решение

Итак, мой вопрос: есть ли способ найти путь к файлу оригинального шаблона? В примере это будет C:\My Stuff.

Ты не можешь

Я уверен, что это не тот ответ, который вы искали, но, к сожалению, это ответ.

Если вы дважды щелкнули шаблон в проводнике, чтобы создать файл, то AFAIK, вы не сможете найти этот путь или имя шаблона, как вы можете сделать в MS Word с помощью oDoc.AttachedTemplate

альтернатива

Вставьте этот код в свой шаблон ThisWorkbook

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

А затем из новой книги, которую вы создали, дважды щелкнув шаблон, который вы можете использовать CurDir чтобы получить этот путь.

Недостатки вышеуказанного метода

  1. Workbook_Open() код переносится в новый файл
  2. Если пользователь изменит местоположение шаблона, вы не получите правильный путь
Источник

Есть способ делать то, что ты хочешь... вроде как. Имя или путь исходного шаблона не могут быть известны, потому что Excel мгновенно делает его копию для создания новой книги и не предоставляет никаких свойств с этой информацией. Но шаблон можно идентифицировать по уникальному тегу, к которому затем можно будет получить доступ в новой книге. И тег не должен быть чем-то необычным, например, секретной ячейкой, скрытым листом или скрытым текстовым полем. Он использует редко упоминаемую встроенную функцию Excel под названиемCustomDocumentProperties.

To do this requires a bit of extra VBA code that you may not be familiar with. Specifically, a VBA project reference to MSO.DLL, aka the Microsoft Office X Object Library, where "X" is the version of office. In my case, it's Office 2007, which is version 12, so it's Microsoft Office 12.0 Object Library.

To give the template a tag, simply add a custom document property to it. This can be done right from the main Excel window while you have the workbook open, as follows:

Click the Office Button > Prepare > Properties as shown below:

This opens the yellow Document Properties bar with various textboxes such as Author, Title, etc:

Then click Document Properties > Advanced Properties...

This opens the Workbook Properties window:

  • Click the Custom tab.
  • Type a property name.
  • Type a property value.
  • Click the Add button.

The new custom property will appear in the Properties list. Click the OK button:

Now for that bit of extra VBA I mentioned...

Open the VBA editor window, click Tools > References, and add a reference for Microsoft Office X Object Library (assuming you don't already have it), where X is your version of Office. If it doesn't appear in the list, you'll have to browse for it, and it could be in a variety of places depending on your version of Windows and Office.

This is how it appears IF you already have it. In my case it's already there and the box is checked:

If you scroll way down to the Microsoft items in the middle of the list, and you don't find it, poke the browse button. Note that if it is in the list, but you miss it, adding it again won't hurt anything. Plus sometimes it's just easier to do that instead of staring at the list for five minutes.

На моем 32-битном устройстве dev-beater путь следующий. Я думаю, что то же самое должно быть и для 64-битной Windows с 64-битным офисом, но я не уверен.

C:\Program Files\Common Files\Microsoft Shared\OFFICE12\MSO.DLL

На моем настольном компьютере с 64-разрядной ОС Windows с 32-разрядной версией Office это:

C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE12\MSO.DLL

Не забудьте изменить OFFICE12к имеющейся у вас версии Office, если это не так. Получив его, нажмитеOK кнопка.

Теперь код...

В обозревателе VBAProject дважды щелкните ThisWorkbookи добавьте следующий код. Если у вас уже естьSub Workbook_Open, вам придется интегрировать это в это, как вы сочтете нужным. Затем, как только вы увидите, как это работает, вы можете использовать его по мере необходимости:

Private Sub Workbook_Open()
    Dim xl      As Excel.Application
    Dim wb      As Excel.Workbook
    Dim dcps    As Office.DocumentProperties
    Dim dcp     As Office.DocumentProperty
    Dim tname   As String
    Const dq As String = """"
    Set xl = Excel.Application
    Set wb = xl.ActiveWorkbook
    Set dcps = wb.CustomDocumentProperties
    Set dcp = dcps.Item("ThisTemplateName")
    tname = dcp.Value
    MsgBox "This workbook was created from the template named " & dq & tname & dq & "..!"
End Sub
Источник

Проблема в том, что в несохраненной книге Application.ActiveWorkbook.path не определен. Вот метод, который предоставляет путь шаблона к несохраненной книге. Требуется, чтобы два пакетных (командных) файла находились в той же папке, что и шаблон Excel.

Первый пакетный файл SetVariable.cmd создает переменную среды.

      @echo off

rem Create an environment variable That Excel macro can read
setx TTxyPATHzz "%cd%"

Из-за того, как Windows обрабатывает переменные среды, значение не сразу становится доступным для текущего сеанса консоли. (Источник)

Поэтому необходим второй командный файл OpenInstance.cmd .

      @echo off
:: This boot-strap script enables the use of an environment variable which can be read by an Excel macro
:: Because of the way user environment variables are created, run SetVariable the first time you use template.

rem Create an environment variable That Excel macro can read
rem Setting the variable again will allow the location to change.
setx TTxyPATHzz "%cd%"

rem Invoke cmd.exe to open instance of Excel template
FOR /F "usebackq delims==" %%i IN (`dir *.xltm /B`) DO start excel /n "%%i"

В обработчике событий Workbook.Activate шаблона для ThisWorkbook включите следующее:

      Private Sub Workbook_Activate()

   Dim CurrentLocation As String

   CurrentLocation = Environ("TTxyPATHzz")

   ' Your workbook now knows where the template is located.
   
End Sub

Попросите пользователей один раз дважды щелкнуть SetVariable.cmd . После этого пользователям просто нужно дважды щелкнуть OpenInstance .

Выбор имени переменной среды произволен и предназначен для предотвращения случайной перезаписи существующей переменной.

Источник

Я работаю над аналогичной задачей. В качестве отправной точки я использую следующее:

Приложение MsgBox.TemplatesPath

https://learn.microsoft.com/en-us/office/vba/api/excel.application.templatesпуть

Источник
Другие вопросы по тегам