1

I have developed vba code which opens Microsoft Word and inserts data into a Word Document. This works perfectly and I can do whatever I need to do with this if I manually write code for each precedent. What I am trying to do is to automate this code, so that a user can input the fields that need to go into each bookmark in practice, I have created a table which stores the name of the bookmark and the formula for each bookmark. Basically, I am trying to make it easy to create the precedents for each of the clients.

The problem is I don't know how to store the variables as code. For example, if I want to insert the words "insert words here", that is no problem but I cannot store something like "insert words said by " & Customer & "Here". Or alternatively, I might want to use a DLookup value for the bookmark. Does anyone have any idea if what i am trying to do is actually achievable.

So far the code i have is:

BMCount = Oapp.ActiveDocument.Bookmarks.Count
Debug.Print BMCount

    x = 0
    Set dbase = CurrentDb
    Set recset1 = dbase.OpenRecordset("SELECT * FROM [Precedents]")
    Set recset2 = dbase.OpenRecordset("Select * from [Precedent Variables]")

 With recset1
    .AddNew
    recset1![PrecedentName] = Precedent
    recset1![doctype] = "Court Documents"
    recset1![Extension] = Extension
    update
    Debug.Print recset1![PrecedentName]
    Debug.Print recset1![doctype] = Extension
    Debug.Print recset1![Extension]
PrecedentID = recset1![ID]

End With

Do Until x = BMCount
    x = x + 1
    BMark = Oapp.ActiveDocument.Range.Bookmarks(x)
    Debug.Print BMark

    With recset2
        .AddNew
        recset2![Bookmark] = BMark
        recset2![PrecedentID] = PrecedentID
        strvar = InputBox("Please input the formula or data for the bookmark - " & BMark)
        recset2![VariableFormula] = strvar
        Debug.Print strvar
        update
    End With

    Debug.Print BMark
    Debug.Print strvar

    With Oapp.ActiveDocument.Bookmarks
        .Item(BMark).Range.Text = strvar
    End With
Loop

My code and question seems to be confusing everyone including me. I will try to explain further what the problem is. I am trying to insert text into a bookmark in Word. I want the user to be able to decide what variable (or other code) to assign to each different bookmark. So if the bookmark is "customer", they could call a variable called customer (already defined) if the bookmark says "todaysdate" then the user can input Date() and the current date should be put in the bookmark instead of "Date()" In Excel I have no issues because I use the string with an "=" at the front. But Word seems to be different. Hopefully this makes what I am trying to do a little clearer.

June7
  • 19,874
  • 8
  • 24
  • 34
Bender
  • 11
  • 4
  • too confusing to answer. at least pair each Addnew with its update and declare your variables. Then move through the recordset if the recordset returns more than one record. you will probably need to use both addnew and edit. see here for example: https://learn.microsoft.com/en-us/office/vba/access/concepts/data-access-objects/move-through-a-dao-recordset – mazoula May 26 '23 at 04:37
  • The difficulty i am having is at a more basic level. I am struggling to get it to work once. There is quite a bit more code above, where the variables are declared and the code actually opens word. I can post this, but i thought it would confuse more. The main issue (i think) is that whatever i put in strvar is not able to be turned into a code (ie use vba functions or variables). BAsically i want Strvar to be able to refer to a line of code. I could do this manually by deleting it and putting the code after the =. But i am trying to find a way to let user or an access field do he code. – Bender May 26 '23 at 05:28
  • I really don't know what you want, in your description and the code given, I can't see where **Customer** will be used and where you wants it to appear – Oscar Sun May 26 '23 at 06:44
  • i will try to explain a different way. The main problem is with strvar. i am trying to get the user to input a vba formula or code or variable rather than just a straight string. with the customer example that will already be assigned a variable (Say Bob Jones), so i would likd the bookmark to insert the text "Insert Words said by Bob Jones". The Variable Customer will have already been allocated earlier in the code. Maybe for example, the user might want to input data from two different fields such as Customer and CustomerAddress. Or to use a Dlookup function. Did that make better sense – Bender May 26 '23 at 07:06
  • Can you give us an example of what an input might, or what would you like them to input? Like this line `strvar = InputBox("Please input the formula or data for the bookmark - " & BMark)` What value do you expect **strvar ** to have? As you know, it must be a string-type value, right? Do you want to be able to parse the string with or without the name of the named variable in your code? If it does, then do the code-related operation, if not, just pass the value directly in the code. Is that right? – Oscar Sun May 26 '23 at 08:14
  • 2
    Let's take your Customer bookmark example, and assume that the str ar for that bookmark is in fact "customer", and that that is supposed to result in the customer's name being inserted in that bookmark. What will your VBA code actually need to do to retrieve the name? Is it always going to do something like SELECT customer FROM customer WHERE or what? – jonsson May 26 '23 at 09:24
  • 1
    Answer given seems to do what i want, but it is complicated code but i will need to take a while to learn what the different components do and mean i have some basic errors but that is probably just because i dont know what to call the differrent components. WRT retrieving the components, that is what i was trying to automate. Eg CustomerName is already stored in variables as customername. the code i had brings up bookmark1. I would like the inputbox to then ask what variable needs to be assigned to Bookmark1. User would type CustomerName and the program would insert val of CustomerName. – Bender May 27 '23 at 03:14
  • So what you want to pass in string by the input is the name of the variable, not a value of type string of some variable, and you want the program to call that variable with the given variable name and do what it is supposed to do. Is that so? – Oscar Sun May 27 '23 at 04:40
  • 1
    Yes. That is exactly what i was trying to do. Ultimately i might try to complicate it a bit by say putting the name of two variables or the name of some other type of function such as date or dlookup. but if i could just work out how to do one variable i think that would work. – Bender May 27 '23 at 21:59
  • Have you thought about how to correctly transfer these two variable names? So that they are not mistyped and not recognized. Why don't you use something like content control or ActiveX controls, a list box, or a combo box for a user form (or a text box if you have additional information to input) to minimize input errors? Because the variables are fixed, and the variables do not change their names from time to time. Right. – Oscar Sun May 28 '23 at 04:54
  • What I can think of, other than the way @sotto answered, is to add judgmental statements to the code to triage the input according to the name. Parse or judge the input value to see if the user has specified the variable name and wants to perform the corresponding operation. Have you ever thought about it? If not, you may want to try this direction first. – Oscar Sun May 28 '23 at 05:04
  • Yes oscar, i agree with doing those things but i need to get the basic to work first. I am strugling with understanding how sottos answer works. I think it (internally perhaps) opens a module and calls it foo, writes it, executes it,then deletes it. (ok i understand perfect now i worte that). My problem is that i cant set vbcomp. I get the error no 438 object doesnt support this property or method. I am thinking i am missing a library or something simple. – Bender May 29 '23 at 21:43

1 Answers1

1

you can dynamically add items to the vbproject maybe this can help

Private Sub Document_Open()

    StringExecute ("MsgBox ""hello""")
        
End Sub
Sub StringExecute(s As String)
    Dim vbComp As Object
    Set vbComp = ThisDocument.VBProject.VBComponents.Add(1)
    vbComp.CodeModule.AddFromString "Sub foo()" & vbCrLf & s & vbCrLf & "End Sub"
    Application.Run vbComp.Name & ".foo"
    ThisWorkbook.VBProject.VBComponents.Remove vbComp
End Sub

i got this (& edited thisworkbook to thisdocument) from: How to run a string as a command in VBA

when you get trust-error, see: Programmatic Access To Visual Basic Project Is Not Trusted

sotto
  • 2,044
  • 4
  • 18
  • 22