PDA

View Full Version : Excel GetSaveAsFilename with Word Doc



Schnauzer
10-10-2006, 09:51 PM
Hi,

I have an excel file and in it is a command button to run some code. What it is supposed to do when the button is clicked is:

1) open a specified word document
2) open a save as dialog box with the designated directory and a new filename (make up of the old name and a value entered in cell “B11” of the excel document) entered in the file name box
3) when the save button is clicked, the word document should be saved in the designated directory and under the new filename
4) If cancel button is clicked, it is suppose to exit the save as

What actually happen is; when I clicked the command button, the dialog box will appear with the designated directory and the new filename in the filename box. When I clicked the save button the dialog will close and the word document with the new name will appear and it is also saved in the designated directory/folder.

However, if the cancel button is clicked, the error message “Run time error ‘13’: Type Mismatched” appears. If I try to open the specified word document, the “file in use message appears. When I checked the designated directory, a document with the name “~filename” appears and even after I delete it, it will still indicate the file is already open even though I can’t find it anywhere. I have to restart my computer to get rid of that “file in use” message.

Can some one please help.

The two procedures that I wrote are below:


Option Explicit
Public Dir1 As Variant
Public WordApp As Object
Public OldName, NewName, Fname As String
Public WsCL As Worksheet
Public sDirDefault As String
Public sFileTypes As String

Sub Checklist()

sFileTypes = "Word Document (*.doc),*.doc"
sDirDefault = "G:\Fin\Misc"
OldName = "Project Scoping Sheet"

Dir1 = "G:\Fin\Misc\\Templates\Current\Subsidiary.doc"
Set WordApp = CreateObject("word.Application")
Set WsCL = Worksheets("Check List")
WordApp.documents.Open Dir1

WordApp.activedocument.Saveas Filename:=GetSaveAsFilenameTo(sDirDefault)
WordApp.Visible = True

End Sub

Function GetSaveAsFilenameTo(Optional sDirDefault As String) As Variant

Dim sDirCurrent As String

sDirCurrent = CurDir
If sDirDefault = vbNullString Then
sDirDefault = sDirCurrent
Else
If Len(Dir(sDirDefault, vbDirectory)) = 0 Then
sDirDefault = sDirCurrent
End If
End If

ChDrive Left(sDirDefault, 1)
ChDir (sDirDefault)
GetSaveAsFilenameTo = Application.GetSaveAsFilename(InitialFileName:=OldName & " - " & _
WsCL.range("b11"), filefilter:=sFileTypes)
ChDrive Left(sDirCurrent, 1)
ChDir (sDirCurrent)

End Function


Best regards
:(

Bob Phillips
10-11-2006, 01:36 AM
Sub Checklist()
Dim tmp

sFileTypes = "Word Document (*.doc),*.doc"
sDirDefault = "G" '"G:\Fin\Misc"
OldName = "Project Scoping Sheet"

Dir1 = "G:\Fin\Misc\\Templates\Current\Subsidiary.doc"
Dir1 = "G:\Development\Consultancy\xld\RoadTek\99_Deliverables\06_User Guide\Data Entry Buttons.doc"

Set WordApp = CreateObject("word.Application")
Set WsCL = Worksheets("Check List")
WordApp.documents.Open Dir1

tmp = GetSaveAsFilenameTo(sDirDefault)
If tmp <> False Then
WordApp.activedocument.SaveAs Filename:=tmp
WordApp.Visible = True
End If

End Sub

Schnauzer
10-11-2006, 04:35 PM
Hi xld

Thank you very much for your suggestion.

When I include the code below, I still ended with two problems.

SaveFileAs = GetSaveAsFilenameTo(sDirDefault)
If SaveFileAs <> False Then
WordApp.activedocument.Saveas Filename:=GetSaveAsFilenameTo(sDirDefault)
WordApp.Visible = True
End If



Whcn I clicked save, the dialog box will close and open up again. If I clicked cancel the second time, the document is not saved. If I clicked save the second time only then it will be saved.
If I clicked cancel, I can see in the directory/folder “~filename” indicating that the file is opened even though I can’t find it anywhere. The “File in use” message box will appear when I try to open the document.Any solution?

Best regards