Consulting

Page 4 of 5 FirstFirst ... 2 3 4 5 LastLast
Results 61 to 80 of 82

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

  1. #61
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    Sorry, I missed these responses. I'm not 100% sure what you're asking/describing with it "doesn't actually end the mail merge, which seems to slow everything down." I'm also not sure about toggling the preview... I guess I'm a little lost on where you're at on this (things go in and out of my brain). But I've never actually tried to run this code, I've just assumed it's been running. When I start looking at the .MailMerge object, I'm not sure if .OpenDataSource is actually the method you want to use.

    You may want to use the .Execute method off the .MailMerge object, or you might want to make some sort of use of .MailMerge.DataSource.Close.

    I was mostly trying to streamline your code, and help you get it all into Excel.... but maybe there was something else having the Word code in AutoNew. At the end of the day, you want to save the RESULTS of the mail merge, rather than a copy of the mail merge template, right?

    if you've already solved, maybe try posting some of the code which has gotten you closer. I'd have to take a good bit of time to try and reconstruct this whole scenario from scratch, even with the code above.

  2. #62
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    Morning Frosty, not to worry! I knew you'd stumble upon them eventually. Ever the hero

    My code has not changed at all since the last lot that was posted. I was happy that it worked enough for it to be operable, but the operator is finding it a little problematic, and has raised a few issues.

    You're absolutely correct about me wanting the results of the merge - I just need a word document with the text in it from my spreadsheet!!

    When I said about ending the merge, I mean that firstly it doesn't toggle the preview, which was what AutoNew was doing (badly). Secondly, the documents still seem connected. What I mean by this is that if you were to reopen the word document, it would always ask if you want to refresh the source (not sure of the exact terminology), which isn't ideal. I think you're idea of using .Execute may work, as that sounds like the process would be closed by the end of it. However, if I change .OpenDataSource to .Execute, then "Named argument not found" and the debugger selects "Name:=strSourcePath". I thought it may be as a consequence of it not being defined, but it very much is defined.

    One thing I've been trying to get it to do is to add a random number to the name string, to avoid an error whereby you may already have a quote open with an identical name. Additionally, the file names/destinations can be quite long, as some quotations are stored deep within a server here. As a consequence, almost always I get the error with regards to the character string being more than 255 characters. Is there some way round this as it's become quite problematic?

  3. #63
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    Only way around too long of file names is mapping a drive to that location as a way of shortening the overall string. Well, that's not entirely true, there is also a "short name" property that includes a tilde and a number, so that c:\Program Files is also accessible via C:\Progra~1. But that's a really dodgy way to approach, as the tilde is constant, but the number is incremented by the presence
    of other folders with the same first 6 letters. I don't know what it does if there are 7 of the same... Maybe goes alpha.

    Random number is easy to generate, but you're already dealing with long file names, maybe it would be better to simply check if the file name suggested is already there, and then simply increment up a number as a suffix, check again, etc. random numbers in file names are pretty annoying.

    As as for the other (.execute, etc). Now is the time to start reading the help file. Put your cursor inside the .Execute part of the line, and press F1. Or look it up online.... Eventually you'll end up at an msdn link. This is the other way to learn how to use objects properly (you got this far using the first way: record a macro doing what you want). Now you know what you want, and the recorded macro doesn't get you far enough. So read up in help!

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

    You say mapping a drive to that location...what does this mean? The other solution doesn't sound wonder - I'd rather have the solid one!

    With regards to the random number generation/checking if a file was there, how would one check that a file is already open with that name? My thinking is basically to force it to close any word documents, but this isn't ideal if they're working on something else.

    I had a look at the msdn link but it doesn't mention anything do with the name issue...

  5. #65
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    Mapping a drive is something you can do in Windows Explorer, manually or automatically via another script... and say you have a network location which is
    \\mynetworkshare\afolder\asubfolder\

    You can "map" to that network location, and call it the J:\ drive (or all the way up to Z:\). Some mapped drives are hard coded (like A:\ and B:\ traditionally used for floppy drives, C:\ as the hard drive, D:\ and maybe E:\ as CD-ROM drives, etc etc). If you have any network drives (usually something from F:\ or G:\ on up), then you'll see what the "true" path to those network drives is.

    If you had a really long location, even if it was already a mapped drive, you could map to the new location...
    for example:
    F:\subfolder\anothersubfolder\anotherlongsubfoldernamewhichisentirelyridicu lous\shortsub

    You could map your Z:\ to be all the way to that ShortSub folder at the end... and then any code could reference the Z:\ in order to get around long file name limitations. I'm sure you could do this in some fashion from VBA, but I'd be a little hesitant to do it dynamically as it could be slow, and network engineers generally like to own the mapping of network drives.

    Incidentally, if this is a major company-wide problem in terms of really deeply nested sub folders without the use of mapped drives... you've got bigger issues. Seriously. You'll be unable to perform certain kinds of network backups and a host of other issues far beyond the scope of this thread.

    You can use the FileSystemObject scripting to detect whether a file exists or not-- whether it is "locked" (i.e., open by someone else) is a separate issue, although I don't think that matters, since we're talking about saving a document not opening a document. There are a lot of different ways to detect whether a file exists, try searching on it. My only readily available code on that is buried in a class object I re-use in all my projects.

  6. #66
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    Ron de Bruin has some good code on testing whether a file exists (and generally has great resources for this stuff, as does Greg Maxey's site, and Chip Pearson's).
    http://www.rondebruin.nl/win/s9/win003.htm
    http://gregmaxey.mvps.org/word_tips.html
    http://www.cpearson.com/Excel/Topic.aspx

    Only the rondebruin site has something specific to this topic, but greg has some excellent tips and accessible code. Chip Pearson has some more advanced stuff, but I mention it because if you find his code when doing google searches, it's pretty great.

  7. #67
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    I prefer to:

    Design a Worddocument
    Include the desired mergefields.
    Link the document to the Excelfile that contains the 'database'.
    Save that worddocument.(e.g. G:\OF\Maindocument.docx )

    The only code in VBA you will need is:

    Sub M_snb()
       with getobject("G:\OF\Maindocument.docx")
          .Mailmerge.execute
          .clode 0
       end with
    End Sub
    Only the resulting merged documents will be shown in Word.

    If you nevertheless prefer to do the redundant combining of maindocument & database by VBA from Excel you only need:
    NB. You can speed up the code considerably by commenting out: .windows(1).visible=true

    Sub M_snb()
        With CreateObject("word.document")
           .Windows(1).Visible = True
           .Content = String(20, vbCr)
           For j = 1 To Cells(1).CurrentRegion.Rows.Count
              .Fields.Add .Paragraphs(j).Range, 59, Cells(1, j).Text
           Next
           With .MailMerge
             .MainDocumentType = 0
             .Destination = 0
             .SuppressBlankLines = True
             .OpenDataSource ThisWorkbook.FullName
             .Execute
            End With
        End With
    End Sub

  8. #68
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    Hi guys,

    I must apologise profusely with regards to my lack of reply. I have no excuse, other than laziness, and the fact that the code was all working fine!

    Frosty, thank you for the information on mapping, and the detecting of documents. Fortunately, I have managed to get around the mapping issue by having the macro create a temporary document on the desktop of the user, which they can then delete (or if I could get it to work, would be deleted automatically).

    snb, thank you very much for the code. I would love to use your simple method, but when I run this code:

    Sub M_snb()    With GetObject("Q:\Index\Documents\FlatStation Quotation.dotm")
            .MailMerge.Execute
            .clode 0
        End With
    End Sub
    which refers to the document that I want to merge with, I get the runtime error 4605 on the .MailMerge.Execute line for some reason. It says that the mail merge needs a data source, but I thought that had been set with getobject...have you any idea why this might be?

    Another problem, in addition, is that really the document to come from a template file, but am not sure how I might do this. Do you have any ideas?

    Thanks, and sorry again for the lack of reply.

    dirtychinchilla

  9. #69
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    I have an entirely different problem now, which occurs with either the complex mail merge or this simple one, if I can get it to work.

    I have the Microsoft Word 14.0 Object Library switched on, which is the one used in the mail merge code. However, when I run the merge I get the very aggressive "Error has occurred: External table is not in the expected format." and then it asks me about data connections and stuff.

    Please help!! This is extremely annoying as I can't seem to guarantee that I can have this run out of the box on anyone's PC.

    The issue goes away if I save the excel document as .xlt, but this is not ideal considering everyone here has Office 2010, and there is some functionality that I'd like to retain, which is lost if I save as .xlt.

    Further to this, I still would like to get the mail merge to execute as opposed to leaving the connection open. I think snb's code would resolve this, if I could get it to work...

  10. #70
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    The answer lies in the first 4 lines of this post:

    http://www.vbaexpress.com/forum/show...l=1#post306594

    If the worddocument isn't a mailmerge maindocument, nothing will happen.

    amended a typo

    Sub M_snb() 
        With getobject("G:\OF\Maindocument.docx") 
            .Mailmerge.execute 
            .close 0 
        End With 
    End Sub

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

    Thanks for your response. I have made the word document, included the desired merge fields, and then I get stuck. You say link the file - surely it was linked when I managed to put the merge fields in, by carrying out most of a mail merge in excel.

    dirtychinchilla

  12. #72
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    Sorry for the triple post, but in addition to this, I'm wondering if there's a way to add the necessary reference libraries for any user, as I don't want to have to go and enable it on every PC manually. I found the following code online somewhere, and modified it a tiny bit:

    Sub AddReference()     'Macro purpose:  To add a reference to the project using the GUID for the
         'reference library
         
        Dim strGUID As String, theRef As Variant, i As Long
         
         'Update the GUID you need below.
        strGUID = "{00062FFF-0000-0000-C000-000000000046}"
         
         'Set to continue in case of error
        On Error Resume Next
         
         'Add the reference
        ThisWorkbook.VBProject.References.AddFromGuid _
        GUID:=strGUID, Major:=1, Minor:=0
         
         'If an error was encountered, inform the user
        Select Case Err.Number
        Case Is = 32813
             'Reference already in use.  No action necessary
        Case Is = vbNullString
             'Reference added without issue
        Case Else
             'An unknown error was encountered, so alert the user
            MsgBox "A problem was encountered trying to" & vbNewLine _
            & "add or remove a reference in this file" & vbNewLine & "Please check the " _
            & "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
        End Select
        On Error GoTo 0
    End Sub
    However, this seems to do absolutely nothing!! I tried various pieces of code, and none of them actually did anything. Also, I really struggled finding a library of the GUID's, so if anyone could advise me on that, it would be much appreciated.

  13. #73
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    Hey guys,

    I've made some advancement going on what snb provided. This is my code now:

    Function NewMerge()
    
    On Error GoTo ErrHandler:
    
    
        ThisWorkbook.SaveCopyAs Filename:="C:\Users\" & Environ$("Username") & "\Desktop\" & "Temp" & ".xls"
    
    
            With GetObject("Q:\Index\Documents\FlatStation Quotation.dotm")                 
                .Mailmerge.Execute
                .Close 0
            End With
        
        Kill ("C:\Users\" & Environ$("Username") & "\Desktop\" & "Temp" & ".xls")
        
    'Skip error handler
    Exit Function
    
    
    ErrHandler:
        Kill ("C:\Users\" & Environ$("Username") & "\Desktop\" & "Temp" & ".xls")
        MsgBox ("Run-time Error " & Err.Number)
        Exit Function
        
    End Function
    I changed the sub to be a function, which meant that I could call the function from elsewhere, and assign the calling macro to a button.

    This code works beautifully, for the most part. Having it both open and delete the temp file is great; this wasn't possible before because I was using mailmerge.opendatasource, leaving the files connected, and as such the temp file was always in use, whilst the word document was open.

    However, the problem I now have is that I really need to be able to set the data source for the mail merge to be this temp file. I had done that in Word, but because I need this to work on other people's computers and the document must be referring to my own temp file on my desktop, I cannot fully execute the merge. Does anyone know a way round this?

    Edit:

    I tried a different bit of code that I found online, modified as follows:

    Function MergeIt()
    
    Dim objWord As Word.Document
    Set objWord = GetObject("Q:\Index\Documents\FlatStation Quotation.dotm")
    
    
    ' Make Word visible.
        objWord.Application.Visible = True
        
    ' Set the mail merge data source.
        objWord.MailMerge.OpenDataSource _
        Name:="C:\Users\" & Environ$("Username") & "\Desktop\" & "Temp" & ".xls", _
        LinkToSource:=True, _
        Connection:="sConnection", _
        SQLStatement:="SELECT * FROM `MailMerge`"
    
    
    ' Execute the mail merge.
        objWord.MailMerge.Execute
    
    
    End Function
    This also works and allows me to determine the data source, but then when it carries out the execution, it leaves the original word document open, and then creates the merged document. It's just a Frankenstein combo of the previous two methods...and doesn't work perfectly either!
    Last edited by dirtychinch; 04-24-2014 at 07:14 AM.

  14. #74
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    A function has not been designed for that purpose, a sub has.

    You can call any macro simply by using it's name:

    Sub M_start()
       M_snb
    End Sub
    
    Sub M_snb() 
        With getobject("G:\OF\Maindocument.docx") 
            .Mailmerge.execute 
            .close 0 
        End With 
    End Sub

  15. #75
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    Mmm I realise now that it won't be possible to execute the merge and select a data source, unfortunately. More work for the operators!

    I know how to call a macro, but it was because the macro is in ThisWorkbook, and it seems you cannot call from there?

  16. #76
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    You can 'cal' from everywhere.

    Sub M_start() 
        ThisWorkbook.M_snb 
    End Sub

    In thisworkbook codemodule:

    Sub M_snb() 
        With getobject("G:\OF\Maindocument.docx") 
            .Mailmerge.execute 
            .close 0 
        End With 
    End Sub
    if the macro is in the sheet1 codemodule:

    Sub M_start() 
        Sheet1.M_snb 
    End Sub

  17. #77
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    Thanks for that, snb. I didn't end up needing it in the end

    I'm glad to say that I've mostly got the mail merge working to my satisfaction, but there doesn't seem to be a great deal of consistency and reliability for the program.

    My code:

    Sub MailMerge()
    
        Dim objDoc As String
        
        objDoc = "Q:\Temp\FSTemp.xls"
         
    On Error GoTo ErrHandler:
    
    
        If MsgBox("Do you want to continue with the mail merge? If nothing happens, check whether a dialog box has opened in the background, or wait 30 seconds and try again. When the dialog box opens, please press yes to accept the mail merge.", vbYesNo, "Confirm") = vbNo Then
            Exit Sub
        End If
        
        ThisWorkbook.SaveCopyAs Filename:=objDoc
              
        With CreateObject("Q:\Index\Documents\FlatStation Quotation.dotm")
            .MailMerge.Execute
            .Close 0
        End With
         
        Kill objDoc
         
         'Skip error handler
        Exit Sub
              
    ErrHandler:
        Kill objDoc
        MsgBox ("Run-time Error " & Err.Number)
        Exit Sub
         
    End Sub
    The first problem is that the dialog box for MS Word appears in the background, and you have to alt+tab to it to get it to work, which isn't great.

    Secondly, although the user will then press yes to accept the merge, sometimes MS Word just doesn't open up, although the process might be running in the background. This is causing me a real headache because it gets into a loop whereby every time you run the macro, MS Word is missing.

    Any help would be very very much appreciated, as I finally feel that I'm nearly ready to launch the updated programs (7 in all).

  18. #78
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    With CreateObject("Q:\Index\Documents\FlatStation Quotation.dotm") 
         .Application.visible=true
         .MailMerge.Execute 
         .Close 0
     End With
    PS. What's the benefit of the combination 'savecopyas' & 'Kill' ?

  19. #79
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    Hi snb, thanks for that. I'm not entirely sure it works, but I'll keep bashing away in hope of doing it.

    I can't remember why I do the copy and kill. I think it's something to do with maximising compatibility. If I remember, I'll let you know

  20. #80
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    snb, can you think of any way to name the word document automatically, or at least give it a name other than "Letter1"?

    Edit: just remembered why I do the savecopyas, and kill. The problem I had is that when someone is doing the mail merge, they could be on any computer in the building, or any excel document that they're working on could be stored anywhere at all. This makes it impossible to link the word template to the document properly. So, I just made the destination of the file fixed, by using this temp file, thereby preventing any problems with determining the file location. It works brilliantly I do the kill just to clean up afterwards.

    Edit2: Just found this, and I'm wondering if perhaps it can be adapted to work from Excel to Word:

    http://www.vbaexpress.com/kb/getarticle.php?kb_id=122

    I'm yet to look into it properly, but it's food for thought.
    Last edited by dirtychinch; 05-07-2014 at 05:57 AM.

Posting Permissions

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