Results 1 to 5 of 5
  1. #1
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211

    Error in Access VBA Automating Word Merge from Excel

    Hello All,



    I'm trying to do a Word MailMerge using a Word document template and Excel as the data source.
    I can successfully create an Excel workbook from my Access VBA, but when I try to do the merge, I get an error saying:

    "The Microsoft Access database engine cannot open or write to the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view and write its data."

    This error appears in the Word application when I switch to it.

    The Excel file is not open, and I already set its permissions and the enclosing folder so "Everyone" has full access. The Word template and the Excel sheet are both on my local drive.

    Here is a snippet of my Access VBA code which is automating word:

    Code:
        If isExcel Then sql = "Select * from " & SheetName & "$"    
        With wordDoc.MailMerge
            .OpenDataSource Name:=m_DataFile, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
                            PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", WritePasswordTemplate:="", _
                            Revert:=False, Format:=0, Connection:="", SQLStatement:=sql, SQLStatement1:="", SubType:=wdMergeSubTypeWord2000
            
            .Destination = wdSendToNewDocument
            .MailAsAttachment = False
            .MailAddressFieldName = ""
            .MailSubject = ""
            .SuppressBlankLines = True
            .DataSource.FirstRecord = 1
            .Execute Pause:=False
            
        End With
    The error occurs when I try to execute the OpenDataSource command.
    If I instead use a CSV file for my data source, it seems to work fine. I use a slightly different SQL statement, and I don't include the SubType argument, but the code is basically the same.

    Incidentally, if I open a new Word document by double-clicking the Word template, then I select data manually by browsing to the Excel workbook, the Word merge seems to work fine, and I can get preview results.

    Any idea why it won't work in code? Maybe the arguments are wrong for Excel data source?

    Thanks...

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Have you tried doing this from Excel out of interest?
    Or importing the Excel data to access then merging directly from Access?

    It just seems a bit convoluted to use Access if the Excel data isn't in Access.

    I suspect that Access has opened the Excel document to read the data via VBA hence making it read only.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Quote Originally Posted by Minty View Post
    Have you tried doing this from Excel out of interest?
    Or importing the Excel data to access then merging directly from Access?

    It just seems a bit convoluted to use Access if the Excel data isn't in Access.

    I suspect that Access has opened the Excel document to read the data via VBA hence making it read only.
    The data is definitely in Access. It turns out to be a lot easier to control the merge from Access vba if the data was first exported to either a CSV file or an Excel file. So I'm exporting it to Excel using VBA from Access. I'm making sure to close and null all of my Excel objects in the code when I'm done exporting. Only then do I automate Word to do the actual mail merge. So I don't think (but not positive) the issue is really permissions. Especially since the error message says '(unknown)' instead of the actual file name.

  4. #4
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You could check if the file is open from access?

    Code:
    Function IsFileOpen(filename As String) As Boolean
        Dim filenum As Integer, errnum As Integer
    
    
        'Firstly check there is a file to check ;)
        If Not FileExists(filename) Then
            IsFileOpen = False        'doesn't exist so therefore it can't be open
            Exit Function
        End If
    
    
        On Error Resume Next        ' Turn error checking off.
        filenum = FreeFile()        ' Get a free file number.
        ' Attempt to open the file and lock it.
        Open filename For Input Lock Read As #filenum
        Close filenum        ' Close the file.
        errnum = Err        ' Save the error number that occurred.
        On Error GoTo 0        ' Turn error checking back on.
    
    
        ' Check to see which error occurred.
        Select Case errnum
    
    
            ' No error occurred.
            ' File is NOT already open by another user.
            Case 0
                IsFileOpen = False
    
    
                ' Error number for "Permission Denied."
                ' File is already opened by another user.
            Case 70
                IsFileOpen = True
    
    
                ' Another error occurred.
            Case Else
                Error errnum
        End Select
    
    
    End Function
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Thanks Minty.

    I did try that code, and the file is NOT open. I even tried creating the Excel a different way, instead of automating Excel I used the following:
    Code:
    DoCmd.TransferSpreadsheet acExport, , dataSource, datFile, True
    where dataSource is the name of the query in Access, and datFile is the full path of the Excel file to be created.

    Therefore, I believe there is no risk of accidentally having an open reference to the Excel workbook. But, I still get the same error, so again I'm thinking it is not really a permissions issue, but something I'm doing wrong in the OpenDataSource command.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-01-2018, 12:16 PM
  2. Replies: 1
    Last Post: 07-18-2015, 08:02 AM
  3. Replies: 1
    Last Post: 12-03-2014, 06:41 PM
  4. Replies: 6
    Last Post: 08-13-2014, 10:32 PM
  5. Error when creating mail merge doc or any word doc
    By maxmaggot in forum Programming
    Replies: 5
    Last Post: 08-25-2013, 01:35 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums