Forum Discussion

Kevin_McElvaney's avatar
Kevin_McElvaney
Copper Contributor
Oct 09, 2023

Repetitive form filling

hello asking if there is a solution. Daily I have to update 9 to 11 work permits. The list of permits Title, date and number are in an Excel spreadsheet. The permits are Word docs, stored in a folder for that day. I have to open doc, change all 6 dates, renew the permit number, save it into next days folder under its new number, then print it. I have built a Macro to do this task, but I have to edit the macro each time I run it for each permit. Any ideas, I'm not an IT guru, but have self taught much. I have e feeling VB might be the answer, if it is possible Kevin
  • Doug_Robbins_Word_MVP's avatar
    Doug_Robbins_Word_MVP
    MVP
    Oct 09, 2023

    Kevin_McElvaney If you post back with the code of the macro, we should be able to tell you how to modify it (probably by the use of some InputBox commands) so that you will be asked for the information from the document interface, rather than having to modify the macro.

    • Kevin_McElvaney's avatar
      Kevin_McElvaney
      Copper Contributor
      Oct 10, 2023
      Thank you for the prompt reply, below is the Macro

      Sub Macro1()
      '
      ' Macro1 Macro
      ' permit change
      '
      Selection.Find.ClearFormatting
      Selection.Find.Replacement.ClearFormatting
      With Selection.Find
      .Text = "GP1310"
      .Replacement.Text = "GP1319"
      .Forward = True
      .Wrap = wdFindContinue
      .Format = False
      .MatchCase = False
      .MatchWholeWord = False
      .MatchWildcards = False
      .MatchSoundsLike = False
      .MatchAllWordForms = False
      End With
      Selection.Find.Execute Replace:=wdReplaceAll
      With Selection.Find
      .Text = "09-10-23"
      .Replacement.Text = "10-10-23"
      .Forward = True
      .Wrap = wdFindContinue
      .Format = False
      .MatchCase = False
      .MatchWholeWord = False
      .MatchWildcards = False
      .MatchSoundsLike = False
      .MatchAllWordForms = False
      End With
      Selection.Find.Execute Replace:=wdReplaceAll
      ChangeFileOpenDirectory _
      "C:\Users\Kevin.McElvaney\Desktop\Permits\10 Oct\10.10.23\"
      ActiveDocument.SaveAs2 FileName:= _
      "Boilers - General activities Scaffold (erection)GP1319.docx", _
      FileFormat:=wdFormatXMLDocument, LockComments:=False, Password:="", _
      AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
      EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
      :=False, SaveAsAOCELetter:=False, CompatibilityMode:=15
      Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
      wdPrintDocumentWithMarkup, Copies:=1, Pages:="", PageType:= _
      wdPrintAllPages, Collate:=True, Background:=True, PrintToFile:=False, _
      PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
      PrintZoomPaperHeight:=0
      ActiveWindow.Close
      Application.Quit
      End Sub

Resources