英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论