Consulting

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

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

Hybrid View

Previous Post Previous Post   Next Post Next Post
  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 Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    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,480
    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,480
    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
    4,435
    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
    [Fmr 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
    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

  8. #8
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    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
    [Fmr MS MVP - Word]

  9. #9
    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...

  10. #10
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Quote Originally Posted by dirtychinch View Post
    Apologies for the cross post...I didn't realise that the forums were connected at all,
    The internet make the earth a very small world particularly when people join multiple forums to gain a wider experience in subject such as Excel and VBA etc. Please take the time to read the link provided by Paul.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  11. #11
    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.

  12. #12
    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.

  13. #13
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    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...

  14. #14
    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...

  15. #15
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    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)

  16. #16
    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!!


  17. #17
    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

  18. #18
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    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
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    What is this supposed to be doing?
     If .Show <> 0 Then 
                SelectedItems(1) = SourceDoc
    because it looks like you are trying to set the filepicker output (read only) to be the same as an uninitialized variable (Value = "").

     If .Show <> 0 Then 
                SourceDoc = SelectedItems(1)
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

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

    Post

    Quote Originally Posted by Frosty View Post
    Your replaced the appWord variable... But then left Word.Documents.Add.

    Use appWord instead, and also dim a new variable... A document variable.

    Dim oDoc As Wird.Document.

    Set oDoc = appWord.Documents.Add (Template:= yada yada)

    then use that document object variable to run the mail merge. You'll probably need to pass as a parameter. I can't easily give you your code edited back at the moment, since I'm still typing on a phone.

    Means yes, we're very helpful-- to someone with a great attitude, like you've brought. So thank you!
    Frosty, I've made those changes but it still just opens the word document, and then does nothing. My code is now as follows:

     'encapsulates getting the word application, using GetObject first, and Create obectPublic Function fGetApp(Optional bCreated As Boolean) As Object
        Dim oRet As Object
         
         'attempt to get it, ignoring any error if it isn't launched
        On Error Resume Next
        Set oRet = GetObject(, "Word.Application")
         
         'if we didn't get it, then attempt to create it, but reset error trapping
        On Error GoTo 0
        If oRet Is Nothing Then
            Set oRet = CreateObject("Word.Application")
            bCreated = True
        End If
         
        Set fGetApp = oRet
    End Function
    Sub MailMerge()
    
    
       Dim appWord As Word.Application
       
       MSG1 = MsgBox("Do you want to continue with the mail merge?", vbYesNo, "Confirm")
    
    
        If MSG1 = vbYes Then
        ElseIf MSG1 = vbNo Then
            Exit Sub
        End If
       
    'Ensures workbook saved
        ThisWorkbook.Save
        
    'Create new quotation for template
        Set appWord = fGetApp
        appWord.Documents.Add Template:="Q:\AirMaster\AirMaster Quotation1.dotm", NewTemplate:=True, DocumentType:=0
        appWord.Visible = True
    
    
        With appWord
            .Visible = True
            .ActiveDocument.SaveAs2 Filename:="Prod Quote_xxAMxHRV_ProjName " & Format(Date, "ddmmyyyy") & ".docx"
        End With
    
    
        Application.ScreenUpdating = False
        Application.CutCopyMode = False
        Application.DisplayAlerts = True
        
    End Sub
    I don't understand what you mean about passing a parameter...

    Also, do I need to dump this code?

    Sub MailMergeFromExcel()
         
        Dim sConnection As String
        Dim strSourcePath As String
         
        strSourcePath = fGetFilePath
         
        If strSourcePath = "" Then
            Exit Sub
        End If
         
         'your connection string, also more easily separated with line breaks and arguments
        sConnection = "Provider=Microsoft.ACE.OLEDB.14.0;" & _
        "User ID=Admin;" & _
        "Data Source=" & strSourcePath & ";" & _
        "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:=strSourcePath, _
        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
    Quote Originally Posted by SamT View Post
    What is this supposed to be doing?
     If .Show <> 0 Then 
                SelectedItems(1) = SourceDoc
    because it looks like you are trying to set the filepicker output (read only) to be the same as an uninitialized variable (Value = "").

     If .Show <> 0 Then 
                SourceDoc = SelectedItems(1)
    Hi Sam, thanks but that code isn't in use any more. You are right though...I didn't quite understand what was going on with that code at the time.

    Quote Originally Posted by Aussiebear View Post
    The internet make the earth a very small world particularly when people join multiple forums to gain a wider experience in subject such as Excel and VBA etc. Please take the time to read the link provided by Paul.
    I did read the link, thank you. As I said before, I'd requested that the post on that forum be removed before I even joined this one.

Posting Permissions

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