My challenge was saving a table created in a workbook as a word doc, I wanted to use the Excel file name with a .doc added, so my macro actual does all this in excel, but I pasted it into a Word macro, worked fine.
Sub DisplayASuggestedDefaultName ()
' Sets the Filename variable and then saves the file, appears Filename can include just about anything that is acceptable to Windows filenames, _-.(){} as an example
Filename = "_-.(){}"
ActiveDocument.SaveAs Filename:=Filename & ".doc"
' Opens Dialog Box for locating the file in the correct folder
Application.Dialogs(wdDialogFileSaveAs).Show Filename & ".doc"
' Closes Word and deletes the dummy file in My Documents
ActiveDocument.Close
Kill "C:\Documents and Settings\maxs\My Documents\" & Filename & ".doc"
' Opens the file in the saved as folder
Application.RecentFiles(1).Open
End Sub
It is a little bit of a round about path, but pretty simple, I do not like the Kill statement, but with out a directory path to the recycle bin it was my only choice.
Peter Hibbs wrote:
OK, Plan B.
30-Jan-10
OK, Plan B
Looking at my code again I realise that another option the user has i
to send an email from the database with the Word document attache
which means that the document has to be saved to disk first before i
can be attached to an email. So my plan now is this :
1. Open Word and set up the document from the database
2. Allocate a new filename, change the destination folder to the loca
drive and save the document to disk with the new filename. I will the
use this file to attach to the email if the user so chooses
3. Change the Saved status of the document to False so that the use
has to save the document (or is warned that he has not saved it)
4. Change the destination folder to a new folder in which the use
would normally save the document. This needs to be done via the SaveA
dialog because the user will usually save it in a sub-folder of th
default folder
This is the code that I am using (in addition to the code in m
original post) but it does not work properly and I cannot see why
'fetch location of front-end fil
Set dbLocal = CurrentDb(
vLocal = GetPathname(dbLocal.Name
'change directory path to local database folde
ObjWord.ChangeFileOpenDirectory vLoca
'create new filename vFilename = Format(Date, "dd-mm-yyyy") & "
& lstDocuments.Column(0
'and save with new name to local folde
ObjWord.ActiveDocument.SaveAs Filename:=vFilenam
'reset Saved fla
ObjWord.ActiveDocument.Saved = Fals
'change directory path for saved doc to new folde
vDestination = Nz(DLookup("CoverLettersArchive", "tblDefaults")
ObjWord.ChangeFileOpenDirectory vDestinatio
The first bit works OK, the file is saved in the local folder (define
by vLocal) and the .Saved = False sort of works but the destinatio
folder does NOT change to the path defined in vDestination so when th
user tries to save the document with the SaveAs option, the Fil
Dialog shows the local folder instead of the new destination folder
Also, although the Saved flag seems to work when the user tries t
exit the document (with the File -> Exit or Close buttons) in tha
Word issues a warning that the document has not been saved, if th
user clicks on the toolbar Save icon, it saves the file immediately t
the local folder again whereas I need it to show the SaveAs dialog i
the same way as if he had created a new document and then clicked th
Save icon
Anyone know how to fix these problems
Peter Hibs.
Previous Posts In This Thread:
How to set filename via Automation
Access 2003 -> Word 2003
I am using Automation to open and display a Word document from a
Access database. The code I have is like this :
-------------------------------------------------------------------------------------
Public Sub PrintLetter(vPath As String
'Open Word and copy record info to Word do
'Entry (vPath) = Pathname and filename of template Word documen
'Exit Word doc displayed on screen ready for user to sav
Dim ObjWord As Word.Application
Dim vDestination As String, vFilename As String
On Error GoTo ErrorCode
'Start Word and create new doc
Set ObjWord = New Word.Application
DoEvents
ObjWord.ScreenUpdating = False
ObjWord.Documents.Add vPath
'.....code here to copy data to Word doc....
'fetch archive folder root pathname
vDestination = DLookup("CoverLettersArchive", "tblDefaults")
'fetch selected filename from list box
vFilename = lstDocuments.Column(0)
'change destination directory
ObjWord.ChangeFileOpenDirectory vDestination
'save doc with new filename
ObjWord.ActiveDocument.SaveAs Filename:=vFilename '???
ObjWord.ScreenUpdating = True
DoEvents
'Display Word doc on screen
ObjWord.Visible = True
ObjWord.Application.Activate
Set ObjWord = Nothing
Exit Sub
ErrorCode:
Beep
MsgBox Err.Description
End Sub
--------------------------------------------------------------------------------------
I have removed the irrelevant code for the purposes of this post.
What happens at the moment is that the user opens a selected Word
document, the code copies some data to the Word doc (not shown) and
then the document file is saved under a new filename in the folder
defined by the pathname returned from a field called
'CoverLetterArchives' in table tblDefaults. This works OK but what I
want now is NOT to save the document automatically but allow the user
to save the document in a folder of their choosing but keep the same
filename (unless they choose to change it, of course).
So I want to remove the line that does the SaveAs operation but if I
do that the filename just defaults to the first line of text in the
document and if the user closes the document, it does not warn him
that it has not been saved.
I want to change the code above so that the document opens on screen
in an un-saved state (so the user cannot close it without a warning)
and with the document filename defined as in the variable vFilename
above. The destination folder seems to change correctly so that bit
works.
Anybody got any ideas.
Peter Hibbs.
See
http://www.word.mvps.org/FAQs/MacrosVBA/SetDefFilename.htm.
See
http://www.word.mvps.org/FAQs/MacrosVBA/SetDefFilename.htm.
--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.
Peter Hibbs wrote:
Hi Jay,Thanks for that info, the code works 'sort of' now.
Hi Jay,
Thanks for that info, the code works 'sort of' now. I added the
following code to the routine :-
......
'change destination directory
ObjWord.ChangeFileOpenDirectory vDestination
'open doc with new filename
With ObjWord.Dialogs(wdDialogFileSummaryInfo)
..Title = vFilename
..Execute
End With
ObjWord.ScreenUpdating = True
DoEvents
.......
which does show the correct filename in the dialog box when the user
chooses the Save or SaveAs options.
However, when the document opens, the default filename at the top of
the screen shows "Document1 - Microsoft Word" and if the user tries to
exit Word without saving the document first he gets the warning
message :- "Do you want to save the changes to Document1?".
How can I also change the default filename to show the new name when
the document is first opened?
Thanks for any assistance.
Peter Hibbs.
A document does not have a name (other than "Document X") until it has
A document does not have a name (other than "Document X") until it has been
saved for the first time. There is no mechanism other than saving for
assigning a name. As I am sure you have already learned, the .Name property of
a Document object is a read-only string.
So if you want to give the document a name from the start, then your code
needs to make saving the first thing that happens after the document opens.
If you need user input through a field or something in the document, you
could pop up a userform to get the information, populate the field in the
document, assign the title, and immediately force a save.
No, I do not like it either, but that is what we have to work with.
--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.
Peter Hibbs wrote:
Hi Jay,OK, this would explain why I could not find any help on Google.
Hi Jay,
OK, this would explain why I could not find any help on Google.
Although it is a bit annoying I do not think it will be a major
problem, I will just advise the users to ignore the default filename
that they see, at least the Save dialog shows the correct filename
which is important to them.
Thanks again for your help.
Peter Hibbs.
Hi Jay (or anyone).OK, I spoke too soon.
Hi Jay (or anyone).
OK, I spoke too soon. Although the code does work, the way I am saving
the document files is to enter a date as the start of a filename, like
so :-
01-01-2020 Name of Template.doc
When the document is saved the filename shows up as 01.doc in the File
Dialog form. The same applies if the dashes are replaced with
underscores or slashes but NOT spaces. I know the Web site you
mentioned does state that this is a bug in Word and there is some code
there which supposedly provides a work-around but the Change_Props
routine does not work (it has numerous compile errors and when I fixed
those it does not work anyway).
So my questions is - is there a work around for the bug that actually
works and how would one use it (the Web site does not say how)?
Alternatively I am thinking I could save the file to a dummy folder
first, with the same filename each time so that only one file would
ever be shown in the folder, and then I could change the filename and
destination folder to the correct location and reset the File Saved
flag (I think this is possible) so the user could not close the
document without saving first. Would this get around the problem of
not being able to change the displayed filename at the top of the
screen because the document has now been saved? If so what sort of
code would I need to do this?
The only other option that I can see is to use spaces in the date part
of the filename and leave the code as is. I will need to check with
the users to see if this would be acceptable.
Any thoughts.
Peter Hibbs.
OK, Plan B.
OK, Plan B.
Looking at my code again I realise that another option the user has is
to send an email from the database with the Word document attached
which means that the document has to be saved to disk first before it
can be attached to an email. So my plan now is this :-
1. Open Word and set up the document from the database.
2. Allocate a new filename, change the destination folder to the local
drive and save the document to disk with the new filename. I will then
use this file to attach to the email if the user so chooses.
3. Change the Saved status of the document to False so that the user
has to save the document (or is warned that he has not saved it).
4. Change the destination folder to a new folder in which the user
would normally save the document. This needs to be done via the SaveAs
dialog because the user will usually save it in a sub-folder of the
default folder.
This is the code that I am using (in addition to the code in my
original post) but it does not work properly and I cannot see why.
'fetch location of front-end file
Set dbLocal = CurrentDb()
vLocal = GetPathname(dbLocal.Name)
'change directory path to local database folder
ObjWord.ChangeFileOpenDirectory vLocal
'create new filename vFilename = Format(Date, "dd-mm-yyyy") & " "
& lstDocuments.Column(0)
'and save with new name to local folder
ObjWord.ActiveDocument.SaveAs Filename:=vFilename
'reset Saved flag
ObjWord.ActiveDocument.Saved = False
'change directory path for saved doc to new folder
vDestination = Nz(DLookup("CoverLettersArchive", "tblDefaults"))
ObjWord.ChangeFileOpenDirectory vDestination
The first bit works OK, the file is saved in the local folder (defined
by vLocal) and the .Saved = False sort of works but the destination
folder does NOT change to the path defined in vDestination so when the
user tries to save the document with the SaveAs option, the File
Dialog shows the local folder instead of the new destination folder.
Also, although the Saved flag seems to work when the user tries to
exit the document (with the File -> Exit or Close buttons) in that
Word issues a warning that the document has not been saved, if the
user clicks on the toolbar Save icon, it saves the file immediately to
the local folder again whereas I need it to show the SaveAs dialog in
the same way as if he had created a new document and then clicked the
Save icon.
Anyone know how to fix these problems?
Peter Hibs.
Submitted via EggHeadCafe - Software Developer Portal of Choice
Featured Product / Service Review: TekPub
http://www.eggheadcafe.com/tutorial...2e-39384482c80e/featured-product--servic.aspx