I've done excel sheets to excel sheets before, but I have a requirement to generate a MS word defect record sheet, and copy certain data fields from it onto a excel based tracking register tool. So, multiple defect sheets and one register with many rows. Is that possible?
The alternative I thought about would be to have the record sheet in Excel initially, then once filled copy the data into a separate register sheet, (fairly easy) and automatically generate a Word file for on going work after. (?? no clue about that bit)
I've got enough VBA skills to pinch bits of code off the internet and modify them to make them function the way I want, but don't know the basic functions I could use to do this.
Cheers.
Have you tried the Macro record function. Do what you want once then edit the code to be repetitive.
It sounds like the kind of Knime could do.
It’s free so might be worth having a look/play.
If you used an excel sheet for the initial form, once you’ve transferred the data to the register sheet, you could use mail merge to create the word doc if you need one.
Sounds more like a job for an Access database, form to capture the data, database to hold it, report to output it?
(My knowledge of databases is around 15 years out of date)
It is possible using VBA but it tends to be a pain. For the Word document you really want it to be a 'form' so that the text is in blocks that can easily be identified and are always called the same thing. You can even put some validation on the blocks to numbers are always numbers, dates are always dates etc. You then have VBA in the Excel sheet that opens the Word doc and goes through the form fields and copies them into the Excel sheet. The code looks a little like:
Dim objFF As Word.FormField
'objDoc is your Word file
For Each objFF In objDoc.FormFields
debug.print objFF.name
debug.print objFF.result
next objFF
or something like that. You get the idea. The VBA code is in the Excel file. This is better done by an Access db probably but it's quite nice for people to take a Word template and then send it in rather than everyone having to know where the db is and how to fill it in so it's not a bad idea
edit: remember to lock the Word form so people can't edit it. That's the biggest pain in the arse. I also tend to put a version number in the properties so if you add form fields later you can identify which version of the form you are importing
Thanks Gents, agreed Access would be the tool, but it's really not used here. I suspect it may not even be universally supplied as part of the standard Office package even, which would cause issues with my multiple users.
leffeboy, I like the idea of using forms, I'll investigate that. I've also been thinking about doing it the other way, replicating the record sheet in Excel, then once it's filled copying and pasting that range of cells into a freshly launched blank Word doc and also the data to a separate register sheet in the same workbook.
Cheers.
excel then to word, or access or even better a proper database with SQL..
damn accountants ;0)
leffeboy, I like the idea of using forms, I’ll investigate that.
Yep, it works well. I've been down the excel 'form' route asawell and that also works but again you have to lock it so people can't mess with it as they always will rather than work out what they are meant to do. Access generally is part of MS Office these days but if you have a mix of office versions it can be messy. Not too bad since 2013 though
Forms is good for data capture but not gonna generate a word doc.
Another idea is to web scrape the word doc. Have never done it myself but seen it done.
Or if you have 365 use ms forms and then an ms flow to generate an email.