PDA

View Full Version : [SOLVED:] Save document using field values



Dray
10-23-2017, 10:29 AM
We have a problem with people not saving documents with the proper names (big surprise right?), so I am trying to come up with a macro that will save the document with the name of the file that we need it as. I've done some macro work, but this one is escaping me somewhat. So here is what I need:

I have a word document that has specific fields that the users will input data into. Three of the fields are for Product, Date, and Time (we use 24hr time). We want the file name to include these three fields in the filename and, if possible, save it to a specific folder (might be asking too much to save it to a Google share drive?). We do not have desktops, so we do not have HDDs to save to. We each have our own "slice" of the share drive that is ours, or we can save things to the share drive so that others can get to it. Can I add a button at the bottom of the form that can be used to perform this function for the user to make it as easy as possible? This document is a "template" that we made, so we would prefer that the users are not able to save the document, only perform a SaveAs. We are also looking to save it as a PDF, which I believe is a FileFormat:=wdFormatPDF command?

Another question would be if we want these three fields added to the name of the document, can that be done? For example, the name of the file is "working.doc". We would want it to say "working 10-23-17 1030.doc". Would that require another field for "working"?

Any help would be greatly appreciated!

macropod
10-23-2017, 03:25 PM
Assuming your fields are formfields with the bookmark names, Product, Date, and Time, you could use code like:

Sub Demo()
Dim StrFlNm As String
With ActiveDocument
StrFlNm = .Bookmarks("Product").Range.Text & _
Format(.Bookmarks("Date").Range.Text, "MM-DD-YY") & _
" " & Format(.Bookmarks("Time").Range.Text, "HHMM")
.SaveAs FileName:=StrFlNm & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
.SaveAs FileName:=StrFlNm & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
End With
End Sub
Note that no path is specified; you'll need to supply that.

Dray
10-24-2017, 05:14 AM
Note that no path is specified; you'll need to supply that.

Path being where Filename is, correct?

macropod
10-24-2017, 05:25 AM
You need to insert the path immediately before StrFlNm. For example:
FileName:="C:\Users\" & Environ("Username") & "\Documents\" & StrFlNm
to save the file in the user's 'Documents' folder.

Dray
10-24-2017, 05:33 AM
If this needed to be a share drive, there should be no problem with that as long as it's a mapped drive, correct?

I've tried my C:\ drive and share drive, but not for sure what I am doing wrong with this:


Sub Demo()
Dim StrFlNm As String
With ActiveDocument
StrFlNm = .Bookmarks("Product").Range.Text & _
Format(.Bookmarks("Date").Range.Text, "MM-DD-YY") & _
" " & Format(.Bookmarks("Time").Range.Text, "HHMM")
.SaveAs FileName:="C:\Users\Public\Documents\" & StrFlNm & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
.SaveAs FileName:="C:\Users\Public\Documents\" & StrFlNm & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
End With
End Sub

macropod
10-24-2017, 05:49 AM
Do you have write access to the drives & folders concerned?

Dray
10-24-2017, 06:13 AM
Yes, all users have R/W access to the drive we want this file saved to. I've done a macro in Excel that saves the spreadsheet as the contents of a certain cell, in this particular folder, and it works fine.

macropod
10-24-2017, 01:07 PM
Check that you have a valid filename in StrFlNm (e.g. Msgbox StrFlNm) after you've populated it.

Dray
10-25-2017, 06:04 AM
Check that you have a valid filename in StrFlNm (e.g. Msgbox StrFlNm) after you've populated it.

OK, so lets say that the original file name is testing.doc, and that it is in C:\Users\Public\, but I want it to save to C:\Users\Public\Documents\, how would it look? I figured out my first problem with the '5152' error, but now I am getting a '5941' error. I'm missing something, but can't seem to see what is missing.

Edit: Nevermind, it is back to the '5152' error with the first .SaveAs line.

Sub SaveAs()
Dim StrFlNm As String
With ActiveDocument
StrFlNm = .Bookmarks("Product").Range.Text & _
Format(.Bookmarks("Date").Range.Text, "MM-DD-YY") & _
" " & Format(.Bookmarks("Time").Range.Text, "HHMM")
ActiveDocument.SaveAs FileName:="C:\Users\Public\Documents\" & StrFlNm & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
ActiveDocument.SaveAs FileName:="C:\Users\Public\Documents\" & StrFlNm & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
End With
End Sub

Dray
10-25-2017, 08:47 AM
I gotta say, Excel is so much easier. This is my macro (attached to a button) that works perfectly:

Sub SaveAs()
Dim Path As String
With ActiveWorkbook
Dim filename As String
Path = "S:\Operations\TEST\"
filename = Range("K3")
ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsm", FileFormat:=52
End With
End Sub

Why can't Word be this easy? lol

Dray
10-25-2017, 11:17 AM
OK, I finally got it to work, however, the filename has FORMTEXT before each part of the filename when it saves. Is there a way to prevent that from happening?

This is what the file name looks like after it saves:

C:\Users\Public\Documents\ FORMTEXT  Alarm Event TEST  FORMTEXT 10-25-17  FORMTEXT 1302 .docx
C:\Users\Public\Documents\ FORMTEXT  Alarm Event TEST  FORMTEXT 10-25-17  FORMTEXT 1302 .pdf

macropod
10-25-2017, 01:14 PM
The only way I can see that you might end up with FORMTEXT showing is if your document is displaying the field codes, which you ordinarily wouldn't do.

Dray
10-26-2017, 05:01 AM
The only way I can see that you might end up with FORMTEXT showing is if your document is displaying the field codes, which you ordinarily wouldn't do.

The fields are displaying the info that is inputted into the boxes. I've done a Shift-F9, which causes the box to show FORMTEXT, and than I turned it back off. I'm stumped right now. Your code works great though, macropod. Thank you!

macropod
10-26-2017, 05:34 AM
Try:

StrFlNm = .FormFields("Product").Result & _
Format(.FormFields("Date").Result, "MM-DD-YY") & _
" " & Format(.FormFields("Time").Result, "HHMM")

Dray
10-26-2017, 06:21 AM
FORMTEXT doesn't show up, but it zeroes out the time. The filename ends up being "TEST 10-26-17 0000.doc"

EDIT: I changed how the time is entered into the Time field and it seems to work. Now to see if I can get it to work for other users.

EDIT 2: So I attached the macro to a MacroButton, which works just fine. That is, until I turn on the Restrict Editing to protect the document. Users can enter the info needed into the various fields but can not make any other changes to the document. However, when the button is pushed to make it save and run the macro, it does not do anything but send me back to the first text field.

macropod
10-26-2017, 01:00 PM
To be able to use a macrobutton field, you'd need to insert a Section break before and/or after it and leave that Section unprotected.

Dray
10-27-2017, 05:30 AM
To be able to use a macrobutton field, you'd need to insert a Section break before and/or after it and leave that Section unprotected.

OK, I have the Section break in, and it works great! Thanks a bunch! :clap::bow:

suez00
12-07-2017, 01:34 PM
Hello, I successfully used this code for my document but get an error when transferring the information on the form to excel.
Is there a way to use this code and an extractor code together or is it impossible?


I tried to save the form after this automatic name change but get the following error when I use both macros:


Run-time error '6102':
Word encountered an error processing the XML file "AUTOMATEDFILENAME".docm
No error detail available


when I go to debug the code, it refers me to the following line:


Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)

The code seems to work on any other file except the automatically saved files.
Changing the name manually does not work.

Any ideas?

macropod
12-07-2017, 01:47 PM
The discussion and code in this thread has nothing to do with Excel, so I have no idea what you're trying to do or what you mean by 'automatically saved files'. Even your code snippet isn't related to anything discussed here.

Dray
12-08-2017, 11:12 AM
The discussion and code in this thread has nothing to do with Excel, so I have no idea what you're trying to do or what you mean by 'automatically saved files'. Even your code snippet isn't related to anything discussed here.

Are you talking about Suez post?

macropod
12-08-2017, 01:42 PM
Well, that is the post I was replying to...

shadyhoo
12-09-2017, 08:43 AM
thanks