Consulting

Page 3 of 5 FirstFirst 1 2 3 4 5 LastLast
Results 41 to 60 of 82

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

  1. #41
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    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!
    _______________________________________________
    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

  2. #42
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,844
    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)
    Please take the time to read the Forum FAQ

  3. #43
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,819
    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 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

  4. #44
    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.

  5. #45
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    In MailMerge, add a variable:
    Dim oMailMergeDoc As Word.Document
    instead of appWord.Documents.Add yada yada
    Set oMailMergeDoc = appWord.Documents.Add (yada yada)
    (You'll need to use parens when setting your document object to the result of the documents.add function)

    now you have your document variable populated with the document you're about to tell to perform a mailmerge. Use the document variable to do the save, not the appWord.ActiveDocument.

    Change your MailMergeToExcel so it can accept a document parameter, like so:
    Sub MailMergeToExcel (oDoc As Word.Document)

    within the routine, replace the use of ActiveDocument with your passed document object variable for the actual mail merge.

    Now back to MailMerge, add the following call to your newly parameterized sub...

    MailMergeToExcel oMailMergeDoc

    All the code should be in
    excel, including the stuff you were asking if you should dump (that's the mail merge code, after all!)

    step through the code and see what happens.

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

    Thanks, once again. I've amended the code to what I think you're telling me (a few terms in there that I don't quite understand, but I've done my best to make sense of).

    The fGetApp function doesn't seem to do anything, as far as I can tell.

    At the bit that's commented "'Create new quotation for template" the word document opens.

    As far as I can tell, at this point I cannot choose the source document (I don't know whether it's just using the active document or not), and from that point nothing else happens. I think I may have put the commands for "MailMergeToExcel oMailMergeDoc" in wrong though, as I just copied that straight in...

    Here's my code, anyway:

    
    
     '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
       Dim oMailMergeDoc As Word.Document
       
       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
        Set oMailMergeDoc = appWord.Documents.Add("Q:\AirMaster\AirMaster Quotation.dotm")
        appWord.Visible = True
    
    
        With appWord
            .Visible = True
            .ActiveDocument.SaveAs2 Filename:="Prod Quote_xxAMxHRV_ProjName " & Format(Date, "ddmmyyyy") & ".docx"
        End With
        
        MailMergeToExcel oMailMergeDoc
    
    
        Application.ScreenUpdating = False
        Application.CutCopyMode = False
        Application.DisplayAlerts = True
        
    End Sub
    Sub MailMergeToExcel(oDoc As Word.Document)
         
        Dim sConnection As String
        Dim strSourcePath As String
         
        strSourcePath = oMailMergeDoc
         
        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

  7. #47
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    The ActiveDocument.MailMerge line of code, with all the parameters I did line breaks for you on... Use the document variable you are now passing to the sub routine "MailMergeToExcel"

    oDoc.MailMerge.OpenDataSource etcetc

    you're close.

    fGetApp doesn't need to do anything if Word is already open... It just provides a "handle" to the word application in order to create documents and run mail merges. Providing that handle doesn't take much time if word is already open.

  8. #48
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    Also, you're clearly not using option explicit at the top of your modules. Put that at the top of each code module (there is a setting to do this automatically) under tools/options.

    Option Explicit will help you more than any forum. That will help you see other errors in your code... But you need to step through. Alternatively, if you think you're having trouble because of porting to excel, copy the mail merge code back into word, and try it out. Since it has a parameter, you'll either need to create a parameter-less routine that just contains one line:
    MailMergeToExcel ActiveDocument
    Or you could type that in the immediate window to test (all in Word VBA instead if Excel VBA.

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

    I turned on "Require Variable Declaration" but nothing has actually happened. I think that was what you were meaning?

    It still runs through, but there is still no source document, so it's getting to

        If strSourcePath = "" Then        Exit Sub
        End If
    And exiting.

    This is my code now:

    '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
    Dim oMailMergeDoc As Word.Document

    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
    Set oMailMergeDoc = appWord.Documents.Add("Q:\AirMaster\AirMaster Quotation.dotm")
    appWord.Visible = True


    With appWord
    .Visible = True
    .ActiveDocument.SaveAs2 Filename:="Prod Quote_xxAMxHRV_ProjName " & Format(Date, "ddmmyyyy") & ".docx"
    End With

    MailMergeToExcel oMailMergeDoc


    Application.ScreenUpdating = False
    Application.CutCopyMode = False
    Application.DisplayAlerts = True

    End Sub
    Sub MailMergeToExcel(oDoc As Word.Document)

    Dim sConnection As String
    Dim strSourcePath As String

    strSourcePath = oMailMergeDoc

    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
    oMailMergeDoc.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

  10. #50
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    Here are some modifications, and clarifications... try this out. This is the ENTIRETY of my module in excel -- notice the Option Explicit at the top...
    [vba]
    Option Explicit
    '-----------------------------------------------------------------------------------------------------
    Public Sub MailMergeMain()

    Dim appWord As Word.Application
    Dim oMailMergeDoc As Word.Document
    Dim strSourcePath As String

    'verify no mis-clicks? Perhaps this is where you should get the path instead...
    strSourcePath = fGetFilePath
    'if blank, user hit cancel... double-check? Or just comment out the msgbox logic and exit
    If strSourcePath = "" Then
    If MsgBox("Do you want to continue with the mail merge?", vbYesNo, "Confirm") = vbNo Then
    Exit Sub
    End If
    End If

    'Ensures workbook saved
    ThisWorkbook.Save

    'Create new quotation for template
    Set appWord = fGetApp
    'make it visible
    appWord.Visible = True
    'create a new document based on a hard coded path? Should you offer a choice here?
    Set oMailMergeDoc = appWord.Documents.Add("Q:\AirMaster\AirMaster Quotation.dotm")
    'Save the document with a particular name and path
    oMailMergeDoc.SaveAs2 Filename:="Prod Quote_xxAMxHRV_ProjName " & Format(Date, "ddmmyyyy") & ".docx"

    'run the mail merge
    MailMergeToExcel oMailMergeDoc, strSourcePath

    'why is this here?
    Application.ScreenUpdating = False
    Application.CutCopyMode = False
    Application.DisplayAlerts = True

    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
    '-----------------------------------------------------------------------------------------------------
    '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
    '-----------------------------------------------------------------------------------------------------
    'main mail merge routine
    Public Sub MailMergeToExcel(oDoc As Word.Document, Optional strSourcePath As String)

    Dim sConnection As String

    'if we didn't pass in a value, then ask the user
    If strSourcePath = "" Then
    strSourcePath = fGetFilePath
    End If

    'if still no info, then indicate that we've cancelled
    If strSourcePath = "" Then
    'need to inform the user, since an emptry string means nothing was chosen from fGetFilePath
    MsgBox "Action Cancelled", vbInformation, "MailMergeToExcel"
    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
    oDoc.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]

  11. #51
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    I should have clarified... requiring variable declaration only adds option explicit automatically to the top of *new* code modules... not existing ones.

  12. #52
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    Wow, that works perfectly Frosty, thank you very much I'll have a look through the changes that you made.

    Ah right, that makes sense now. I was waiting for some sort of magic to happen and all these option explicit tags to appear everywhere

  13. #53
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    With regards to you comment in the code, I don't want the users to be able to select the template - each dept. has their own quotation program so I'll just make it fixed. The main reason why i'm doing this is to stop them using their on style of quotes, hah!

  14. #54
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    Well, in that case, you should encapsulate that with a hard code, so that you can copy paste updates to the code to each of your different locations. I don't know how many departments you have, but this would be a better structure...
    [vba]
    '-----------------------------------------------------------------------------------------------------
    Public Sub MailMergeDeptA()
    MailMergeMain "Q:\AirMaster\AirMaster Quotation.dotm"
    End Sub
    '-----------------------------------------------------------------------------------------------------
    Public Sub MailMergeDeptB()
    MailMergeMain "Q:\someotherstyle\otherstyle.dotm"
    End Sub
    '-----------------------------------------------------------------------------------------------------
    Public Sub MailMergeMain(sQuotationDocPath As String)

    Dim appWord As Word.Application
    Dim oMailMergeDoc As Word.Document
    Dim strSourcePath As String

    'verify no mis-clicks? Perhaps this is where you should get the path instead...
    strSourcePath = fGetFilePath
    'if blank, user hit cancel... double-check? Or just comment out the msgbox logic and exit
    If strSourcePath = "" Then
    If MsgBox("Do you want to continue with the mail merge?", vbYesNo, "Confirm") = vbNo Then
    Exit Sub
    End If
    End If

    'Ensures workbook saved
    ThisWorkbook.Save

    'Create new quotation for template
    Set appWord = fGetApp
    'make it visible
    appWord.Visible = True
    'create a new document based on a hard coded path? Should you offer a choice here?
    Set oMailMergeDoc = appWord.Documents.Add(sQuotationDocPath)
    'Save the document with a particular name and path
    oMailMergeDoc.SaveAs2 Filename:="Prod Quote_xxAMxHRV_ProjName " & Format(Date, "ddmmyyyy") & ".docx"

    'run the mail merge
    MailMergeToExcel oMailMergeDoc, strSourcePath

    'why is this here?
    Application.ScreenUpdating = False
    Application.CutCopyMode = False
    Application.DisplayAlerts = True

    End Sub
    [/vba]

  15. #55
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    And then I would be really really hesitant to deploy this to multiple departments as .docm and excel macro-enabled spreadsheets (.xlsm) ... what you're doing is deploying spreadsheet documents with whatever version of the macro when the user saved the document. This can create a nightmare.

    What you should do is have a single excel addin, which contains your code... and is deployed to each of your end-user's machine... or only accessible in a single template, which everyone opens in order to have access to the code, and you have on the network as a read-only copy of your own code.

    The line "ThisWorkbook.Save" concerns me, because it means people are saving the macros in these individual spreadsheets... which means they could easily be using outdated code. For example, let's say your network engineers take down the Q:\ drive and decide to call it the X:\ drive. You need to update your code... so you do. But a number of your users are in-process on a bunch of spreadsheets with macros in them... which no longer work.

  16. #56
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,819
    Location
    Quote Originally Posted by dirtychinch View Post
    As I said before, I'd requested that the post on that forum be removed before I even joined this one.
    Please don't ever request that a post be removed from this forum.
    Remember To Do the Following....
    Use 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

  17. #57
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    Yeah, although to be fair... even the best-intentioned person has an issue getting around the rule of linking to other cross-posts, but not being able to do internet links until a certain post-count threshold is met. From what I see, dirty chinch has brought a good attitude and willingness to learn, as well as having made an effort to post to an appropriate location after initially posting to the wrong place. He *could* have indicated in his original post that he also asked this question at another forum, but I think everyone's on the same page at this point.

  18. #58
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    Quote Originally Posted by Frosty View Post
    Well, in that case, you should encapsulate that with a hard code, so that you can copy paste updates to the code to each of your different locations. I don't know how many departments you have, but this would be a better structure...
    [vba]
    '-----------------------------------------------------------------------------------------------------
    Public Sub MailMergeDeptA()
    MailMergeMain "Q:\AirMaster\AirMaster Quotation.dotm"
    End Sub
    '-----------------------------------------------------------------------------------------------------
    Public Sub MailMergeDeptB()
    MailMergeMain "Q:\someotherstyle\otherstyle.dotm"
    End Sub
    '-----------------------------------------------------------------------------------------------------
    Public Sub MailMergeMain(sQuotationDocPath As String)

    Dim appWord As Word.Application
    Dim oMailMergeDoc As Word.Document
    Dim strSourcePath As String

    'verify no mis-clicks? Perhaps this is where you should get the path instead...
    strSourcePath = fGetFilePath
    'if blank, user hit cancel... double-check? Or just comment out the msgbox logic and exit
    If strSourcePath = "" Then
    If MsgBox("Do you want to continue with the mail merge?", vbYesNo, "Confirm") = vbNo Then
    Exit Sub
    End If
    End If

    'Ensures workbook saved
    ThisWorkbook.Save

    'Create new quotation for template
    Set appWord = fGetApp
    'make it visible
    appWord.Visible = True
    'create a new document based on a hard coded path? Should you offer a choice here?
    Set oMailMergeDoc = appWord.Documents.Add(sQuotationDocPath)
    'Save the document with a particular name and path
    oMailMergeDoc.SaveAs2 Filename:="Prod Quote_xxAMxHRV_ProjName " & Format(Date, "ddmmyyyy") & ".docx"

    'run the mail merge
    MailMergeToExcel oMailMergeDoc, strSourcePath

    'why is this here?
    Application.ScreenUpdating = False
    Application.CutCopyMode = False
    Application.DisplayAlerts = True

    End Sub
    [/vba]
    Quote Originally Posted by Frosty View Post
    And then I would be really really hesitant to deploy this to multiple departments as .docm and excel macro-enabled spreadsheets (.xlsm) ... what you're doing is deploying spreadsheet documents with whatever version of the macro when the user saved the document. This can create a nightmare.

    What you should do is have a single excel addin, which contains your code... and is deployed to each of your end-user's machine... or only accessible in a single template, which everyone opens in order to have access to the code, and you have on the network as a read-only copy of your own code.

    The line "ThisWorkbook.Save" concerns me, because it means people are saving the macros in these individual spreadsheets... which means they could easily be using outdated code. For example, let's say your network engineers take down the Q:\ drive and decide to call it the X:\ drive. You need to update your code... so you do. But a number of your users are in-process on a bunch of spreadsheets with macros in them... which no longer work.
    Hi Frosty. As always, thank you very much.

    When I say it's going to be deployed to multiple departments, it won't be with the same program, although the structure would be the same. I don't want to have to put an add in on each person's computer, and they can't necessarily be trusted to do it themselves. When you say deployed to their machines though, what exactly are we talking about?

    With regards to "ThisWorkbook.Save" they could indeed be using outdated code. However, the way I see it is that each time they do a quote, if the program is updated then they just continue to use that program (probably not even noticing the updates). They would also always be using the template document on the network; never a local copy. I do understand your concern, but there will only be one operator for the program (perhaps two) and the company isn't very big so it would be easy for me to get the word to them to hold off while I adjust the code.

    I mean, is there really an alternative?

    Quote Originally Posted by Aussiebear View Post
    Please don't ever request that a post be removed from this forum.
    Don't worry - I won't! I had a hard enough time finding any decent information about mail merging using VBA. This thread would have been a gold mine for me last week!!

    Quote Originally Posted by Frosty View Post
    Yeah, although to be fair... even the best-intentioned person has an issue getting around the rule of linking to other cross-posts, but not being able to do internet links until a certain post-count threshold is met. From what I see, dirty chinch has brought a good attitude and willingness to learn, as well as having made an effort to post to an appropriate location after initially posting to the wrong place. He *could* have indicated in his original post that he also asked this question at another forum, but I think everyone's on the same page at this point.
    Thank you for sticking up for me Indeed, I realised at some point that perhaps I wasn't supposed to be coding in excel. The error was mine, and I had no idea that the forums were interconnected.

    Anyway, I shan't be cross posting again, don't worry!!

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

    I'm having a problem; once the document has been created, it firstly doesn't toggle the preview (not too big a deal), but also doesn't actually end the mail merge, which seems to slow everything down. Would it be possible to include this (both if possible) in the code somehow? I'm guessing that may have to be done in word.

    Thanks,

    dirtychinchilla

  20. #60
    VBAX Regular
    Joined
    Feb 2014
    Location
    Ashford, Surrey, UK
    Posts
    64
    Location
    Also, would it then be possible to sever the link between the source document and the word document. The reason why I ask is that every time you open the document after it has completed the mail merge, it asks if you want to update etc. I just want to sever this tie and tell the operators that if they change the quote, just press the button again.

Posting Permissions

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