Advanced Macro(s) required: Inter-program, multitasking macro (word and excel)

[mattdafool]

Hi all,

[Moderator Edit: To all: Please only answer the "Hard Question" Below in this thread. See Excel Folder for answering the first two requests. Than you. SamT]

easy question 1, if one document is saved on p drive can a macro open a file from m-files? (m-files is a doc management system)

easy question 2, if i manualy select a cell in a row (spreadsheet) can the whole row get a nice shade of blue until i click onto another row?

Hard question ,I am a terrible person with this request - I don't know how I would thank anyone enough if this could be solved as described below

I have a theory on what I want but I have had no luck, I am new to any sort of VBA...real new

Documents will be attached as word 2016 however i wish to use between 2013 and 2016, if this is an issue please let me know.

Password to word: 123

Perfect world as below

Someone opens a word template	restricted to form filling only (prefer content control but will accept legacy)
fills out form	[COLOR=rgb(0,0,255)](for purposes of this trial see spreadsheet 'register' tab for test data[/COLOR]
save as in folder	say P drive named "Unregistered QA Forms"
As and when required I open the excel spreadsheet, press the "register next" button to start macro	I dont have one made and if it could A) show how many forms are in folder, well that would be extra great and B) update one form per click
Word document is opened, data in fields copied to the next available row in excel
Then, the CCF referance number that the info populated next to in the spredsheet would be copied to the word doc in the CCF reference box
the word document then saves as into a different folder named "Registered QA Forms"
The file in "unregisiered qa forms" is deleted
The spreadsheet updates by adding a row below with the next sequental CCF reference and saves spreadsheet	manual entering of information must also be allowed so maybe a '"save manual entry" button to prompt new row?

Thankyou to anyone that could help

[SamT]

Please start two new threads in the Excel Forum Folder for Questions 1 and 2.

[macropod]

easy question 1, if one document is saved on p drive can a macro open a file from m-files? (m-files is a doc management system)

That really depends on what your doc management system requires. SharePoint, for example, provides for files to be checked out for usage and checked back in again when finished. See: https://support.office.com/en-us/art...C-C26D79A2506F

easy question 2, if i manualy select a cell in a row (spreadsheet) can the whole row get a nice shade of blue until i click onto another row?

Excel already shades such rows but changing it to something different requires VBA coding and can be resource intensive if there is other cell shading to consider.

Hard question ,I am a terrible person with this request - I don't know how I would thank anyone enough if this could be solved as described below
...
Perfect world as below

Someone opens a word template	restricted to form filling only (prefer content control but will accept legacy)
fills out form	[COLOR=rgb(0,0,255)](for purposes of this trial see spreadsheet 'register' tab for test data[/COLOR]
save as in folder	say P drive named "Unregistered QA Forms"
As and when required I open the excel spreadsheet, press the "register next" button to start macro	I dont have one made and if it could A) show how many forms are in folder, well that would be extra great and B) update one form per click
Word document is opened, data in fields copied to the next available row in excel
Then, the CCF referance number that the info populated next to in the spredsheet would be copied to the word doc in the CCF reference box
the word document then saves as into a different folder named "Registered QA Forms"
The file in "unregisiered qa forms" is deleted
The spreadsheet updates by adding a row below with the next sequental CCF reference and saves spreadsheet	manual entering of information must also be allowed so maybe a '"save manual entry" button to prompt new row?

Your Word document contains a series of formfields. You will find VBA code for extracting such data to an Excel workbook in threads such as:
http://www.vbaexpress.com/forum/show...el-spreadsheet
A simple search would have turn that up.

As for the CCF Reference, that would require a minor tweaking of the code and you will need a formfield to hold that; otherwise the macro code would have to unprotect then re-protect each document. The SaveAs & deletion are further minor tweaks.

[mattdafool]

Thank you Paul

I will do more searching, sorry for wasting time. I find code very difficult to read, maybe if I find what I am looking for it would be better to ask for help to merge into one if it doesn't work properly. Cheers for the links

[macropod]

I will do more searching, sorry for wasting time.

It's not so much a matter of wasting time but pointing out that forums such as this typically have vast amounts of code, etc. that can point you in the right direction. Here's an Excel macro, based on the one in the link I posted, for handling your 'hard' question:

Sub GetFormData()
     ' Note: this code requires a reference to the Word object model,
     ' inserted via Tools|References in the Excel VBA Editor
    Application.ScreenUpdating = False
    Dim wdApp As New Word.Application
    Dim wdDoc As Word.Document
    Dim FmFld As Word.FormField
    Dim strFolder As String, strFile As String
    Dim WkSht As Worksheet, r As Long, c As Long
    Const StrSrcFldr = "C:\Users\Matt\Complaints\UnRegistered QA Forms" 'Path to source folder
    Const StrDstFldr = "C:\Users\Matt\Complaints\Registered QA Forms" 'Path to destination folder
    Set WkSht = ActiveSheet
    r = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
    strFile = Dir(StrSrcFldr & "*.doc", vbNormal)
    While strFile <> ""
        r = r + 1
        Set wdDoc = wdApp.Documents.Open(Filename:=StrSrcFldr & "\" & strFile, _
            AddToRecentFiles:=False, Visible:=False)
        With wdDoc
            c = 0: .FormFields("CCRef").Result = WkSht.Cells(r - 1, 3) + 1
            For Each FmFld In .FormFields
                c = c + 1
                WkSht.Cells(r, c) = FmFld.Result
                MsgBox c & vbTab & FmFld.Result & vbCr & WkSht.Cells(r, c)
            Next
            .SaveAs2 Filename:=StrDstFldr & Split(.Name, ".doc")(0) & ".docx", _
                FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
            .Close SaveChanges:=False
        End With
        'Kill StrSrcFldr & "\" & strFile
        strFile = Dir()
    Wend
    wdApp.Quit
    'ActiveWorkbook.Save
    Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing
    Application.ScreenUpdating = True
End Sub

As coded, the macro assumes your CCF references will be in column 3. That's just because your form's layout makes it easier to work with such an arrangement. Also, since I'm not sure what your CCF reference codes are comprised of, at this stage it's just a simple incremental counter. As mentioned in my previous reply, you'll need to add a text formfield to the document for the CCF reference. I've also left the file deletion code and workbook save code commented-out. That's so you can test the macro without the risk it will delete/save something in error. You will also need to supply the correct paths for the source and destination folders.


Note that the macro only collects data from formfields. Your form has a formfield checkbox for the 'QA Report Required' 'no' response, but a content control for the 'yes' response. Your form also has content control checkboxes for the 'Credit / Replacement Required' responses. You should use either formfields or content controls throughout your document. Using a mix of both can lead to problems, as they were not designed to work together. Personally, I'd be inclined to replace all those checkboxes with dropdown formfields - one for the 'QA Report Required' response and another for the 'Credit / Replacement Required' response - so the user can only make one choice (with out the need for a macro to force an exclusive choice via formfields).

[mattdafool]

Thanks both Sam and Paul.

I appreciate your help in my issue and integrity to keep the standards of this site maintained.

I will perform searches in the future to atleast try find the answer to my questions instead of thinking I am above the rules just because I wanted to save myself time by hoping someone would do what I am capable of.

I hope to hear from you in the future, I will post my 'easy' questions in the excel thread over the next few days after I reword them to clearly describe what I am looking for (after searching of course).

Best wishes