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

[remeng]

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...

public var rev_level = 2

VB Code (Access)...

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

 

[mintjulep]

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.

 

[remeng]

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

 

[mintjulep]

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

 

[Andrzejek]

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.

 

[combo]

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

 

[remeng]

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

 

[Andrzejek]

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.

 

[combo]

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

combo

 

[remeng]

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

 

[combo]

You may try to use document variables instead (code for word, access to document variables from ms access via automation):

https://support.microsoft.com/en-us...tore-and-retrieve-variables-in-word-documents

combo

 

[remeng]

Hi Combo,

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

Mike

 

[combo]

Your initial problem was:

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

 

[remeng]

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

 

[mintjulep]

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

 

[macropod]

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:

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:

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]

 

[combo]

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



combo

 

[remeng]

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

 

[macropod]

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]