我使用MS Word创建了一个应用程序表格和一系列表单字段,我有一个Access数据库可以从这个Word文档中导入所有需要的数据,感谢这个链接:http://msdn.microsoft.com/en-us/library/aa155434%28office.10%29.aspx。现在一切都很好(我甚至设法将其导入到多个表格中!),但上述问题是我必须手动逐个输入每个文件的名称...如果只是导入应用程序表格,那么这样做就没问题...但我有很多文件坐落在一个需要输入到数据库的文件夹中。然后我找到了这个链接:How to show "Open File" Dialog in Access 2007 VBA? 我试图调整和合并这两个链接以使其工作...但是你可以猜到,没有成功...(当我非常不懂Access时也没有帮助!) 我想要做的是通过使用打开/选择文件对话框将一堆Word文档/表单字段导入到MS Access中...我已经有了可行的方案,但我希望它更容易使用!谢谢大家。Jake
Option Compare Database
Option Explicit
Private Sub cmdFileDialog_Click()
' This requires a reference to the Microsoft Office 11.0 Object Library.
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Dim appWord As Word.Application
Dim doc As Word.Document
' Dim cnn As New ADODB.Connection
' Dim rst As New ADODB.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean
' Clear the list box contents.
' Me.FileList.RowSource = ""
' Set up the File dialog box.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
' Allow the user to make multiple selections in the dialog box.
.AllowMultiSelect = True
' Set the title of the dialog box.
.Title = "Select One or More Files"
' Clear out the current filters, and then add your own.
.Filters.Clear
.Filters.Add "Microsoft Word", "*.DOC"
.Filters.Add "All Files", "*.*"
' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
If .Show = True Then
' Loop through each file that is selected and then add it to the list box.
For Each varFile In .SelectedItems
' Me.FileList.AddItem varFile
Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(varFile)
' cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
' "Data Source=M:\Medical\GPAppraisal\Contacts & Databases\" & _
' "AppForm.mdb;"
' rst.Open "tbl_Applicants", cnn, _
' adOpenKeyset, adLockOptimistic
' With rst
.addnew
!Title = doc.FormFields("wTitle").Result
!FirstName = doc.FormFields("wFirstName").Result
!LastName = doc.FormFields("wLastName").Result
!Address1 = doc.FormFields("wAddress1").Result
!Address2 = doc.FormFields("wAddress2").Result
!Address3 = doc.FormFields("wAddress3").Result
!City = doc.FormFields("wCity").Result
!PostCode = doc.FormFields("wPostCode").Result
!Email = doc.FormFields("wEmail").Result
!Phone1 = doc.FormFields("wPhone1").Result
!Phone2 = doc.FormFields("wPhone2").Result
!LM = doc.FormFields("wLM").Result
!LMAddress1 = doc.FormFields("wLMAddress1").Result
!LMAddress2 = doc.FormFields("wLMAddress2").Result
!LMAddress3 = doc.FormFields("wLMAddress3").Result
!LMCity = doc.FormFields("wLMCity").Result
!LMPostCode = doc.FormFields("wLMPostCode").Result
!LMEmail = doc.FormFields("wLMEmail").Result
!LMPhone = doc.FormFields("wLMPhone").Result
!LMOK = doc.FormFields("wLMOK").Result
!Probity = doc.FormFields("wProbity").Result
!Practising = doc.FormFields("wPractising").Result
!Signature = doc.FormFields("wSignature").Result
!AppDate = doc.FormFields("wAppDate").Result
!e2011012028 = doc.FormFields("w2011012028").Result
!e2011021725 = doc.FormFields("w2011021725").Result
!e2011030311 = doc.FormFields("w2011030311").Result
!e2011031625 = doc.FormFields("w2011031625").Result
!e20110203 = doc.FormFields("w20110203").Result
!e20110211 = doc.FormFields("w20110211").Result
!e20110322 = doc.FormFields("w20110322").Result
!e20110330 = doc.FormFields("w20110330").Result
.Update
.Close
End With
doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Application Imported!"
Cleanup:
' Set rst = Nothing
' Set cnn = Nothing
Set doc = Nothing
Set appWord = Nothing
Next
Else
MsgBox "You clicked Cancel in the file dialog box."
End If
End With
End Sub
我曾试着使用me.tables、me!forms和.add等等,显然我在这里是一个完全的新手!!!
我的目标是从Word文档中的表单字段导入数据到MS Access表中(我已经成功实现了上面原帖中的第一个URL);通过从打开/选择对话框中选择Word文档的方式,而不是手动输入每个Word文档的名称。
如果听起来很明显或简单,请原谅 - Access并不是我的强项!