Attention: Please take a moment to consider our terms and conditions before posting.

Microsoft Excel Question

At present my job (in Pensions) partly consists of me creating Retirement Packs and posting them out for Members

The creation of Retirement Packs can take about 5 / 10-mins to produce (manually entering the Members Personal Details and Retirement Details in the relevant fields throughout the template Document). At the moment its not taking too much time as my Department is new (consists of just myself), yet is part of a pilot project which when it properly goes live will seriously increase not just my workload but will see me employing additional hands to help with the new workload.

Basically at present I have to manually copy and paste the data from the Database that we use on to the Microsoft Word template before sending.

Here's the help (advise) I need... I've found a way (a very quick way) to export all the Database data into a single Microsoft Excel Spreadsheet (which remains on a hidden worksheet). Then on the live sheet I've managed to create something (using VLOOKUPS) where I can enter an individual Reference Number and pull that Member's information without having to hunt for it OR having to manually copy and paste.

What I want to do next is to be able to click a button in the Spreadsheet which automatically loads the relevant Microsoft Word Template and automatically runs a mail merge which pulls the selected Member Data into the relevant fields (without having to do those steps manually).

Can this be done (I'm guessing a lot of VBA will be required)... Or is there another similar approach that can be taken that I've not thought of?
«1

Comments

  • Thread of the year contender ...
  • DA9DA9
    edited January 2017
    Just sent your question to my other half, she's an IT trainer and Microsoft Professional, will let you know
  • What kind of database is it? Would have thought it'd just be easier to create a form straight off the database. That way you don't risk buggering up the data formats in your multiple extract processes.
  • Well, least I now know I am not the only Addick involved in Pensions.

    I would use a Macro but I don't really remember how to set one up, I would have thought a mail merge would be interlinked with your database.

  • What's the database? Definitely sounds like you want a form - if it's Access then it's relatively trivial to create one (you can teach yourself in half a day).

    If it's not, then you might need someone to do some development work for you, which would be a whole different kettle of fish
  • Direct from database is the neatest way, but you need to know a bit more info and probably best to get your dbas to do it.

    To be honest though, all that's needed is to make sure your source is right on the Word doc. Not sure a macro will save much time.
  • Sponsored links:


  • Having said that, your dbas might know a way of getting the data out directly in the format you want. Find who runs the database and chat with them.
  • Ctrl Alt Del
  • I would sack your manager for not getting this automated already. No one creates a process by manually entering data which already exists on a database. It's standard automated mail merge processing in any reasonably sized organisation. Sounds like you don't have an IT system to automate.
  • I can't help on your question. But the way you are doing it at the moment, with a manual input of a reference number (which could easily be incorrectly entered) sounds like a recipe for sending someone's personal data to someone else by mistake. You do have other built in checks presumably? The belt and the braces......
  • Its just standard systems, extremely bog standard that are used, the idea wouldnt be possible at all if there wasnt an Excel Export function on the current DB.

    But at the moment the risk that the manual input creates is huge, other checks are thankfully in place to ensure this doesnt happen.

    When I worked in Contract Production whilst in the Recruitment industry though there was a programme like what I want to create (its where I've got my idea from)... Hell even if I get it to work I swear I'm going to struggle to get sign off past the type of people who wont be keen on the idea because they didnt think it up themselves!!
  • cant you run this from your database rather than Excel?
  • aliwibble said:

    What kind of database is it? Would have thought it'd just be easier to create a form straight off the database. That way you don't risk buggering up the data formats in your multiple extract processes.

    Agree 100%. Easy to do for someone who knows a bit about what they are doing.
  • I would sack your manager for not getting this automated already. No one creates a process by manually entering data which already exists on a database. It's standard automated mail merge processing in any reasonably sized organisation. Sounds like you don't have an IT system to automate.

    No one. Except governemnt departments.
  • Sponsored links:


  • I agree with @cantersaddick. I would write some VBA code to do it. It doesn't sound very difficult to do. If you google, you will find some code that does the job, but you may need a bit of expertise to do it. I've got staff that can do it, if it takes an hour or less you can have it for free. Inbox me with clear instructions if you want more help.
  • Perhaps it might be worth starting with some VBA code. Then perhaps try the Excel Export function. You could always fall back on your dbas and see if that helps.
    Alternatively go with Dave Mehmets tried and trusted formula, or get yourself a nice little diary.
  • Trainspotters and Excel Geeks - The Charlton Fans' Story
  • olster said:
    Stack overflow and MrExcel are very good places to start.

    As previously said by others you'll need a little programming knowledge for this but VBA is one of the easier ones to get in to.

    If you don't already have them then you'll need to set up your word document as templates with merge field references. Google mail merge with your version of Word for a how to guide if you're not sure. This sound simple but there are techniques to avoid layout corrupting from say different length of addresses.

    Make sure your Excel data extract contains headings and given them meaningful names...it makes life much easier and the mail merge template easier to create.

    You can then start on the VBA code to open the ms word application and create a new doc based on the template.

    Once you have it up and running make sure you run it alongside your manual process or double check your created letters as much as possible. As much praise you'll get for saving time, you'll get in double the amount of shit if it goes wrong. I love automation but I see too many people just assuming it works and not checking the outcome.

    Couple of other thing....add as many comments to the code as you need to explain the program. Plus run the tool on someone else's PC - a developers favourite excuse is "well it worked on my PC".

    Good luck
  • Not sure I fully understand what you are attempting but Powerpoint can combine Excel and Word documents.
  • Why not give Katrien a call? I'm pretty sure she knows exactly how to merge customer data into an error-free mail out.
  • I'm not completely sure what you're trying do to but from my understanding the whole process can be simplified by following these steps

    Based on you having a MS Access DB containing all the data you don't even need to manually export anything

    1. Link the data from your MS Access database into a blank MS Excel spreadsheet using Data > From Other Sources. Once linked you can right click and 'refresh' every time your database updates without you having to do anything. In fact you can even set this to refresh every time you open the spreadsheet so the data is always upto date when you open it. This will mean your data will always be in MS Excel but will also mean you don't need to copy and paste from Access and also won't need to use the vlookups, roughly how many records are you managing?

    2. Assuming you have a record in each row on your spreadsheet i'm pretty sure you can then set a mail merge up using the following process https://support.office.com/en-gb/article/Mail-merge-using-an-Excel-spreadsheet-858c7d7f-5cc0-4ba1-9a7b-0a948fa3d7d3

    3. Record step 2 using a macro and then create a button on your toolbar which then runs this macro anytime.
  • I'm not completely sure what you're trying do to but from my understanding the whole process can be simplified by following these steps

    Based on you having a MS Access DB containing all the data you don't even need to manually export anything

    1. Link the data from your MS Access database into a blank MS Excel spreadsheet using Data > From Other Sources. Once linked you can right click and 'refresh' every time your database updates without you having to do anything. In fact you can even set this to refresh every time you open the spreadsheet so the data is always upto date when you open it. This will mean your data will always be in MS Excel but will also mean you don't need to copy and paste from Access and also won't need to use the vlookups, roughly how many records are you managing?

    2. Assuming you have a record in each row on your spreadsheet i'm pretty sure you can then set a mail merge up using the following process https://support.office.com/en-gb/article/Mail-merge-using-an-Excel-spreadsheet-858c7d7f-5cc0-4ba1-9a7b-0a948fa3d7d3

    3. Record step 2 using a macro and then create a button on your toolbar which then runs this macro anytime.

    Can I say that the phrase "I'm not completely sure what you're trying do to but from my understanding the whole process can be simplified by following these steps..." is one of the best things I've seen for a very long time.
  • Chizz said:

    I'm not completely sure what you're trying do to but from my understanding the whole process can be simplified by following these steps

    Based on you having a MS Access DB containing all the data you don't even need to manually export anything

    1. Link the data from your MS Access database into a blank MS Excel spreadsheet using Data > From Other Sources. Once linked you can right click and 'refresh' every time your database updates without you having to do anything. In fact you can even set this to refresh every time you open the spreadsheet so the data is always upto date when you open it. This will mean your data will always be in MS Excel but will also mean you don't need to copy and paste from Access and also won't need to use the vlookups, roughly how many records are you managing?

    2. Assuming you have a record in each row on your spreadsheet i'm pretty sure you can then set a mail merge up using the following process https://support.office.com/en-gb/article/Mail-merge-using-an-Excel-spreadsheet-858c7d7f-5cc0-4ba1-9a7b-0a948fa3d7d3

    3. Record step 2 using a macro and then create a button on your toolbar which then runs this macro anytime.

    Can I say that the phrase "I'm not completely sure what you're trying do to but from my understanding the whole process can be simplified by following these steps..." is one of the best things I've seen for a very long time.
    OK I probably could have worded it better but my steps are based upon my interpretation of what I think are the user's requirements. Of course if I've misinterpreted the requirements then my steps are irrelevant. I'm far better with these things when I have the data before me.
  • aliwibble said:

    What kind of database is it? Would have thought it'd just be easier to create a form straight off the database. That way you don't risk buggering up the data formats in your multiple extract processes.

    I agree this looks the best option.
Sign In or Register to comment.

Roland Out Forever!