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
:(
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
:(