Fields updating via VBA

[DeenaH]

Can anyone point me to a book/tutorial that explains how to implement field updating through vba. Hopefully, the following explanation of what I am trying to achieve is clear. If it is not I will try to articulate it better.

I want to have fields in word. These fields should show a name (i.e., First Name) and linked by code to a named cell in Excel (i.e., FirstName). I cannot use the built-in linked fields, because rows and columns in the Excel sheet are constantly being added and linked fields as you know are referenced by row and column numbers.

I am thinking that I will need to use DOCVARIABLEs. However, I am stuck. When I manually insert a DOCVARIABLE (Ctrl-F9), I can name it {DOCVARIABLE "VariableName"}, but when I click ALT-F9, I cannot see the field, because there is no name assigned to it.

Searched google but cannot find an answer.

[macropod]

I cannot use the built-in linked fields, because rows and columns in the Excel sheet are constantly being added and linked fields as you know are referenced by row and column numbers.

That not really so, as you can use range names in the link field. If you move, expand or contract the named range, the LINK field will pick up those changes.


I am thinking that I will need to use DOCVARIABLEs. However, I am stuck. When I manually insert a DOCVARIABLE (Ctrl-F9), I can name it {DOCVARIABLE "VariableName"}, but when I click ALT-F9, I cannot see the field, because there is no name assigned to it.

A DOCVARIABLE field won't do any better, as it still needs a DOCVARIABLE that contains the actual content - not an external workbook reference.

[DeenaH]

Great, as long as I define a name for a cell or a field, it links it with the name.

[DeenaH]

Macropod, you were correct. However, the word document I am trying to replicate has the option of double clicking on the linked field (DOCVARIABLE) and a window pops up and changes are communicated back to the Excel file. That is what I am trying to learn how to do.

[macropod]

Anything that's going to transfer content from the document back to an Excel workbook will require VBA programming, not just field coding. Since the document you're working from is already doing that, that suggests the VBA code is already in that document (e.g. if it's a docm file) or its template.

[DeenaH]

Exactly my point, that's why I believe that I need to use DOCVARIABLEs, which will be populated by code. But I am trying to figure out how the DOCVARIABLE is showing a name before being updated. I looked for a tutorial or book but cannot find any. Any suggestions where I can find one that addresses the above, would be greatly appreciated.

I am not working from such a document, but I saw this features in a software package and would like to implement it into my word/excel files.

[macropod]

Exactly my point, that's why I believe that I need to use DOCVARIABLEs, which will be populated by code. But I am trying to figure out how the DOCVARIABLE is showing a name before being updated. I looked for a tutorial or book but cannot find any. Any suggestions where I can find one that addresses the above, would be greatly appreciated.
Because that's now how DOCVARIABLE fields either work or can be made to work.

I am not working from such a document, but I saw this features in a software package and would like to implement it into my word/excel files.
That's not what you said before!

the word document I am trying to replicate has the option of double clicking on the linked field (DOCVARIABLE) and a window pops up and changes are communicated back to the Excel file.
A "word document I am trying to replicate" is not the same as "I saw this features in a software package"...

[DeenaH]

Now that you understand what I am looking for, can you suggest further reading/tutorial/video.

Thanks.

[macropod]

Since I haven't seen this 'software package' you're referring to, I don't really know what it's doing or what you're looking for. I suggest you look into the use of userforms and Excel automation. As an alternative to a userform, formfields, contentcontrols or ActiveX controls might be used.

[DeenaH]

It's not being done with a userform. See screenshot.

In word there are DOCVARIABLEs with a placeholder name then a button is clicked and all the fields are updated. In addition, you can double click on any field and a window opens where you can edit the field, which reflects the change in Excel.


22721

[macropod]

Most likely, what you're referring to as "DOCVARIABLEs with a placeholder name" are actually named building blocks, which employ a form of content control (see my previous post). And, if there is a button, then that button (most likely an ActiveX command button, but possibly a picture content control) calls some VBA code to do the updating. As I said before, that code will be in that document (e.g. if it's a docm file) or its template.

[DeenaH]

When you click alt-f9 it shows DOCVARIABLE.

See both images (before and after alt-f9). There is no code attached or Macros, but I believe this is because this is just the doc file and the vba is embedded in a dll. I also do not have access to the software only to the word file. I'm just trying to figure out how it was done so I can add it to my documents. When I click F9 to update the fields, some fields update, while others don't. So I would also like to know how to find out where the references are to the fields.

22722

22723

[macropod]

So how about attaching the actual Word file to a post...

[DeenaH]

The document was way too big, so I attached the first page, which should show how things are done. If you click F9 on file name (13-03201) it updates to 13-03200.

[macropod]

If you click F9 on file name (13-03201) it updates to 13-03200.
That's only because the stored value for the "AppraisalFile" variable is already 13-03200. There is nothing you can do by editing the field to change the variable's value to anything else - any time you press F9 again it will revert to 13-03200.
You can confirm that the "AppraisalFile" variable is already 13-03200 by opening the document and running the following macro before updating the field:

Sub Test()
MsgBox ActiveDocument.Variables("AppraisalFile").Value
End Sub
Conversely, if you run the following macro, then update the field, you'll get 13-03999:

Sub Demo()
ActiveDocument.Variables("AppraisalFile").Value = "13-03999"
End Sub

[DeenaH]

Ok, great, but is there any way to see all the values of the docvariables? Is there a repository for all of the variables?

Is it correct to assume that the placeholder names for the docvariables had to be set in code and then the updating of them causes them to get the value from the Excel file?

[macropod]

DOCVARIABLE fields only reflect what the document variables in the document's metadata contain; they cannot update that data or transfer anything between there and another application (or even another document). The placeholder names would have been the initial values stored in the corresponding document variables. Updating what is displayed has no effect on what is stored; only on what is displayed. The only way a document variable can be updated is through code, whether VBA, XML coding or in some other application. Your previous reference to a button that is clicked on suggests the use of VBA code. Your attachment didn't include that button or any code, however.

Since document variables are user-defined, there is no standard set. You can find out all of their names & current values for a given document with a macro like:

Sub Demo()
Dim i As Long, StrVar As String
With ActiveDocument
For i = 1 To .Variables.Count
With .Variables(i)
StrVar = StrVar & vbCr & "Name: " & .Name & ", Value: " & .Value
End With
Next
End With
MsgBox "Variables in this document:" & StrVar
End Sub

[DeenaH]

Okay. I already saw the variables, but I had to use selection.typetext because a msgbox was not big enough.

So, how can I have docvariables point their respective named cells in excel. Basically the excel and the word documents will reside in the the same folder and have the same name.

Thanks.

[macropod]

So, how can I have docvariables point their respective named cells in excel.
You can't. I've already told you that. Repeatedly.

The only way of updating the variables from Excel or vice-versa (after you've updated them by code in the document via VBA, etc.) is by code and, to do that, one would need to know what the workbook name is and which particular Excel cell relates to a given document variable.

[gmaxey]

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey, http://gregmaxey.com/word_tips.html, 8/18/2018
Dim oXL As Object
Dim oWB As Object
Dim oWS As Object

Set oXL = CreateObject("Excel.Application")
Set oWB = oXL.WorkBooks.Open("D:\Book1.xlsx") 'Or whatever you file name is.
Set oWS = oWB.Sheets("Sheet1")
ActiveDocument.Variables("FirstName").Value = oWS.Range("FirstName").Value 'or whatever you document variable and excel named range are.
ActiveDocument.Variables("LastName").Value = oWS.Range("LastName").Value 'or whatever you document variable and excel named range are.
ActiveDocument.Fields.Update
oWB.Close SaveChanges:=False
oXL.Quit
lbl_Exit:
Exit Sub
End Sub

[DeenaH]

Thanks Greg!