Getting the Template Path
When automating Word from Access, we need to know the name and location of the file we will be opening. This information can be hard-coded in VBA such as:
... 'Ensure Word is visible WordObj.Visible = True 'gets word path from tblSettings strWordPath = "C:\Users\Robert Austin\SkyDrive\AXL\Videos\WordAutomation\Files\HotelWelcomeLetter.docx" WordObj.Documents.Add _ Template:=strWordPath, _ newtemplate:=False ...
In the above code the Word path and filename is “C:\Users\Robert Austin\SkyDrive\AXL\Videos\WordAutomation\Files\HotelWelcomeLetter.docx”. As stated, this path and filename have been hard-coded which may present some problems going forward. What happens if the Word document gets deleted? Or what would happen if the folder structure changed? In both these cases the code would not work and render the module useless.
A better option would be to store the code in a table. It is very common to have a settings table which can contain one record and several fields that contain information such as the name and address of a company, the names of the most prominent senior members of staff and things like the path and filename of certain files that need to be regularly used (such as this one). If we had a table called “tblSettings” and a field called “WelcomeLetterPath”, then rather than hard-code the value, we could simply use a DLookup function to retrieve it from the database. As there is only one record, we would not even need a WHERE condition.
The code would look like this:
...
'Ensure Word is visible
WordObj.Visible = True
'gets word path from tblSettings
strWordPath = DLookup("[WelcomeLetterPath]", "tblSettings")
WordObj.Documents.Add _
Template:=strWordPath, _
newtemplate:=False
..
strWordPath = DLookup(“[WelcomeLetterPath]”, “tblSettings”) is a much better way of handling this.
In the HotelBookings database, the settings table looks like this (design view):
These are some of the fields that we felt was necessary for this particular database. Do all databases need to have these fields? No! Put the fields in the settings table that you think are appropriate for your database.
In datasheet view the table looks like this:
Updating the Template Path
If you are going to store the template path in a table you will need a way of updating what path is stored in the table. Just like above, the file may have been renamed or the folder structure changed so we will need update the table as necessary and as always, direct access to a table is seldom a good idea.
In the Hotel Bookings database we have a form Settings that is bound to tblSettings. We could, if we wanted, write the path to the Word document in the above field Welcome Letter Path but this is not the best way of doing this as if get one character wrong, the database won’t be able to open the file.
Instead of this, we have a button Update located to the right of the Welcome Letter Path field. There is a macro behind the button that looks like this.
This SetProperty macro action will put the value of the function getFilePath() into the Welcome Letter Path field.
Public Function getFilePath() As String
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Show
getFilePath = .SelectedItems(1)
End With
End Function
The getFilePath function uses the native File Picker to obtain a path from the database. Don’t worry too much about every line of code, just understand that in order to get the path of a file, this is the function you need!
It is also worth noting that in order to use the msoFileDialogFilePicker, you will need to set a reference to the Microsoft Office 15.0 Object Library (12.0 2007, 14.0 2010). But you already did this in the previous video.
