我的任务是将 Microsoft Word 表单的内容导入我的 Access 数据库。使用以下从表单触发的 VBA 代码可以正常工作:
Private Sub cmdFileDialog_Click()
On Error GoTo ErrorHandler
Dim objDialog As Object
Dim varFile As Variant
Dim rec, rec2 As Recordset
Dim db As Database
'New Word Document Variables
Dim appWord As Word.Application
Dim doc As Word.Document
Const DEST_TABLE = "ap_behaviour_referrals" 'change to suit
Const PATH_DELIM = "\"
Set objDialog = Application.FileDialog(3)
' Clear listbox contents.
Me.fileList.RowSource = ""
With objDialog
.AllowMultiSelect = False
' Set the title of the dialog box.
.Title = "Please select a behaviour referral to import"
' Clear out the current filters, and add our own.
.Filters.Clear
.Filters.Add "Microsoft Word Forms", "*.docx"
.Filters.Add "All Files", "*.*"
.Show
If .SelectedItems.Count = 0 Then
MsgBox "No file selected."
Else
For Each varFile In .SelectedItems
'New docx Variable Actions
Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(varFile)
Next
Set db = CurrentDb
Set rec = db.OpenRecordset(DEST_TABLE)
With rec
.AddNew
' my data
'preformat the date fields from the form
Dim unformattedpupildob As String
Dim formattedpupildob As Date
unformattedpupildob = doc.FormFields("Text2").Result
unformattedpupildob = Replace(unformattedpupildob, ".", "/")
formattedpupildob = Format(unformattedpupildob, "dd/mm/yy")
'And now insert the record into the table
!pupil_name = doc.FormFields("Text1").Result
!pupil_dob = formattedpupildob
!pupil_yr_grp = doc.FormFields("Text3").Result
!pupil_submitted_eth = doc.FormFields("Text4").Result
!pupil_upn = doc.FormFields("Text5").Result
!pupil_looked_after = doc.FormFields("Text6").Result
!sen_pre_statement = doc.FormFields("Text7").Result
!sen_ehcp = doc.FormFields("Text8").Result
!cat_date_final_ehcp = doc.FormFields("Text9").Result
!num_exclusion = doc.FormFields("Text10").Result
!days_exclusion = doc.FormFields("Text11").Result
!sch_name = doc.FormFields("Text12").Result
!sch_no = doc.FormFields("Text14").Result
!contact_name = doc.FormFields("Text13").Result
!contact_role = doc.FormFields("Text40").Result
!contact_email = doc.FormFields("Text31").Result
.Update
.Close
MsgBox "File Processing Complete"
End With
End If
End With
Set objDialog = Nothing
Me.fileList.RowSource = ""
ExitSub:
Set rec = Nothing
Set db = Nothing
'...and set it to nothing
Exit Sub
ErrorHandler:
If Err.Number <> 0 Then
Msg = "Error # " & Str(Err.Number) & " was generated by " & Err.Source & Chr(13) & "Error Line: " & Erl() & Chr(13) & Err.Description
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
End If
Resume ExitSub
End Sub
除了其中一个字段之外的所有字段都被(严重)添加了书签,因此我可以使用它来获取字段的内容,但是,我遇到了一个未命名的表单字段:
我需要导入,但我不知道如何在没有命名书签的情况下获取其中的内容。
我无法修改表单,因为它由其他人控制并且分布广泛,但是想知道是否有任何方法可以在不命名的情况下提取该字段的内容?
谢谢!
与其他对象集合一样,您可以通过名称(就像您对其他字段所做的那样)或数字索引来寻址它们。
For i = 1 To doc.FormFields.Count
Debug.Print i, doc.FormFields(i).Result
Next i
如果您知道其内容,这应该为您提供该字段的索引。
然后!the_answer = doc.FormFields(42).Result
在您的代码中使用。(42是一个例子!)
编辑:最小工作示例(在 Access 中运行):
Public Sub TestWord()
Dim oWord As Word.Application
Dim oDoc As Word.Document
Dim i As Long
Set oWord = CreateObject("Word.Application")
Set oDoc = oWord.Documents.Open("C:\Users\foobar\Documents\Dok1.docx")
oWord.Visible = True
For i = 1 To oDoc.FormFields.Count
Debug.Print i, oDoc.FormFields(i).Name, oDoc.FormFields(i).Result
Next i
oDoc.Close
oWord.Quit
End Sub
直接窗口 (Ctrl+g) 列出所有表单字段及其索引、名称 = 书签和默认文本。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句