none
Access 2016 form field import from Word 2016 - file dialog issue RRS feed

  • Question

  • 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

    Thursday, December 13, 2018 4:15 PM

Answers

  • I noticed this:

    Dim varFile As Variable

    Does that even compile? You probably meant:

    Dim varFile As Variant


    -Tom. Microsoft Access MVP

    • Marked as answer by JMcG123 Tuesday, December 18, 2018 10:47 AM
    Friday, December 14, 2018 4:30 AM

All replies

  • I noticed this:

    Dim varFile As Variable

    Does that even compile? You probably meant:

    Dim varFile As Variant


    -Tom. Microsoft Access MVP

    • Marked as answer by JMcG123 Tuesday, December 18, 2018 10:47 AM
    Friday, December 14, 2018 4:30 AM
  • Hi Tom

    That's the problem! Thanks very much, must've selected Variable by mistake!

    Cheers

    J

    Tuesday, December 18, 2018 10:47 AM