Consulting

Page 1 of 5 1 2 3 ... LastLast
Results 1 to 20 of 82

Thread: Run-time error '5922' - Mail Merge

  1. #1
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location

    Run-time error '5922' - Mail Merge

    Afternoon all,

    Sorry for the mislead title - I had that error previously and will probably have it again, but another hump got in the way, so I amended my post.

    I'm trying to set up a mail merge from Excel to Word. I've got it working manually, but need to automate the process for our users.

    I have the macro below run in an AutoNew sub, when the document opens. However, every time I try and use it, after one clicks Yes to continue with the mail merge, it comes up with the error "Object doesn't support this property or method."

    Sub MailMerge()
        Dim SourceDoc As String
        Dim SelectedItems(1) As String
    
    
        With Application.FileDialog(msoFileDialogFilePicker)
            .AllowMultiSelect = False
            If .Show <> 0 Then
                SelectedItems(1) = SourceDoc
            Else
                Exit Sub
            End If
        End With
        
        ActiveDocument.MailMerge.OpenDataSource Name:=SourceDoc, _
            ConfirmConversions:=True, ReadOnly:=False, LinkToSource:=True, _
            AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
            WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
            Format:=wdOpenFormatAuto, Connection:= _
            "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=H:\Jonathon Hunter Hill\Mail Merge\AirMaster V 0.9.xlsm;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Jet OLEDB:" _
            , SQLStatement:="SELECT * FROM `MailMerge$`", SQLStatement1:="", SubType _
            :=wdMergeSubTypeAccess
            
    End Sub
    If I click No to the question of whether I want to carry out the mail merge or not, I can then run this macro again and it will get past that initial Object error stage. However, it then allows me to select a document (which I am able to mail merge manually), and then I can select the Provider (OLED), despite that choice already being filled (I think) at which point the 5922 error occurs and the sub terminates.

    Any help would be very much appreciated,

    dirtychinchilla
    Last edited by dirtychinch; 02-20-2014 at 07:50 AM.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,908
    Location
    Shouldn't your source be in quotes, especially as it has spaces in it.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,475
    Location
    I think the connection string (which is the actual argument being passed), has larger problems, as that entire argument is a string, but you're also trying to pass a whole bunch of different double-quotes, as well as what looks to me a null argument. Combine that with line breaks created by a recorded macro, it's pretty tough to see what's going on... is it a syntax error in VBA, or is it a SQL syntax error in your connection string?

    Here's your code re-written simply for legibility...

    Take it out of the AutoNew routine, and try stepping through the code... and seeing if your connection string is wrong (almost guaranteed) or some other parameter you're passing.

    [VBA]
    Sub MailMerge()
    Dim SourceDoc As String
    Dim SelectedItems(1) As String
    Dim sConnection As String


    With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    If .Show <> 0 Then
    SelectedItems(1) = SourceDoc
    Else
    Exit Sub
    End If
    End With

    'your connection string, also more easily separated with line breaks and arguments
    sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "User ID=Admin;" & _
    "Data Source=H:\Jonathon Hunter Hill\Mail Merge\AirMaster V 0.9.xlsm;" & _
    "Mode=Read;" & _
    "Extended Properties=""HDR=YES;IMEX=1;"";" & _
    "Jet OLEDB:System database="""";" & _
    "Jet OLEDB:Registry Path="""";" & _
    "Jet OLEDB:Engine Type=35;" & _
    "Jet OLEDB:"

    'using your conection string... with the parameter names and the passed values separated nicely
    ActiveDocument.MailMerge.OpenDataSource _
    Name:=SourceDoc, _
    ConfirmConversions:=True, _
    ReadOnly:=False, LinkToSource:=True, _
    AddToRecentFiles:=False, _
    PasswordDocument:="", _
    PasswordTemplate:="", _
    WritePasswordDocument:="", _
    WritePasswordTemplate:="", _
    Revert:=False, _
    Format:=wdOpenFormatAuto, _
    Connection:=sConnection, _
    SQLStatement:="SELECT * FROM `MailMerge$`", _
    SQLStatement1:="", _
    SubType:=wdMergeSubTypeAccess

    End Sub
    [/VBA]
    _______________________________________________
    Please don't cross-post without providing links to your cross-posts. We answer questions for free. Please don't waste the time of the people helping you.
    For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

    - Frosty

  4. #4
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,475
    Location
    FYI - you can use Debug.Print to put the results of something in the immediate window (available under the View menu)
    Debug.Print sConnection

    Or you can hover over a variable (not as useful, when it's this long) when stepping through

    Or you can type:
    ?sConnection
    in the immediate window, and see what it is when stepping through the code.

    I get your connection string as this...
    Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=H:\Jonathon Hunter Hill\Mail Merge\AirMaster V 0.9.xlsm;Mode=Read;Extended Properties="HDR=YES;IMEX=1;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=35;Jet OLEDB:

    I'm no expert, but that doesn't look wrong around the "Extended Properties" area...

  5. #5
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,531
    Location
    Cross-posted at: http://www.mrexcel.com/forum/newrepl...3128&noquote=1
    For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184
    Cheers
    Paul Edstein
    [MS MVP - Word]

  6. #6
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    Apologies for the cross post...I didn't realise that the forums were connected at all, and thought that this forum was more applicable for the issue. I requested that topic be removed on the other forum, before I posted in this one.

    xld, thanks for your input. I think Frosty sorted that out. It didn't make difference, unfortunately.

    Frosty, thank you very much. It's at least legible now, if not working! I think the problem is that I've got
        ActiveDocument.MailMerge.OpenDataSource _    Name:=SourceDoc, _
    and it doesn't like how I've got SourceDoc as an input. This can't be avoided, due to the nature of how the program will be used.

    I've got it working like this:

    Sub MailMerge()
        Dim SourceDoc As String
        Dim SelectedItems(1) As String
        Dim sConnection As String
         
         
        With Application.FileDialog(msoFileDialogFilePicker)
            .AllowMultiSelect = False
            If .Show <> 0 Then
                SelectedItems(1) = SourceDoc
            Else
                Exit Sub
            End If
        End With
         
         'your connection string, also more easily separated with line breaks and arguments
        sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "User ID=Admin;" & _
        "Data Source=""H:\Jonathon Hunter Hill\AirMaster\AirMaster V 0.9.xlsm"";" & _
        "Mode=Read;" & _
        "Extended Properties=""HDR=YES;IMEX=1;"";" & _
        "Jet OLEDB:System database="""";" & _
        "Jet OLEDB:Registry Path="""";" & _
        "Jet OLEDB:Engine Type=35;" & _
        "Jet OLEDB:"
         
         'using your conection string... with the parameter names and the passed values separated nicely
        ActiveDocument.MailMerge.OpenDataSource _
        Name:="H:\Jonathon Hunter Hill\AirMaster\AirMaster V 0.9.xlsm", _
        ConfirmConversions:=True, _
        SQLStatement:="SELECT * FROM `MailMerge`"
        'ReadOnly:=False, _
        'LinkToSource:=True, _
        'AddToRecentFiles:=False, _
        'PasswordDocument:="", _
        'PasswordTemplate:="", _
        'WritePasswordDocument:="", _
        'WritePasswordTemplate:="", _
        'Revert:=False, _
        'Format:=wdOpenFormatAuto, _
        'Connection:=sConnection, _
        'SQLStatement1:="", _
        'SubType:=wdMergeSubTypeAccess
        
        ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
         
    End Sub
    But it's not acceptable for me to have the source in there, as I've said. So, does anyone have any suggestions of how to get around this? Also, I have commented out most of the ActiveDocument bit because the link to that document makes it over 255 characters, which is apparently unacceptable...

    Thanks for your help, chaps.

  7. #7
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    Strangely enough, if I set SourceDoc in the code, it has no problems, like so:

    Sub MailMerge()    Dim SourceDoc As String
        Dim SelectedItems(1) As String
        Dim sConnection As String
         
        SourceDoc = "H:\Jonathon Hunter Hill\AirMaster\AirMaster V 0.9.xlsm"
        
        'With Application.FileDialog(msoFileDialogFilePicker)
        '    .AllowMultiSelect = False
        '    If .Show <> 0 Then
        '        SelectedItems(1) = SourceDoc
        '    Else
        '        Exit Sub
        '    End If
        'End With
         
         'your connection string, also more easily separated with line breaks and arguments
        sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "User ID=Admin;" & _
        "Data Source=SourceDoc;" & _
        "Mode=Read;" & _
        "Extended Properties=""HDR=YES;IMEX=1;"";" & _
        "Jet OLEDB:System database="""";" & _
        "Jet OLEDB:Registry Path="""";" & _
        "Jet OLEDB:Engine Type=35;" & _
        "Jet OLEDB:"
         
         'using your conection string... with the parameter names and the passed values separated nicely
        ActiveDocument.MailMerge.OpenDataSource _
        Name:=SourceDoc, _
        ConfirmConversions:=False, _
        ReadOnly:=False, _
        LinkToSource:=True, _
        AddToRecentFiles:=False, _
        Revert:=False, _
        Format:=wdOpenFormatAuto, _
        SQLStatement:="SELECT * FROM `MailMerge`"
        'Connection:=sConnection
        'SQLStatement1:="", _
        'SubType:=wdMergeSubTypeAccess
        'PasswordDocument:="", _
        'PasswordTemplate:="", _
        'WritePasswordDocument:="", _
        'WritePasswordTemplate:="", _
    
    
    End Sub
    But, if I have the user select the source document it fails to execute.

  8. #8
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    I have a further, very strange, thing going on. I have put this macro where macros record to in the project browser (under Normal, Modules, NewMacros), but this seems to have gone across all my documents??

    E: Fixed it, had to go under TemplateProject

  9. #9
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,531
    Location
    Quote Originally Posted by dirtychinch View Post
    Apologies for the cross post...I didn't realise that the forums were connected at all, and thought that this forum was more applicable for the issue. I requested that topic be removed on the other forum, before I posted in this one.
    Evidently you haven't read what's in the link. The whole point about cross-posting etiquette that you'll read there is that the forums aren't linked and, by not applying the etiquette (which amounts to nothing more than common courtesy), you could end up having different people working on the same issue, possibly unnecessarily traversing the same ground and maybe even working at cross purposes.

    As for:
    I think the problem is that I've got
        ActiveDocument.MailMerge.OpenDataSource _    Name:=SourceDoc, _
    and it doesn't like how I've got SourceDoc as an input. This can't be avoided, due to the nature of how the program will be used.

    I've got it working like this:

    Sub MailMerge()
        Dim SourceDoc As String
    '...
        With Application.FileDialog(msoFileDialogFilePicker)
            .AllowMultiSelect = False
            If .Show <> 0 Then
                SelectedItems(1) = SourceDoc
            Else
                Exit Sub
            End If
        End With
         
         'your connection string, also more easily separated with line breaks and arguments
        sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "User ID=Admin;" & _
        "Data Source=""H:\Jonathon Hunter Hill\AirMaster\AirMaster V 0.9.xlsm"";" & _
        '...
        Name:="H:\Jonathon Hunter Hill\AirMaster\AirMaster V 0.9.xlsm", 
    '...     
    End Sub
    But it's not acceptable for me to have the source in there, as I've said.
    there's not much point in defining and selecting a source document, then not using it!

    Your two references to:
    "H:\Jonathon Hunter Hill\AirMaster\AirMaster V 0.9.xlsm"
    should be replaced with:
    SourceDoc
    Cheers
    Paul Edstein
    [MS MVP - Word]

  10. #10
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    Quote Originally Posted by macropod View Post
    Evidently you haven't read what's in the link. The whole point about cross-posting etiquette that you'll read there is that the forums aren't linked and, by not applying the etiquette (which amounts to nothing more than common courtesy), you could end up having different people working on the same issue, possibly unnecessarily traversing the same ground and maybe even working at cross purposes.
    I appreciate that, and no, I hadn't read the link. As I said, I had requested that that thread be closed.

    Quote Originally Posted by macropod View Post
    As for:

    there's not much point in defining and selecting a source document, then not using it!

    Your two references to:
    "H:\Jonathon Hunter Hill\AirMaster\AirMaster V 0.9.xlsm"
    should be replaced with:
    SourceDoc
    If I replace those references with SourceDoc, it then has the execution problem...

  11. #11
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    To follow up on my last post, this code does not work (with the sourcedoc thing changed). I get runtime error 4198.

    Sub MailMergeFromExcel()
    
        Dim SourceDoc As String
        Dim SelectedItems(1) As String
        Dim sConnection As String
         
         With Application.FileDialog(msoFileDialogFilePicker)
             .AllowMultiSelect = False
             If .Show <> 0 Then
                 SelectedItems(1) = SourceDoc
             Else
                 Exit Sub
             End If
         End With
         
    'your connection string, also more easily separated with line breaks and arguments
        sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "User ID=Admin;" & _
        "Data Source=SourceDoc;" & _
        "Mode=Read;" & _
        "Extended Properties=""HDR=YES;IMEX=1;"";" & _
        "Jet OLEDB:System database="""";" & _
        "Jet OLEDB:Registry Path="""";" & _
        "Jet OLEDB:Engine Type=35;" & _
        "Jet OLEDB:"
         
    'using your conection string... with the parameter names and the passed values separated nicely
        ActiveDocument.MailMerge.OpenDataSource _
        Name:=SourceDoc, _
        ConfirmConversions:=False, _
        ReadOnly:=False, _
        LinkToSource:=True, _
        AddToRecentFiles:=False, _
        Revert:=False, _
        Format:=wdOpenFormatAuto, _
        SQLStatement:="SELECT * FROM `MailMerge`"
        'Connection:=sConnection
        'SQLStatement1:="", _
        'SubType:=wdMergeSubTypeAccess
        'PasswordDocument:="", _
        'PasswordTemplate:="", _
        'WritePasswordDocument:="", _
        'WritePasswordTemplate:="", _
    
    
         
    End Sub
    But the problem only occurs if you use a dialogue box to do it. If you set SourceDoc= "xyz" then it does work...
    Last edited by dirtychinch; 02-21-2014 at 08:01 AM.

  12. #12
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,475
    Location
    You should step through your code, and hover over the value of source doc. It sounds like it's not the value you think it is...

  13. #13
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    I've been trying to do that, but it doesn't get past the point of "SelectedItems(1) = SourceDoc" so I can't read the value. I changed that to "Set SelectedItems(1) = SourceDoc" and now I get error 91...

  14. #14
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,475
    Location
    Then you should try to see what the value of SelectedItems(1) is... you might need SelectedItems(0).

    Here's the thing... that whole File Picker dialog exposes you to a lot of different potential inputs, if you're not validating what you get back. Setting the .AllowMultiSelect to false is good... but then you need to make sure (I believe) that they've also selected a valid file (how do you know they didn't select a power point presentation?

    I don't have access to a coding box here... but it sounds like you're on the right path. You're trying to have a way of getting the right info from a dialog picker... so focus on that. You don't have a MailMerge problem, yet.

    In the immediate window, try typing:
    ?SelectedItems(0)
    and
    ?SelectedItems(1)

  15. #15
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    Hi Frosty,

    Sorry I didn't reply sooner - I'm not taking my work home for the weekend

    Yes you've nailed it down - I need to get the right information from the dialogue picker.

    I have tried typing those commands into the immediate window, but that doesn't do anything...nor does hovering over them..do I need to run them in a sub or something?

    I'm not fixed on how to do the file picking bit, so if you have any other suggestions then please do let me know. I just need the mail merge to work!!


  16. #16
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    I've now made some minor changes to the code, but it comes up with run-time error '91' now, just after I select the source document. Any ideas?!

    Sub MailMergeFromExcel()
         
        Dim SelectedItems(1) As Object
        Dim sConnection As String
         
        With Application.FileDialog(msoFileDialogFilePicker)
            .AllowMultiSelect = False
            .Filters.Add "Excel Macro-Enabled Workbook", "*.xlsm"
            If .Show <> 0 Then
            Else
                Exit Sub
            End If
        End With
         
        'your connection string, also more easily separated with line breaks and arguments
        sConnection = "Provider=Microsoft.ACE.OLEDB.14.0;" & _
        "User ID=Admin;" & _
        "Data Source=SelectedItems(1);" & _
        "Mode=Read;" & _
        "Extended Properties=""HDR=YES;IMEX=1;"";" & _
        "Jet OLEDB:System database="""";" & _
        "Jet OLEDB:Registry Path="""";" & _
        "Jet OLEDB:Engine Type=35;" & _
        "Jet OLEDB:"
         
        'using your conection string... with the parameter names and the passed values separated nicely
        ActiveDocument.MailMerge.OpenDataSource _
        Name:=SelectedItems(1), _
        ConfirmConversions:=False, _
        ReadOnly:=False, _
        LinkToSource:=True, _
        AddToRecentFiles:=False, _
        Revert:=False, _
        Format:=wdOpenFormatAuto, _
        SQLStatement:="SELECT * FROM `MailMerge`", _
        Connection:=sConnection, _
        SQLStatement1:="", _
        SubType:=wdMergeSubTypeAccess, _
        PasswordDocument:="", _
        PasswordTemplate:="", _
        WritePasswordDocument:="", _
        WritePasswordTemplate:=""
         
    End Sub

  17. #17
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,475
    Location
    Yeah, you don't want to start randomly changing data types, to hope something works. You'll get a different error message, but the root of the problem is the same. Why don't you try something like this, just to test that functionality...
    [VBA]
    Sub Test()
    MsgBox fGetFilePath
    End Sub
    Public Function fGetFilePath() As String
    With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "My Filter", "*.dotm"

    If .Show = 0 Then
    Exit Function
    End If
    fGetFilePath = .SelectedItems(1)
    End With

    End Function
    [/vba]
    I'm not totally familiar with the file picker, and if I had to guess, I would guess that you're getting some kind of incomplete return based on the multiple application of filters on a persistent object. Because the usage is really really simple.

    Try modifying just the function above and seeing if your message box result continues to be an empty string, or if it is actually representative of the path you want.

  18. #18
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,475
    Location
    Oh, jeez... I just realized why it's not working... you're not using .SelectedItems as a property of the file picker... you're using it as your own variable, which you *never set* within the with block. Of course your own variable named "SelectedItems" is going to be blank... what you want is the .SelectedItems array which is a property OF the dialog picker... and my demo code shows the usage of.

    So... take my function, then add a variable called "strSourcePath" as a string to your main function (and remove your own SelectedItems(1) variable)... and set the new variable to the result of fGetFilePath... and if the result is "", then exit your main routine.

    This gives you a subroutine you can use in other places as well, if you wanted to change filters or something. You'll also need to modify the filter back to the way you want it to be.

    See how far you can get with the above guidance, and then post back any questions or your working code.

  19. #19
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    Hi Frosty,

    Thanks for your help. I have actually fixed the problem, but wanted to be sure before I updated the thread. I had to use a loop to get it working, but it's pretty much perfect now. The only problem I have is that it takes about 30 seconds to work, so if you have any hints as to why that might be, I'd appreciate them.

    The code now is as follows:

    Sub MailMergeFromExcel()
         
        Dim sConnection As String
        Dim lngCount As Long
        
        'Open the file dialog
        With Application.FileDialog(msoFileDialogFilePicker)
                .AllowMultiSelect = True
                .Show
            For lngCount = 1 To .SelectedItems.Count
                
                'your connection string, also more easily separated with line breaks and arguments
                sConnection = "Provider=Microsoft.ACE.OLEDB.14.0;" & _
                "User ID=Admin;" & _
                "Data Source=SelectedItems(lngCount);" & _
                "Mode=Read;" & _
                "Extended Properties=""HDR=YES;IMEX=1;"";" & _
                "Jet OLEDB:System database="""";" & _
                "Jet OLEDB:Registry Path="""";" & _
                "Jet OLEDB:Engine Type=35;" & _
                "Jet OLEDB:"
         
                'using your conection string... with the parameter names and the passed values separated nicely
                ActiveDocument.MailMerge.OpenDataSource _
                Name:=.SelectedItems(lngCount), _
                ConfirmConversions:=True, _
                ReadOnly:=False, _
                LinkToSource:=True, _
                AddToRecentFiles:=False, _
                Revert:=False, _
                Format:=wdOpenFormatAuto, _
                SQLStatement:="SELECT * FROM `MailMerge`", _
                Connection:=sConnection, _
                SQLStatement1:="", _
                SubType:=wdMergeSubTypeAccess, _
                PasswordDocument:="", _
                PasswordTemplate:="", _
                WritePasswordDocument:="", _
                WritePasswordTemplate:=""
                    
            Next lngCount
        End With
        
    End Sub

  20. #20
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,475
    Location
    There is no way the code you just posted works. Please review with my notes above in mind. Are you using option explicit?

    and do you actually want the ability to select multiple source files and perform the mail merge on them? Becuse
    youve switched the core way your code was working, from the end-user perspective (allowing multi-select vs not allowing)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •