+ Reply to Thread
Results 1 to 5 of 5

Excel Mail Merge Macro stops working when Excel & Word files are moved to another computer

  1. #1
    Registered User
    Join Date
    12-07-2016
    Location
    CDMX, Mexico
    MS-Off Ver
    2013
    Posts
    2

    Question Excel Mail Merge Macro stops working when Excel & Word files are moved to another computer

    I have made a macro that populates a Word template based on information reviewed on an Excel Workbook, and after the review has finished successfully I generated the stated Word report. This macro works on my computer, but this process is done by various colleagues that have no idea about Mail Merge function or VBA.
    I want them to download the excel file and word template into a file folder on their personal computers and have the macro work without any further assistance. Nevertheless the SQL connection gets lost every time these two files are in another computer.
    Is there a way to solve through coding?

  2. #2
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Excel Mail Merge Macro stops working when Excel & Word files are moved to another comp

    Welcome to the forum!

    Without seeing code or the files, I don't see how we could help.

    This may help. If you want sample files, see: https://www.dropbox.com/s/fx4r06xq4m...sDocs.zip?dl=0
    Please Login or Register  to view this content.
    Last edited by Kenneth Hobson; 12-07-2016 at 07:46 PM.

  3. #3
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Excel Mail Merge Macro stops working when Excel & Word files are moved to another comp

    Quote Originally Posted by saledesma View Post
    SQL connection gets lost every time these two files are in another computer.
    Is there a way to solve through coding?
    Yes, but the solution depends on whether the merge is being executed from Word directly or from Word via automation from Excel.

    As Ken says, we'd need to see your code.
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  4. #4
    Registered User
    Join Date
    12-07-2016
    Location
    CDMX, Mexico
    MS-Off Ver
    2013
    Posts
    2

    Re: Excel Mail Merge Macro stops working when Excel & Word files are moved to another comp

    I'll do a sample file with the part that contains the mail merge and will try to post it here, since web sites like dropbox are fileterd out.


    Found this code and made it work for my situation.
    Please Login or Register  to view this content.
    Last edited by saledesma; 12-08-2016 at 03:11 PM.

  5. #5
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Excel Mail Merge Macro stops working when Excel & Word files are moved to another comp

    When running a merge from Excel, you need to suppress the SQL prompt when opening the mailmerge main document; otherwise the merge will stall at that point. However, since you're also supplying the SQL string etc. in the code, you don't need to have the document connected to the data source anyway. Try the following code. Note the use of:
    .DisplayAlerts = wdAlertsNone
    That both suppresses the SQL prompt and disconnects the document from the data source. Using:
    .MainDocumentType = wdFormLetters
    and the other code you already had remedies that.
    Please Login or Register  to view this content.
    Note that the code you posted has an undefined variable 'sXLSPathFile'. Your use of sSQLModel and sSQLWhere seem strange, too. I can't see why you wouldn't simply include the entire final string in the 'SQLStatement' definition and eliminate both variables and the circumlocution involving them.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Word Macro that will mail merge to excel?
    By billisnice in forum Word Programming / VBA / Macros
    Replies: 1
    Last Post: 05-08-2015, 07:24 AM
  2. Replies: 0
    Last Post: 01-05-2015, 07:35 AM
  3. Mail merge: recorded macro in Word, could use some help to integrate it in Excel
    By dourpil in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-23-2014, 06:19 AM
  4. Mail merge from Excel to Word, moving the files breaks links?
    By BoldCreature in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-10-2014, 10:46 AM
  5. Needing a Macro to active word mail merge from excel
    By AzhureSunsoar in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-06-2013, 03:35 PM
  6. [SOLVED] Excel VB Macro to Word Mail Merge to PDF.
    By Max_Taylor in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-10-2012, 11:12 AM
  7. Mail Merge into Word doc from Excel file not working?
    By danny2000 in forum Word Formatting & General
    Replies: 3
    Last Post: 05-30-2010, 05:19 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1