Is there an efficient way to store variables as code when inserting data into a Word Document using VBA?

huangapple go评论152阅读模式
英文:

Is there an efficient way to store variables as code when inserting data into a Word Document using VBA?

问题

我已经开发了VBA代码,可以打开Microsoft Word并将数据插入到Word文档中。这个功能运行得非常完美,我可以手动为每个前置条件编写代码来实现需要的功能。我现在尝试的是自动化这个代码,以便用户可以输入需要放入每个书签的字段。在实际操作中,我创建了一个表格,其中存储了书签的名称和每个书签的公式。基本上,我想要简化为每个客户创建前置条件的过程。

问题是我不知道如何将变量存储为代码。例如,如果我想要插入单词“insert words here”,那没有问题,但我无法存储像“insert words said by” & Customer & "Here"这样的内容。或者,我可能希望为书签使用DLookup值。

有没有人有任何想法,我正在尝试的事情是否真的可以实现。

到目前为止,我的代码是:

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

我的代码和问题似乎让每个人都感到困惑,包括我自己。我将尝试进一步解释问题所在。我试图在Word中的书签中插入文本。我希望用户能够决定为每个不同的书签分配哪个变量(或其他代码)。因此,如果书签是“customer”,他们可以调用一个名为customer(已经定义的)的变量。如果书签是“todaysdate”,则用户可以输入Date(),并且应该将当前日期放入书签中,而不是“Date()”。在Excel中,我没有任何问题,因为我在字符串前面使用了“=”。但是Word似乎有所不同。希望这能让我试图做的事情更清楚一些。

英文:

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.

答案1

得分: 1

你可以动态添加项目到vbproject
也许这可以帮助

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"
    ThisDocument.VBProject.VBComponents.Remove vbComp
End Sub

我从这里获取并编辑了这个(将thisworkbook更改为thisdocument):https://stackoverflow.com/questions/43216390/how-to-run-a-string-as-a-command-in-vba

当出现信任错误时,请参考:https://stackoverflow.com/questions/25638344/programmatic-access-to-visual-basic-project-is-not-trusted

英文:

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: https://stackoverflow.com/questions/43216390/how-to-run-a-string-as-a-command-in-vba

when you get trust-error, see: https://stackoverflow.com/questions/25638344/programmatic-access-to-visual-basic-project-is-not-trusted

huangapple
  • 本文由 发表于 2023年5月26日 09:35:51
  • 转载请务必保留本文链接:https://go.coder-hub.com/76337144.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定