Save versions

[Jed Harrison]

I am trying to create documents that populate bookmarks based on an ADO connection to an Access database. What I want to do is have the original document remain open and unchanged, while each record in the table results in a new document being saved without ever being opened. I think I have the idea of how to do this, what I am missing is the understanding of the properties and methods involved. I have so far tried using activedocument.SaveAs, but what this does is change the original, and leaves the final document open upon finishing.

Bonus points if someone can tell me how to remove the VBA code from the documents that are produced.

This is the code I have so far,

Option Explicit

Sub testBookMarks()


'declarations
Dim theCon As ADODB.Connection
Dim theRecSet As ADODB.Recordset
Dim strDataLocation As String
Dim theSQL As String

'create an ADO connection to the access database
Set theCon = New ADODB.Connection
strDataLocation = "C:\Jed\Parks map reports\testparks.mdb"
theCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDataLocation

'Open the connection
theCon.Open

theSQL = "SELECT * FROM ParkDesc"

'Create a ADO recordset that will contain data from access table
Set theRecSet = theCon.Execute(theSQL)

Call getData(theRecSet)

'do clean up
'close the ADO connection
theCon.Close

'set objects to nothing
Set theCon = Nothing
Set theRecSet = Nothing



End Sub

Sub getData(theRecSet As ADODB.Recordset)

Dim intFieldCount As Integer
intFieldCount = theRecSet.Fields.Count
Dim strFieldName As String
Dim strUpdateText As String
Dim i As Integer
Dim intTest As Integer 'Delete after testing

Do While Not theRecSet.EOF

For i = 0 To intFieldCount - 1

strFieldName = theRecSet.Fields(i).Name

If Not IsNull(theRecSet.Fields(i).Value) Then

strUpdateText = theRecSet.Fields(i).Value

Else
End If

Call UpdateBookmark(strFieldName, strUpdateText)

Next

intTest = intTest + 1

ActiveDocument.SaveAs "C:\Jed\Parks map reports\Park Report Test\test " & CStr(intTest)
'Here is where I want to save a new version without changing the origianl document

theRecSet.MoveNext

Loop


End Sub


Sub UpdateBookmark(BookmarkToUpdate As String, TextToUse As String)
Dim BMRange As Range
Set BMRange = ActiveDocument.Bookmarks(BookmarkToUpdate).Range
BMRange.Text = TextToUse
ActiveDocument.Bookmarks.Add BookmarkToUpdate, BMRange
End Sub

 

[Jezebel]

Doing a SaveAs always renames the original. There's no getting around this.
Do your SaveAs, close the document, re-open the original. Nuisance, but
relatively minor.

Your code should be in a template, not in the document; then it won't be
saved with the documents anyway. It is possible to manipulate code directly
(delete modules, etc); but it's tricky to say the least, and in this case
quite unnecessary. Don't go there.

I am trying to create documents that populate bookmarks based on an ADO

connection to an Access database. What I want to do is have the original
document remain open and unchanged, while each record in the table results
in a new document being saved without ever being opened. I think I have the
idea of how to do this, what I am missing is the understanding of the
properties and methods involved. I have so far tried using
activedocument.SaveAs, but what this does is change the original, and leaves
the final document open upon finishing.

Bonus points if someone can tell me how to remove the VBA code from the documents that are produced.

This is the code I have so far,

Option Explicit

Sub testBookMarks()


'declarations
Dim theCon As ADODB.Connection
Dim theRecSet As ADODB.Recordset
Dim strDataLocation As String
Dim theSQL As String

'create an ADO connection to the access database
Set theCon = New ADODB.Connection
strDataLocation = "C:\Jed\Parks map reports\testparks.mdb"
theCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDataLocation

'Open the connection
theCon.Open

theSQL = "SELECT * FROM ParkDesc"

'Create a ADO recordset that will contain data from access table
Set theRecSet = theCon.Execute(theSQL)

Call getData(theRecSet)

'do clean up
'close the ADO connection
theCon.Close

'set objects to nothing
Set theCon = Nothing
Set theRecSet = Nothing



End Sub

Sub getData(theRecSet As ADODB.Recordset)

Dim intFieldCount As Integer
intFieldCount = theRecSet.Fields.Count
Dim strFieldName As String
Dim strUpdateText As String
Dim i As Integer
Dim intTest As Integer 'Delete after testing

Do While Not theRecSet.EOF

For i = 0 To intFieldCount - 1

strFieldName = theRecSet.Fields(i).Name

If Not IsNull(theRecSet.Fields(i).Value) Then

strUpdateText = theRecSet.Fields(i).Value

Else
End If

Call UpdateBookmark(strFieldName, strUpdateText)

Next

intTest = intTest + 1

ActiveDocument.SaveAs "C:\Jed\Parks map reports\Park Report Test\test " & CStr(intTest)
'Here is where I want to save a new version without changing the origianl document

theRecSet.MoveNext

Loop


End Sub


Sub UpdateBookmark(BookmarkToUpdate As String, TextToUse As String)
Dim BMRange As Range
Set BMRange = ActiveDocument.Bookmarks(BookmarkToUpdate).Range
BMRange.Text = TextToUse
ActiveDocument.Bookmarks.Add BookmarkToUpdate, BMRange
End Sub

if what I ask seems obvious.

 

[Jed Harrison]

I tried putting the code into a template and running it, but it still gets replicated with each version of the document. Perhaps I misunderstood your suggestion?

 

[Jezebel]

There is a misunderstanding, yes. Is your template a DOT file, or simply a
document that you are using as a template? If the latter, read help on how
templates work. Code in a dot template is not duplicated into the documents
created from that template. It does however remain available if you have
open a document based on the template, if the template is installed on the
computer.

I tried putting the code into a template and running it, but it still gets

replicated with each version of the document. Perhaps I misunderstood your
suggestion?

 

[Jed Harrison]

The template is a .dot , but it does replicate the code into the .doc's

Does is have to be located in the templates directory for this property to work?

 

[Jezebel]

Are you creating your documents by opening the dot file then saving as a new
document? In which case that's your problem; as suggested before, read help
on how templates work. To create a new document based on the template:
manually, use File > New and select your template. (The template should be
located in one of the templates folders.) From VBA use Set NewDoc =
Documents.Add (Template:= ....) .

With either of those methods your code will not be replicated into the
created document.

 

[Jezebel]

Put the code in a new module located either in normal.dot or in a template
installed as an add-in. Normal.dot is convenient if it's just you who will
be using the code. An add-in is a more professional approach if others will
be running the code also. (An add-in is any template saved in the
Office\Start up folder -- you'll see it listed on the Tools > Templates and
Add-ins dialog.) Using an add-in is also better from a code management point
of view -- normal.dot can get large and unstable if you use it
indiscriminately for code.

An add-in also has the advantage that you can add your own toolbar buttons,
used for calling your macro.

A couple of points with your code:

1. You set strUpdateText to the field value if the field is not null: as it
currently stands, if the field IS null you will re-insert the value of the
previous field.

2. Instead of using bookmarks, have a look at CustomDocumentProperties --
they are easier to work with from VBA, and you can insert the same value in
more than one place in the document if needed.

3. Rather than creating your document once and doing a SaveAs for each
record, it would be more reliable and cleaner to create it afresh for each
record. The performance difference would be minimal.

4. If you don't explicitly Save the document before, or when, you close it
you'll be prompted each time to save changes.

Thanks again, I'm getting closer now. Up to now, all of my limited VBA

has either been for Access or ArcMap, so this is new stuff for me.

I modified the code to use the documents.add method. Now I have to figure

out how to trigger the procedure. I initially put a call into the
document_new event, but discovered (doh) that this creates an infinite loop,
as I am creating many documents from the same procedure.

Here is the updated code. Any suggestions on how to run the code would be

welcome. I don't necessarily need to have it run whenever the template is
opened. Putting it in a ui button click would be fine, I'm just not sure
how that could work either.

Thanks for all the help so far


Option Explicit



Sub testBookMarks()

'declarations
Dim theCon As ADODB.Connection
Dim theRecSet As ADODB.Recordset
Dim strDataLocation As String
Dim theSQL As String

'create an ADO connection to the access database
Set theCon = New ADODB.Connection
strDataLocation = "C:\Jed\Parks map reports\testparks.mdb"
theCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDataLocation

'Open the connection
theCon.Open

theSQL = "SELECT * FROM Parks"

'Create a ADO recordset that will contain data from access table
Set theRecSet = theCon.Execute(theSQL)

Call getData(theRecSet)

'do clean up
'close the ADO connection
theCon.Close

'set objects to nothing
Set theCon = Nothing
Set theRecSet = Nothing

End Sub

Sub getData(theRecSet As ADODB.Recordset)

Dim intFieldCount As Integer
intFieldCount = theRecSet.Fields.Count
Dim newDoc As Word.Document
Dim strFieldName As String
Dim strUpdateText As String
Dim i As Integer
Dim intTest As Integer 'Delete after testing

Documents.Add Template:="C:\Jed\Parks map reports\Park Report with Code version 2.dot"

Do While Not theRecSet.EOF

ActiveDocument.SaveAs "C:\Jed\Parks map reports\Park Report

Test2\test " & CStr(intTest) & ".doc"

 

[Jed Harrison]

I'll look up customDocumentProperties. Bookmarks seemed a bit clunky, but I didn't find any other way, it seemed easy from a design perspective. One of my goals here is to make it easy to modify after I am gone (summer student position).

I'll defnitely be creating and closing a new document each time. In fact, I think I've decided it would all work best if I just put the code into the database and run it from a user form there. That will be the primary interface for the users anyhow.

Thanks so much for all the advice. It's so easy to get lost in all the options without an occasional push in the right direction.