Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    I've built a Word form template that will eventually be used by a number of people across my organization. I'd like to control where the completed documents will be filed. It occurs to me that I could add a "Save" button to the form. A macro behind the button would automatically save every new document based on the template to a specific folder. I feel certain this can be done, but I know very little about writing a macro or using VBA in Word. (I've used it with adult supervision in Access, but not in Word.)

    So far I've created the button with the caption that reads "Save to SharePoint." Then I opened the code builder where it reads:

    Private Sub SharePoint_Click()

    End Sub


    What goes in between? I'll be grateful for any help. Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Have you thought about a naming convention? If you want documents to be saved automatically, you'll have to provide unique names to avoid existing documents from being overwritten. You could include the date and time in the filename, and perhaps the username.

    Keep in mind that users will still be able to use Office button > Save As to save the document in a different location or under a different name.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    Matter of fact, I had thought of both points. I simply failed to mention those details.

    Re: naming convention—Let's assume I want every document filename to include PinnacleAAR. The next element could be the UserName, followed by an incrementing AutoNumber. A typical file name might look like this:

    PinnacleAAR_John Smith_001.docx

    I'd also thought about disabling the Save As... function to force the user to use this macro button. I suppose some of my users might not appreciate the beauty of that approach.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could put code in the Document_New event procedure in the ThisDocument module of the template. This event procedure will be executed each time a new document is created based on the template.

    The tricky part will be to find the next sequence number. You have to loop through the documents in the folder to find the highest number used, then add 1. The code could look like this - substitute the correct path:

    Code:
    Private Sub Document_New()
      ' Modify as needed but keep the trailing backslash
      Const strPath = "H:\Docs\"
      Dim strUser As String
      Dim strFile As String
      Dim intSeq As Integer
      Dim intMax As Integer
      strUser = Environ("Username")
      strFile = Dir(strPath & "PinnacleAAR_" & strUser & "_*.docx")
      Do While Not strFile = ""
    	intSeq = Left(Right(strFile, 8), 3)
    	If intSeq > intMax Then
    	  intMax = intSeq
    	End If
    	strFile = Dir
      Loop
      strFile = strPath & "PinnacleAAR_" & strUser & "_" & Format(intMax + 1, "000") & ".docx"
      ActiveDocument.SaveAs FileName:=strFile
    End Sub

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    Thank you, Hans! I placed the code in the module voila! it works! The file saved directly in the new folder I'd created for it. Beautiful!

    Now a new problem. I ran eagerly to a colleague's cubicle and got him to open the form. He launched a new document based on my template and ticked the radio button to allow the macro to run. The new document displayed his UserName (good), but the form fields don't work. They're all grayed out. Another colleague tried it with the very same results. The form works fine on my machine. Any idea why they don't work on other machines?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The code that I posted would run automatically when the user creates a new file from your template; the user doesn't have to click anything. So you wouldn't need any button.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    Yes, I'm very pleased with the way this code works—sweet! I've found another little problem, however. Please see my edited previous post. Thank you, Hans!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Caesar3' post='794293' date='21-Sep-2009 18:18']Now a new problem. I ran eagerly to a colleague's cubicle and got him to open the form. He launched a new document based on my template and ticked the radio button to allow the macro to run. The new document displayed his UserName (good), but the form fields don't work. They're all grayed out. Another colleague tried it with the very same results. The form works fine on my machine. Any idea why they don't work on other machines?[/quote]
    I don't have Office 2007 but I think that it has to do with security. Your colleagues will have to make sure that the template is trusted.

    Security settings can be viewed/edited as follows:
    - Click the Microsoft Office Button.
    - Click Word Options.
    - Click Trust Center.
    - Click Trust Center Settings.

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    OK...yesterday, this thing worked beautifully. The tip you gave me in your most recent reply allowed my two colleagues to use the form. (We ticked the box to "Trust access to the VBA project object model." We did not change the macro settings. They still say "Disable all macros with notification.") Good so far.

    But today I've copied the template to another directory to make it available to more people. I've made sure the VBA code is in place. (I had to copy and paste it into the code builder in the new location.) I've made no changes to the code. The file path is exactly the same as it was yesterday.

    Yesterday, when any member of my immediate team (myself and two others) launched a new document based on the template, Word immediately created a file in the default location, per code. I've got three files in the directory as proof. Today, nothing happens. I've read thru the code trying to see where this process goes awry, but I don't see the problem.

    One more thing: Word now crashes when I try to use this template. When I try to close a new document, Word tells me it cannot close it (something about it can't read from the source).

    What have I overlooked?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    There should be no need to tick "Trust access to the VBA project object model" here - that is only necessary if you use code to modify other code.

    I think you'll have to make the folder containing the template a trusted location - see Create, remove, or change a trusted location for your files.

Posting Permissions

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