Consulting

Page 2 of 5 FirstFirst 1 2 3 4 ... LastLast
Results 21 to 40 of 82

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

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

    The code works perfectly, and the code is only for selecting single documents as the purpose of this is to produce a single quotation with little effort from the operator.

    Do you think it's worth modifying the code as you've suggested, considering it works?

    E: in fact, I'll give it a go now in the hope that it'll speed the process up.

  2. #22
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    I've ended up with this, which definitely isn't correct:

    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
        
        MailMergeFromExcel1
         
    End Function
    Sub MailMergeFromExcel1()
         
        Dim sConnection As String
        Dim strSourcePath As String
        Dim fGetFilePath As String
         
        Set fGetFilePath = strSourcePath
         
         '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

  3. #23
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    Well, if it's working, it's because you don't need your connection string at all, because it is currently worthless. The fact that it takes 30 seconds is because you're probably finding your source doc from an alt method (30 seconds is probably the time for your bad connection string to give up and go another way).

    also, have you tried selecting multiple files? You allow it in your code, someone will do it.

    You don't need this loop at all-- you just happened to copy the relevant correct usage of .SelectedItems from wherever you got this code, rather than what you had before.

    I know you just want it to work. But I'm more interested in you understanding why it works (or doesn't). Teach someone to fish, and all that

  4. #24
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    Haha fair enough! I'm about to clock off, so will give your instructions a better shot tomorrow.

    I'll make sure I disable the multiple file selection - haven't tested that s yet. I did indeed just copy the correct stuff from some other code...caught me

    Yes indeed, and I appreciate you teaching me. As I say, I'm about to clock off so I'll try tomorrow and report back

    Thanks for your help, sir.

  5. #25
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    That was a good effort... here is what you're looking for. See if you can understand the main differences:
    1. You call a function, so you just care about the result. You don't want to call your "main" function from within that function. Try out the TestFilePicker to see how the function works.
    2. when you're trying to pass the value of a variable to a string... you need to make sure you're not passing the name of the variable, instead of the variable.... the difference between

    "hello & strTemp & world"
    and
    "hello " & strTemp & " world"

    3. You don't need to dim a variable with the same name as a function... you use the result of the function and plop it into a different variable.

    [vba]
    Public Sub TestFilePicker()
    MsgBox fGetFilePath
    End Sub


    'function to return the path of a single selected file
    Public Function fGetFilePath() As String
    With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Filters.Clear
    '.Filters.Add "My Filter", "*.dotm"
    .Filters.Add "Excel Macro-Enabled Workbook", "*.xlsm"
    If .Show = 0 Then
    Exit Function
    End If
    fGetFilePath = .SelectedItems(1)
    End With

    End Function
    Sub MailMergeFromExcel1()

    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
    [/vba]

  6. #26
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    I still don't know if your connection string is correct in your environment, but this will be closer than before. I still have concerns about that portion-- but you should be able to select a file properly now.

  7. #27
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    Good morning, sir.

    That works beautifully, now! As I understand it, there's a subtle difference in the way that you've attached the the file path to a variable. I didn't realise that you could retain that .SelectedItems(1) value outside of the with loop. But, am I right in saying that's only if it's defined as a function?

    Are there clearly defined occasions on which you should use a function, as opposed to a sub? I don't understand the difference...

    This works perfectly now, and is almost instantaneous, so thank you very very much!!

  8. #28
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    OK, oddly enough it works very quickly when standalone, but when combined with my spreadsheet is very slow. Would you mind taking a look at my excel code here, which I use to run the macro? Oddly enough, it becomes slow once Word has its part, like it was doing before you amended my code for me.

    Sub MailMerge()
    
       Dim Word As Word.Application
       
    'Ensures workbook saved
       
        MsgBox ("This workbook will be automatically saved before continuing.")
        ThisWorkbook.Save
        
    'Create new quotation for template
        Set Word = CreateObject("Word.Application")
        Word.Documents.Add Template:="Q:\AirMaster\AirMaster Quotation.dotm", NewTemplate:=True, DocumentType:=0
        Word.Visible = True
    
    
        With Word
            .Visible = True
            .ActiveDocument.SaveAs2 Filename:="Prod Quote_xxAMxHRV_ProjName " & Format(Date, "ddmmyyyy") & ".docm"
        End With
    
    
        Application.ScreenUpdating = False
        Application.CutCopyMode = False
        Application.DisplayAlerts = True
        
    End Sub

  9. #29
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    You've got four actions which can cause processing time: 2 saves. 1 open/new and a createobject. I assume you can't get around the saves and new (and speed of those obviously depends in size of document as well as network speed, since these appear to be on network locations).

    CreateObject *always* creates a new instance of word. You're also not ending that instance of word in this code.

    What you should do is try to use GetObjext first (to use an exising word process, if possible). And then if you do have to use CreateObject, make sure end your Word process, if appropriate (if you're just saving the document).

    Have you checked your task manager? I'm guessing you have a lot of winword.exe processes in the processes tab. This will definitely slow down your computer over time too

  10. #30
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    I'm actually not thrilled with the structure of this... You have the code for the mail merge being triggered from an AutoNew within the Word template, but are calling the code from Excel. Why not just have all the code in your excel template? It would be easier to debug and troubleshoot, unless you also need the ability to open the word document from word and have it automatically generate.

    If you can describe the actual desired workflow, there may be a better way to go about what you're doing.

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

    I don't think it's ideal either, but this was the best strategy that I could determine. Of course, I'm happy to take any advice you have about the method.

    So the process is quite simple; we use an Excel spreadsheet to generate our quotations. Currently, the operators just copy an image of the quotation in excel, and drop it into a word document, which I think is unacceptable. What I want to be able to do is for them to complete the quotation in excel, as they currently do, and then just have them press a button, which will transfer the quotation into a word document.

    I don't need to open the word document from word and have it automatically generate, but I do need to end up with a word document, if that makes any sense.

  12. #32
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    They click a button in excel... Magic happens, and they end up with an active/open Word document? Which they can then cut/paste the contents of into another word doc?

    i guess I'd have to see the mail merge, but I'm not sure why they start in excel... Why not have them do whatever they want in excel, then click a button in word and have the contents end up in their clipboard? Or pasted where the cursor is?

    keep describing as completely as you can until I get a real picture of the process, then I'm betting you'll end up with something better than just "use GetObject before trying CreateObject" (which is the best and only advice I can give you, at the moment)

  13. #33
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    Haha yes, magic indeed! They just want to churn figures out, which is fine by me.

    I could have them copy and paste, but really want to automate the process because, frankly, people are incompetent, and take it upon themselves to modify things etc, and may not even have a rudimentary grasp of how copy & paste work - basically I need it to be as idiot proof as possible.

    OK a better break down for you, though I'm not sure I can provide that much detail:

    - User fills quotation using excel program. it's basically a process of selecting the unit they want, pressing a button, options come up, they select them

    - press another button, and the information that they've just generated gets transferred (using VBA) from the input sheet to a summary sheet, which is a facsimile of the quotation

    - repeat this process until the quotation is filled

    - at this point they can amend the quotation as they wish, as long as they only touch certain cells. I've warned them off clicking on other things because I know that if they break a formula, they won't be able to fix it. I can only lock them down so far though

    - user presses transfer to quote button, and then the mail merge happens

    I hope that's helpful...

  14. #34
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    And what should happen after the mail merge? User is left with a word document to do something with? Or do they just need the word document saved to a folder but not left open? Do they need to do anything in word? Or do they immediately go back to excel to generate another quote?

  15. #35
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    Also, you can protect a sheet in excel, such that only certain cells are able to be edited. This is a good way to protect formulas

  16. #36
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    I know of the protection, I just haven't set it up because I like them to have some ability to edit. It's the end result that concerns me. If they break the spreadsheet then they'll just have to start again, hah! (translation: I can't be bothered to protect it properly)

    After the mail merge they have the word document with the filled quotation. This will then probably be copied and pasted into a letter, the contents of which I'm unsure of. I expect that they will want to save that document, but they don't necessarily need it to open up at that time. They may do another quote immediately after, but they may open the quotation to copy it into this letter...

  17. #37
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    Fair enough. Well, I guess my only advice is try getobject first. Sample code is easy to find, or I can post it later. If you knew what your end users wanted to do after pressing the magic button, then you could skip showing word at all, and simply give an "output to where?" dialog, which your code could use for the later SaveAs process of the mail merge result.

    I'd probably recommend pulling the code out of autonew of the word template, and putting it into excel (based on your excel code, you already have references to the word object model in your excel project, so this port will be relatively easy and be easier to debug later). Launching word and waiting for an auto macro to process could be slower than explicitly telling word to open a document and then run a mail merge.

    I suspect you're doing this because you think you need to use ActiveDocument to trigger the mail merge. You don't. Use a document variable combined with documents.add to set the variable to the document you're going to open.

    I'd also question why you're saving the result of the mail merge as a .docm file-- you don't need macros in that file, especially if they are likely to just cut and paste the contents. Use .docx instead.

    Let us know how it goes.
    Last edited by Frosty; 02-26-2014 at 08:13 AM. Reason: Phone typos

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

    Thanks for that. I'll have a look around for some GetObject code, though would appreciate it if you wouldn't mind showing me some code? I would be happy for the document not to open, to be honest. I think it would be best if it just had this save to where dialog.

    The autonew code bit...I only did that because I didn't know what to do with integration between excel and word. If there's a way to do it in excel, and you wouldn't mind telling me how to do it, then I'd be more than happy to do that. I can't say that I like the autonew code at all.

    I'll try and get it saving as docx. I'm not clear why I set it to docm in the first place, hah.

    Thanks

  19. #39
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    I just wrote this, but it's the basics... there are a lot of different ways to do this...
    [VBA]
    'encapsulates getting the word application, using GetObject first, and Create obect
    Public 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
    [/vba]

    Usage in your existing code would be to do
    Set Word = fGetApp
    instead of
    Set Word = CreateObject("Word.Application")

    HOWEVER -- you should rename your variable "Word" to "appWord" ... otherwise you're using an object library ("Word") and a variable name for an application object ("Word") in the same project. Very bad practice.

    From there, just try copy and pasting all of your Word code into your Excel project, and then try to run, work out kinks... post your results, and I'll help from there.
    _______________________________________________
    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

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

    Thanks for the code, and for the advice! I can never get over how helpful people on forums like these are.

    This is my code now, for the mail merge only (all in Excel):

     'encapsulates getting the word application, using GetObject first, and Create obect
    Public 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
        Word.Documents.Add Template:="Q:\AirMaster\AirMaster Quotation1.dotm", NewTemplate:=True, DocumentType:=0
        Word.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 deleted all the code from my word document, as you advised. However, that code above asks me correctly whether I want to merge, and then asks me from which document, but it then just opens the word document and doesn't actually carry out the mail merge.

    Should I be using this code also:

    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

Posting Permissions

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