Hi guys
I've re-purposed and written some code to import a load of fields from a Word 2016 document into a table in an Access 2016 database. The import itself works perfectly (thanks to some help from Daniel) when I point Access to a file location but I'm encountering
issues when trying to use file dialog in order to select the same file from the same location.
The file dialog section works perfectly when importing an Excel document using .TransferSpreadsheet. I've tried multiple different combinations to try to get this to work, I think I'm going wrong with setting strDocName as varFile but I'm not too experienced
and haven't attempted anything like this before so any help would be greatly appreciated!!
When running the code below I encounter Run-time error '424': Object required, the debugger points to [For Each varFile In .SelectedItems]
I've tried setting the strDocName as varFile to no avail and have also tried amending the doc name [Set doc = appWord.Documents.Open(strDocName)] to varFile and that doesn't work either. Is it possible this is because varFile is a variable?
The code I'm using is as follows:
Private Sub Btn_Frm_ImportVRForm_Click()
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
Dim fDialog As FileDialog
Dim varFile As Variable
'On Error GoTo ErrorHandling
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.AllowMultiSelect = False
.Filters.Add "Word Document", "*.docx"
.Filters.Add "Macro Enabled Word Document", "*.docm"
If .Show = True Then
For Each varFile In .SelectedItems
strDocName = varFile
'strDocName = "C:\Users\User_Name\Desktop\VR Final June 2018v1.docm"
Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(strDocName)
'This will need to be amended when the database is centralised
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Users\User_Name\Desktop\" & _
"AccessibleRecruitment.accdb;"
rst.Open "Tbl_ImportVRForm", cnn, _
adOpenKeyset, adLockOptimistic
With rst
.AddNew
!DateRaised = doc.FormFields("Dt_DateRaised").Result
Obviously with a load of extra code to import the rest of my fields and close everything up.
Many thanks in advance!
Cheers
J
