Word 2007 VBA Macro to Assemble a Manual from Chapters

The following VBA macro allows you to scan a directory for subdirectories and add INCLUDETEXT fields with additional bookmarks to create a master document which can be used to create several different versions of the same content. It will also create stubs for each chapter, based on the admin types.

This is very helpful for manuals.

This macro was used with a master docm file which was supported by a directory called Chapters. The Chapters directory had a numerically named subdirectory for each chapter, and within each chapter there were documents for all the admin types, as well as a Common.docm file which included text for all admin types. The content control is in the admin type file, which can be empty to omit the chapter from the manual, a single INCLUDETEXT of Common.docm to just display the common text, or any other appropriate content.

MasterDocument.docm
Chapters

  • 1

    • Common.docm
    • System.docx
    • Application.docx
    • Editor.docx
    • Guest.docx
  • 2

    • Common.docm
    • System.docx
    • Application.docx
    • Editor.docx
    • Guest.docx

The document needs two fields, AdminType and BaseDir. AdminType must be one of those listed in the AdminTypes array, BaseDir must be set to the base directory for the document. I used ASK fields, but a SET should be fine as well.

The document also needs two bookmarks, “ChapterBlockStart” and “ChapterBlockEnd", which indicate where the chapter includes are to be placed.

Chapter numbering is managed with a SEQ field in the master document. Each chapter’s common.docm has a SEQ reference which increments the chapter number. This ensures the chapter numbers are contiguous even if some chapters are omitted for a specific admin type.

Option Base 1
Option Explicit
Global AdminTypes

Sub Init()
'   Initialize AdminTypes array
    AdminTypes = Array("System", "Application", "Editor", "Guest")
End Sub


Sub FindChapters()

Init

Dim CleanStart, CleanEnd, CleanRange
Dim I, L, T, D, Done
Dim Range
Dim BaseDir, ChapterDir, RootDir
Dim AdminTypeRange, BaseDirRange

' This code removes the old chapters, so you can run the macro on a document to update it if you have
' added more chapters.

CleanStart = ActiveDocument.Bookmarks("ChapterBlockStart").Range.Start
CleanEnd = ActiveDocument.Bookmarks("ChapterBlockEnd").Range.End
Set CleanRange = ActiveDocument.Range(Start:=CleanStart, End:=CleanEnd)
CleanRange.MoveStart wdCharacter, 1
CleanRange.MoveEnd wdCharacter, -1
If CleanRange.Characters.Count > 1 Then CleanRange.Cut

' Position the range at the start of the ChapterBlock
Set Range = ActiveDocument.Bookmarks("ChapterBlockStart").Range

' Get the value of BaseDir.  This is probably terribly inefficient.
I = 1
L = ActiveDocument.Fields.Count()
For I = 1 To L
    T = ActiveDocument.Fields.Item(I).Code.Text
    If (InStr(1, T, "BaseDir", vbTextCompare) <> 0) Then
        BaseDir = ActiveDocument.Fields.Item(I).Result()
    End If
Next

' Loop through all the chapters.  The directory is named 'Chapters'
D = FileSystem.CurDir()
RootDir = BaseDir + "\Chapters\"
I = 1
' This is actually an infinite loop.  It uses On Error to terminate when it runs out of chapters
Done = False
While Not Done
    ' Name the chapter directory
    ChapterDir = RootDir + Trim(Str$(I))
    ' If you can't change into the chapter directory, you're done
    On Error GoTo AllDone
    ' Change into the chapter directory
    FileSystem.ChDir (ChapterDir)
    ' This ensures there is a file for all the admin types for that chapter
    CreateChapter (Trim(Str$(I)))
    ' Each chapter is in a section, on a new page
    Range.Sections.Add Range, wdSectionNewPage

    ' Create the INCLUDETEXT tag
    Range.Fields.Add Range, wdFieldIncludeText, Chr(34) + "BaseDir\\Chapters\\" + Trim(Str$(I)) + "\\AdminType.docx" + Chr(34)

    ' Add in the BaseDir and AdminType fields, so the document is portable and adapts to the admin type
    Set BaseDirRange = Range.Duplicate
    BaseDirRange.Find.MatchCase = True
    BaseDirRange.Find.Text = "BaseDir"
    BaseDirRange.Find.Execute
    BaseDirRange.Fields.Add BaseDirRange, wdFieldEmpty, , False
    Set AdminTypeRange = Range.Duplicate
    AdminTypeRange.Find.Text = "AdminType"
    AdminTypeRange.Find.Execute
    AdminTypeRange.Fields.Add AdminTypeRange, wdFieldEmpty, , False
    AdminTypeRange.Collapse wdCollapseEnd

    ' Advance the range pointer
    Range.MoveEnd wdSection, 1
    Range.Collapse wdCollapseEnd

    ' Increment the chapter counter
    I = I + 1
Wend
AllDone: MsgBox "Found " + Str$(I - 1) + " Chapters"

' Update the fields
ActiveDocument.Fields.Update

End Sub

Sub CreateChapter(C As String)
    Dim I, L
    Dim NewDoc, NewRange, BaseDirRange
    Dim FileName
   
    I = LBound(AdminTypes)
    L = UBound(AdminTypes)
    ' Loop through all the admin types
    For I = 1 To L
        FileName = AdminTypes(I) + ".docx"

        ' If the document doesn't exist
        If (Dir(FileName) = "") Then
            ' Create the document
            Set NewDoc = Documents.Add
            Set NewRange = NewDoc.Range
         
            ' Add an INCLUDETEXT field to include Common.docm which would be the chapter content common
            ' to all admin types.
            NewRange.Fields.Add NewRange, wdFieldIncludeText, Chr(34) + "BaseDir\\Chapters\\" + Trim(Str$(C)) + "\\Common.docm" + Chr(34)
            Set BaseDirRange = NewRange.Duplicate
            BaseDirRange.Find.MatchCase = True
            BaseDirRange.Find.Text = "BaseDir"
            BaseDirRange.Find.Execute
            BaseDirRange.Fields.Add BaseDirRange, wdFieldEmpty, , False
            NewRange.MoveEnd wdSection, 1
            NewRange.Collapse wdCollapseEnd

            ' Save the new file, named by the role and close the window
            NewDoc.SaveAs FileName
            NewDoc.Close
        End If
    Next
End Sub 

Sincere thanks to the referenced link. If you have questions about the field insertion, you will find the answers there.

For the table of contents, I extended the ChapterBlockStart and used it to indicate the content to include. The footer included the page number with a chapter prefix.

Word VBA Macro to Remove All Bookmarks in a Document

This macro removes all the bookmarks from a Word (2007) Document. I use it to update documents which are imported from Framemaker and had a lot of unused bookmarks.

It may not be the most efficient code, but it does work.

The approach is to create an array of bookmark names or ids, and then loop through and remove them.

Note the ShowHidden, which ensures the entire collection is accessible.

Sub ClearBookmarks()
'
' ClearBookmarks Macro
'
'
Dim I, L, Bees() As String, aBookmark
ActiveDocument.Bookmarks.ShowHidden = True
MsgBox "There are " + Str(ActiveDocument.Bookmarks.Count) + " bookmarks"
I = 1
L = ActiveDocument.Bookmarks.Count
ReDim Bees(L)
For Each aBookmark In ActiveDocument.Bookmarks
    If IsObject(aBookmark) Then
        On Error GoTo Skip
        Bees(I) = aBookmark.Name
        I = I + 1
    End If
Skip:
Next aBookmark

I = LBound(Bees)
L = UBound(Bees)
For I = I To L
    If Bees(I) <> "" Then
        If ActiveDocument.Bookmarks.Exists(Name:=Bees(I)) Then
            ActiveDocument.Bookmarks(Index:=Bees(I)).Delete
        End If
    End If
Next
MsgBox "There are " + Str(ActiveDocument.Bookmarks.Count) + " bookmarks"
End Sub

The Bees array is very busy. :)

Zend Framework Admin Form extension of Zend_Dojo_Form

The objective of this class is to provide a foundation class for admin forms which provides the following:

  • Security - a hash input is used. It must return to the server with the same value sent to the client.
  • Multi-form pages - this class prefixes all inputs so it can be used to generate forms which coexist on the same page. validate, getValues and populate have an additional parameter to support this, indicating whether or not to use the prefix.
  • Greater transaction safety - the version number of the data is sent to the client, and tested later to ensure the data was not changed prior to saving. This test is performed in the Doctrine code, using a Record_Listener which tests the version submitted against the current record version and then increments it prior to save if valid, or throws an exception in the case of a mismatch.
  • ACL support - the form reviews the array of buttons set in the extension class to ensure the administrator has adequate privileges.

<?php
	// application/forms/Account.php
	class Admin_Form extends Zend_Dojo_Form
	{
		protected $_security = null;
		protected $_buttons = null;
		protected $_hash = null;
		protected $_prefix = '';

		public function init()
		{	
			$front = Zend_Controller_Front::getInstance();
			$this->setAction('/'.$front->getRequest()->getParam('module').'/'.$front->getRequest()->getParam('controller'));
			$security = array();
			$security['hash'] = new Zend_Form_Element_Hash('hash');
			$security['hash']->setOptions(array('salt'=>'unique'));
			$security['id'] = new Zend_Dojo_Form_Element_TextBox('id');
			$security['version'] = new Zend_Dojo_Form_Element_TextBox('version');
			$security_names = array();
			foreach ($security as $k => $v)
				$v->setRequired(true)
					->addValidator('Identical')
					->addFilter('StringTrim');
			$this->addElements($security);
			$this->_security = $security;
			$this->addDisplayGroup(array_keys($security),'hsh',array('order'=>0,'style'=>"display:none"));
			if ($this->_buttons != null) 
			{
				$acl_buttons=array();
				$buttons_ok=false;
				if (Zend_Auth::getInstance()->hasIdentity())
        		{
		            $identity = Zend_Auth::getInstance()->getIdentity();
        		    $role = $identity['role'];
				}
				else
					$role = 'none';
				$acl = Zend_Registry::get('Zend_Acl');
				$resource = 'mvc:'.$front->getRequest()->getParam('module').'.'.$front->getRequest()->getParam('controller');
				$buttons = $this->_buttons;
				foreach ($buttons as $k => $v)
					if ($acl->has($resource) && $acl->isAllowed($role,$resource,$k))
					{
						$this->addElement($v);
						$buttons_ok=true;
					}
				if ($buttons_ok) 
					$this->addDisplayGroup(array_keys($buttons),'buttons',array('order'=>100,'class'=>'buttons'));
			}
			parent::init();
		}

		protected function setPrefix($prefix)
		{
			$prefix .= '_';
			$this->_prefix = $prefix;
			$elements = $this->getElements();
			foreach ($elements as $k => $v)
			{
				$v->setName($prefix.$k);
				$this->prefixElement($k);
			}
            $display_groups = $this->getDisplayGroups();
            foreach ($display_groups as $k => $v)
				$v->setName($prefix.$k);		
			$this->_hash = $prefix.'hash';
			foreach ($this->_security as $k => $v)
			{
				$this->_security[$prefix.$k] = $v;
				unset($this->_security[$k]);
			}
			/* Avoids duplicate id in XHTML for hash */
			$fix = $this->getElement($prefix.'hash');
			$tag = $fix->getDecorator('HtmlTag');
			$tag->setOption('id','dd_'.$prefix.'hash');
			foreach ($this->_buttons as $k => $v)
			{
				$this->_buttons[$prefix.$k] = $prefix.$k;
				unset($this->_buttons[$k]);
			}
		}

		public function getElement($name)
		{
			$element = parent::getElement($this->_prefix.$name);
			if ($element != null)
				return $element;
			/* Fallback */
			$element = parent::getElement($name);
			if ($element != null)
				return $element;
			return null;
		}

		public function populate($values,$use_prefix = false)
		{
			self::_prefixProcessor($values,$use_prefix);
			parent::populate($values);
		}

		private function _prefixProcessor(&$data,$use_prefix = false)
		{
            $prefixed = false;
            if (is_array($data) && (count($data)>=1))
            {
                reset($data); $key = key($data);
                $prefixed = (strpos($key,$this->_prefix) === 0);
            	if ($use_prefix)
				{
					if (!$prefixed)
		            {
						$new_data = array();
        	    	    foreach ($data as $k => $v)
            	    	    $new_data[$this->_prefix.$k] = $v;
						$data = array();
						$data = $new_data;
	    	        }
				}
				else
					if ($prefixed)
					{
						$prefixLength = strlen($this->_prefix);
                        $new_data = array();
                        foreach ($data as $k => $v)
                            $new_data[substr($k,$prefixLength)] = $v;
                        $data = array();
                        $data = $new_data;
					}
			}
		}

		public function setTokens()
		{
            if (($this->_hash == null) || ($this->_security == null)) return;

            $session_security=$this->getElement($this->_hash)->getSession();
            $security = $this->_security;
            foreach ($security as $k => $v)
				$session_security->$k = $this->getElement($k)->getValue();
		}

		public function getTokens()
		{
			if (($this->_hash == null) || ($this->_security == null)) return;

            $session_security=$this->getElement($this->_hash)->getSession();
			$security = $this->_security;
			foreach ($security as $k => $v)
			{
				$validator = $this->getElement($k)->getValidator('Identical');
				if (isset($session_security->$k))
					$validator->setToken($session_security->$k);
				else
					$validator->setToken('');
			}
		}

		public function getValues($data = null,$prefixed = false)
		{
			$return = parent::getValues();

			if ($this->_buttons != null)
				$return = array_diff ($return,$this->_buttons);

			self::_prefixProcessor($return,$prefixed);

			return $return;
		}

		private function prefixElement($name)
	    {
    	    $name = (string) $name;
        	if (isset($this->_elements[$name])) {
				$this->_elements[$name]->setName($this->_prefix.$name);
				$this->_elements[$this->_prefix.$name]=$this->_elements[$name];
	            unset($this->_elements[$name]);
    	        if (array_key_exists($name, $this->_order)) {
					$this->_order[$this->_prefix.$name]=$this->_order[$name];
        	        unset($this->_order[$name]);
            	    $this->_orderUpdated = true;
	            } else {
    	            foreach ($this->_displayGroups as $group) {
        	            if (null !== $group->getElement($name)) {
							$group->addElement($this->getElement($this->_prefix.$name));
            	            $group->removeElement($name);
	                    }
    	            }
        	    }
	            return true;
    	    }

        	return false;
	    }

	}

Doctrine code to test and update the version number. This is added in to the model for the data with addListener.

<?php
class Record_Listener extends Doctrine_Record_Listener
{
    public function preUpdate(Doctrine_Event $event) 
	{
		$invoker = $event->getInvoker();
		if (array_key_exists('version',$invoker->getModified())) 
		{
			$invoker->getErrorStack()->add('version','match');
			throw new Doctrine_EventListener_Exception('Version not identical');
		}
		/* Prevents modification of uuid */
		unset($invoker->uuid);
		$invoker->version++;
	}

	public function preDelete(Doctrine_Event $event)
	{
		$invoker = $event->getInvoker();
		if (array_key_exists('version',$invoker->getModified()))
		{
			$invoker->getErrorStack()->add('version','match');
			throw new Doctrine_EventListener_Exception('Version not identical');
		}
	}
}

This an the extension of the Admin_Form. It sets the name, configuratino from the user.ini file, buttons array, and the prefix.

<?php
	// application/forms/Account.php
	class Admin_User_View_Form extends Admin_Form
	{
		public function init()
		{
			$this->setName('frmUserView');
			$user = new Zend_Config_Ini(APPLICATION_PATH."/configs/admin/user.ini",'production',true);
			$this->setConfig($user);
			$this->_buttons = array();
			$this->_buttons['save'] = new Zend_Dojo_Form_Element_Button('save');
			$this->_buttons['save']->setOptions(array(
				'name' => 'save',  
				'label' => 'Save',
				'value' => 'save',
				'onclick' => 'user_save()'));
			parent::init();
			$this->setPrefix('user');
		}
	}

Sample view implementation. $user is a Doctrine object with the data. This is an AJAX form, so the json helper is used. Note the call to setTokens, which sets the hash value and saves the security elements in a session variable for later validation.


			$data = $user->getData();
			$form = new Admin_User_View_Form();
			$form->populate($data);
			$hash = $form->getElement('hash')->getHash();
			$form->setTokens();
			$values = $form->getValues(null,true);
			unset($values['user_password']);
            foreach ($values as $k => $v)
                $values[$k] = $this->view->escape($v);
			$this->_helper->json($values);

Sample save implementation, from the same controller. Note the getTokens call which retrieves the saved session data for the form.


			$form = new Admin_User_View_Form();
			$form->getTokens();
			$data = $this->getRequest()->getPost();
                        if ($form->isValidPartial($data))
                        {
                            $form->populate($data,true);
                            $user->fromArray($form->getValues($data,false));
                        }
			else
				$this->_helper->json(array('form'=>$form->processAjax($data)));

Supporting javascript is omitted, because it’s really application specific. One note is that the hash input isn’t a dojo/dijit input, so it must be handled manually with dojo.byId, instead of auto loaded with the form values.

This has been updated to work with Zend Framework 1.11.

Zend Framework Regex Routing Example

This ini snippet divides the URL into module, controller, and action, including an optional id. If no id is specified, a 0 (zero) is sent.

resources.router.routes.common.type = "Zend_Controller_Router_Route_Regex"
resources.router.routes.common.route = "(\w+)/(\w+)/(\w+)/?(\d+)?"
resources.router.routes.common.defaults.module = "common"
resources.router.routes.common.defaults.controller = "device"
resources.router.routes.common.defaults.action = "index"
resources.router.routes.common.defaults.id = "0"
resources.router.routes.common.map.1 = "module"
resources.router.routes.common.map.2 = "controller"
resources.router.routes.common.map.3 = "action"
resources.router.routes.common.map.4 = "id"
resources.router.routes.common.reverse = "%s/%s/%s/%d"

AES_ENCRYPT with Doctrine ORM

With thanks to the link above, this code snippet delivers the encrypted value of the password from the database.

$manager=Doctrine_Manager::getInstance();
$conn=Doctrine_Manager::connection('mysql://user:password@localhost/database','doctrine');
$aPasswords=array('password','banana','toast');
foreach ($aPasswords as $password)
{
$salt=mt_rand();
$encrypt = new Doctrine_Expression("AES_ENCRYPT('".htmlspecialchars($password,ENT_QUOTES)."','".$salt."')");
$encrypted_password=$conn->fetchOne('SELECT '.$encrypt,array());
echo 'Password: '.$password.' Salt: '.$salt.' Encrypted Password: '.$encrypted_password.PHP_EOL;
}
exit;