INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VB for word rev of document check when importing to Access DB

VB for word rev of document check when importing to Access DB

(OP)
Hi All;

I am working on a project that involves a Word form and an Access database. The goal is to take the form entries and transfer them to the access database. Because there are different revisions of the form with different variable names, is there a way to have the VB script in Access check the rev version in the Word document?

Is there a way to put a VB Word variable that would indicate the rev level when the VB Access script runs?

Example:


VB Code (Word) - example...

CODE

public var rev_level = 2 

VB Code (Access)...

CODE

Sub Check_rev ()

if rev_level = 2 then 'from the Word Document

import data from Word document

Else

msgbox = "Cannot import data"

end if

End Sub 

Thanks for the help,

Mike

RE: VB for word rev of document check when importing to Access DB

VBA variables cease to exist when the VBA code is not running.

However Word Document Variables are saved with the document and are accessible with VBA.

If your rev level needs to be visible as text in the document then consider Word Content Controls.

RE: VB for word rev of document check when importing to Access DB

(OP)
Hi Mintjulep,

The rev doesn't need to be visible since it is the code revision level only.

You said that the variable can be saved in Word. Is there a way from Access to read that static value?

Thanks,

Mike

RE: VB for word rev of document check when importing to Access DB

Of course. VBA from Access has full access to the Word object model.

RE: VB for word rev of document check when importing to Access DB

You said you have “a Word form and an Access database” and you want “to take the (Word?) form entries and transfer them to the access database.”

A question: why don’t you do it all in Word VBA?
When you in Word, you do know the rev number, you know which tables / fields in Access to write into (right?). So why don’t you – in Word – connect to your Access and “transfer them to the access database”?

No coding in Access at all.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: VB for word rev of document check when importing to Access DB

Looks like you think about reading VBA code variable. There is a limited access to word document's VBA project, this also requires saving the document in macro enabled format (docm). You could consider adding custom document property instead, it can be added to a file without code and is easily accessible via word object model, as mintjulep wrote.

combo

RE: VB for word rev of document check when importing to Access DB

(OP)
All;

The reason I can't export from word to access is because the form goes out to suppliers. When we receive it completed, we want the data to be uploaded to the database. By going from access to word, we prevent suppliers from having a chance of damaging the file more. Additionally, the data is transferred to multiple tables in access.

The file is already a .docm

Mike

RE: VB for word rev of document check when importing to Access DB

So "the form goes out to suppliers", supplier fills the form out and send it back to you (as Word form, Word doc, whatever)? And that's what you process from Access?
Why not - instead of sending back the form - e-mail back just the XML file with all the data from your Word form (just the button on the Form with some simple VBA - Send, or Done)), and process just the XML file when you get it?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: VB for word rev of document check when importing to Access DB

What is the trigger that sets rev_level = 2 and is it possible to use custom properties to store it?

combo

RE: VB for word rev of document check when importing to Access DB

(OP)
Hi Andrzejek,

It's an existing process that is working well here. The access database is new. Also the word doc get revised but the form rev number doesn't change.

Hi Combo,

It is manually entered in the VB and doesn't change unless the form template is changed by myself (reformatted, sections added or deleted, etc.). It is a static value. Anything is possible at this time since it hasn't been added to the form yet. The access database hasn't been built yet.

Mike

RE: VB for word rev of document check when importing to Access DB

(OP)
Hi Combo,

I read the article, but I don't understand what it is saying. I know only a little VBA.

Mike

RE: VB for word rev of document check when importing to Access DB

Your initial problem was:

Quote:

Is there a way to put a VB Word variable that would indicate the rev level when the VB Access script runs?
The additional question is how do you plan to modify it? Is it only a tag to identify documents to process, that has the same value even if tables are not filled?
Whatever the answer is, it's not a good idea to store the value in document's VBA code: there are both security and accessibility issues. If it's the only reason to have macro enabled workbooks, it's better to find other method to store variable and use ".docx" format.

Mintjulep proposed storing data in document variable, this seems to be the best solution: no user access, available with VBA. Custom document property (that I marked) can be modified or deleted in the user's UI.

Your whole project seems to require some knowledge about automation and working with office applications objects. Maybe you should first test word variables from word VBA, by creating, setting value and reading them. You can decide next if it is what you need.

combo

RE: VB for word rev of document check when importing to Access DB

(OP)
Hi Combo,

I have already built the form with values, variables, formulas, and retaining information. I think my question isn't being understood or we went off track.

In access, is there a way to read a stored value in a word document so if the value is equal to or greater than a value that is in the access code, it will then import the form fields?

Mike

RE: VB for word rev of document check when importing to Access DB

Which of the viable solution that you have been presented have you tried?

RE: VB for word rev of document check when importing to Access DB

Quote:

I think my question isn't being understood or we went off track.
More likely, you haven't understood the advice being given.

In Word, you could set the Revision level using a document variable with a macro like:

CODE

Sub SetRev()
Dim i As Long
With ActiveDocument
  On Error Resume Next
  .Variables.Add Name:="Rev", Value:=0
  On Error GoTo 0
  i = CLng(.Variables("Rev").Value)
  On Error GoTo ErrExit
  i = CLng(InputBox("What Revision # is this?", "Set Revision Level", i + 1))
  .Variables("Rev").Value = i
End With
ErrExit:
End Sub 
Ideally, you'd add this to your form's template and run it with the template opened for editing.

Then, when you get the a document from a supplier, you can test and process it form Access with code like:

CODE

Sub Demo()
'Note: A reference to the Word library must be set, via Tools|References
Dim wdApp As New Word.Application, wdDoc As Word.Document
Dim FmFld As Word.FormField, CCtrl As Word.ContentControl
Dim i As Long, bRev As Boolean: bRev = False
With wdApp.Dialogs(wdDialogFileOpen)
  If .Show = -1 Then
    .Update
    Set wdDoc = wdApp.ActiveDocument
  Else
    Exit Sub
  End If
End With
With wdDoc
  For i = 1 To .Variables.Count
    If .Variables(i).Name = "Rev" Then
      bRev = True
      If .Variables(i).Value = 2 Then
        'Get the 'form' data
        For Each FmFld In .FormFields
          With FmFld
            Select Case .Type
              Case Is = wdFieldFormCheckBox
                'Read .CheckBox.Value
              Case Else
                If IsNumeric(FmFld.Result) Then
                  If Len(FmFld.Result) > 15 Then
                    'Read "'" & FmFld.Result
                  Else
                    'Read FmFld.Result
                  End If
                Else
                  'Read FmFld.Result
                End If
              Case Else
            End Select
          End With
        Next
        For Each CCtrl In .ContentControls
          With CCtrl
            Select Case .Type
              Case Is = wdContentControlCheckBox
                'Read .Checked
              Case wdContentControlDate, wdContentControlDropdownList, wdContentControlRichText, wdContentControlText
                If IsNumeric(.Range.Text) Then
                  If Len(.Range.Text) > 15 Then
                    'Read "'" & .Range.Text
                  Else
                    'Read .Range.Text
                  End If
                Else
                  'Read .Range.Text
                End If
              Case Else
            End Select
          End With
        Next
      Else
        MsgBox "Document revision # is: " & .Variables("Rev") & vbCr _
        & "Unable to process.", vbExclamation
      End If
      Exit For
    End If
  .Close SaveChanges:=False
End With
If bRev = False Then MsgBox "Invalid Document: No Revision #", vbExclamation
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing
End Sub 
The above code simply loops through formfields, then content controls, assuming each is to be written out in Access field order; you'll probably want something more sophisticated.

Unless there is something about the Word document you're distributing that needs macros for the users to complete their data entry, it need only be a docx file. A different version of the above code could be used to import the data from a whole folder of Word forms.

Cheers
Paul Edstein
[MS MVP - Word]

RE: VB for word rev of document check when importing to Access DB

In general, the tip is:
instead of transfer of value using:
[word document VBA project constant] => [MS access database]
use:
[word document object one of properties] => [MS access database]
Advantages: no problems with access with vba, does not require docm format, some properties allow interaction with the user if you need it.

combo

RE: VB for word rev of document check when importing to Access DB

(OP)
Hi Guys,

Paul I am not sure I understand your code since I am a novice at VB. I know enough to be dangerous sort of thing.

The file does need to be .docm since there are macros that control parts of the form based on answers that are provided. Also there are background calculations being performed.

Could you break down the code into sections and explain what it is doing?

Thanks,

Mike

RE: VB for word rev of document check when importing to Access DB

Which lines do you need advice on? I'd have thought some parts (e.g. loops, dialogue boxes), at least, are fairly obvious.

Cheers
Paul Edstein
[MS MVP - Word]

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close