Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Type Mismatch error

Status
Not open for further replies.

QueTech

MIS
Apr 13, 2003
79
US
Hi,

I'm using VB macros in microsoft Word Templates to pull client ID and name and place on the document. I have begun today to modify my macro to open "SaveAs" dialogue window. That part is working correctly. However, in my SaveAs window i am coding the file name to pull the Name , document type and file type. When I added a variable dtdate to the file name I get " Run Time Error "13" Type Mismatch. The variable is dtdate and I set it equal to "Now" or "Date". Please find below my code.

'==================================
'==================================
Private Sub cmdSave_Click()
pgnum = 1
namevar = frmSave.txtname.text
idvar = frmSave.txtID.text
dtdate = Now
Proceedvar = 1
Dim oldname, savFileName As Boolean

'THE NEXT BUTTON WAS JUST PRESSED
'check to see that all fields are filled in with something
'If a field is blank do not proceed

If frmSave.txtID = "" Then
MsgBox ("You need to fill in a Patient ID Number")
txtID.SetFocus
Proceedvar = 0
End If
If frmSave.txtname = "" Then
MsgBox ("You need to fill in a name")
Proceedvar = 0
txtname.SetFocus
End If
'
If Proceedvar = 1 Then ' TESTED FOR BLANK FIELDS
' Now that all fields are filled
' Move collected data to document
While pgnum < 3
Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, Name:=pgnum
Selection.Find.Replacement.ClearFormatting
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
With Selection.Find
.text = "(name)"
.Replacement.text = namevar
End With
Selection.Find.Execute Replace:=wdReplaceAll
' Replace the patien's ID with data from the form
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.text = "(number)"
.Replacement.text = idvar
End With
Selection.Find.Execute Replace:=wdReplaceAll
ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
pgnum = pgnum + 1
Wend
Selection.GoTo What:=wdGoToLine, Which:=wdGoToNext, Name:=1
' next we will bring up the "Save As" dialog box
cd1.Filter = "Microsoft Word Document (*.doc)"
UserName = Environ("username")
Pathvar = CurDir
Testvar = "I:\" + UserName
Mylen = Len(Testvar)
MyNew = Mid(Pathvar, 1, Mylen)
Mycheck = Pathvar Like Testvar
If MyNew = Testvar Then
cd1.InitDir = Pathvar
Else
cd1.InitDir = Testvar
End If
' if the user clicks cancel on the Save As dialog box,
' we do not want to save
With Selection.Find
.text = cd1.InitDir
.Replacement.text = thisdir
End With
cd1.FileName = namevar + dtdate + "_Format" + ".doc"
'Name savFileName As InitDir + cd1.FileName + ".doc"
cd1.ShowSave
ActiveDocument.SaveAs FileName:=cd1.FileName
frmSave.Hide
End If ' Ends the Test for blank fields

' Go to the end of the Document and prepare for note entry

End Sub

Thanks
 
Replace this:
dtdate = Now
with something like this:
dtdate = Format(Now, "yyyy-mm-dd_hh_nn")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
PHV has it. Other comments...

1. I hope you are using Option Explicit.
2. You could make the filename to saveas a string variable.
3. You can get at the header/footer without using View.


Gerry
 
I appreciate the help. Can anyone pass on an effecient procedure that will allow me to activate my SaveAs dialogue box at closing or Exit of the document?
 
1. ARE you using Option Explicit? Because:
Code:
Testvar = "I:\" + UserName
would get an error with that "+".

2. As stated, you could be doing things more efficently. Your code:
Code:
Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, Name:=pgnum
        Selection.Find.Replacement.ClearFormatting
        ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
        With Selection.Find
          .text = "(name)"
         .Replacement.text = namevar
        End With
    Selection.Find.Execute Replace:=wdReplaceAll
    ' Replace the patien's ID with data from the form
        Selection.Find.ClearFormatting
        Selection.Find.Replacement.ClearFormatting
        With Selection.Find
            .text = "(number)"
            .Replacement.text = idvar
        End With
       Selection.Find.Execute Replace:=wdReplaceAll
       ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
If you had those two chunks of as bookmarks - say (name) = HeaderName and (number) = HeaderNumber, then you could:
Code:
With ActiveDocument
    .Bookmarks("HeaderName").Range.Text = namevar
    .Bookmarks("HeaderNumber").Range.Text = idvar
End With

As for the SaveAs, if the user closes the document, or Exits they will get a file save dialog. Do you want to explicitly name a file save as? If so, then do so and bypass the user. It seems that you are giving an explicit filename SaveAs.

Finally, I notice you have frmSave.Hide...do you have an Unload instruction somewhere?

Gerry
 
Gerry,

thanks for all your suggestions. I have to admit that do not write in Vb regularly.

1. I am not using Testvar = "I:\" + UserName. It was a thought as I wanted to give the path that the file should be saved in. That is still need for me.

2. Your code does appear to be more efficient. I would like to impliment it. Do I assign my two chunks of code as bookmarks at the beginning of my program? Or do I only need to declare them as (name) = HeaderName ?

3. I do hide the frmSave at the end of the program. I assume that you recommend that I unload the frmSave. What is the syntax for an unload?

4. Yes, ideally I would like to automatically save the document with correct file structure with little to no user interaction.
 
1. I mentioned Testvar = "I:\" + UserName because it has a syntax error. + should be &. Again...hmmmmm...are you using Option Explicit. If not...do so.

2. You obviously have (name) AS TEXT in the header. What I am suggesting is either:

A) go into the header and select (name) and then Insert > Bookmark. Give the name....whatever you want..I used HeaderName, but it could be anything. Now you have a bookmark (named HeaderName). The code simply puts the variable namevar into the range of the bookmark. NOTE: the creation of the bookmark is NOT done by code. You are creating a bookmark, then coding into it.

B) go into the header and create the two bookmarks - HeaderName and HeaderNumber. Get rid of the existing text. It is not really needed anyway.

NOTE 2: if you are going to be doing multiple insertions of text into the bookmark range there are some aspects of inserting text into bookmarks that are affected by that. There is a simple solution, so post back if you need that functionality.

3. Uh...yes. ALL objects should be properly destroyed once they are no longer being used. Unload Me releases the userform. Why are you just hiding it? If it is hidden the object still exists. If you are finished with it...destroy it.

4. If YOU are making the decision regarding saving the file, then simply do so. Forget about the SaveAs dialog. Why bother if you do not need it. You - as the coder - can just save the file where ever you want to save it. However, I notice that you want to give the user a chance to Cancel out of saving.

a) is this correct? If so, why?
b) you could easily making a display to the user asking if they want to save the file. If they respond Yes, then again YOU save it. If they respond No, dump it.

Gerry
 
Thanks Gerry for all your input. I've been out sick for a while but intend to get right back on this.
 
Gerry,

If you can give some help with.

1. Is the syntax for Unload Me "frmSave.Unload Me".
2. You suggest that I use Option Explicit. I am not sure what you mean.
3. You could make a display to the user asking if they want to save the file. If they respond Yes, then again YOU save it. If they respond No, dump it. I think this is the way to proceed.
 
1. Unload frmSave
2. in the Declarations section (ie at the very top of the module) put this instruction:
[tt]Option Explicit[/tt]
Then you have to explicitly declare all the variables.
Its a safety measure against typos.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Hi,

I have implemented the Unload frmSave. Currently I am trying to Code where the file will be saved. This is the code I am using without success. This actually opens the SaveAs dialogue into my mapped drive I:\. The username is placed in the file name. Also when I hit cancel it saves the file anyway. What I really need is to be able to save the file in a predefined folder and allow the user to cancel the Save if they choose.

cd1.Filter = "Microsoft Word Document (*.doc)"
UserName = Environ("username")
Pathvar = CurDir
Testvar = "I:\" & UserName
Mylen = Len(Testvar)
MyNew = Mid(Pathvar, 1, Mylen)
Mycheck = Pathvar Like Testvar
If MyNew = Testvar Then
cd1.InitDir = Pathvar
Else
cd1.InitDir = Testvar
End If
' if the user clicks cancel on the Save As dialog box,
' we do not want to save
With Selection.Find
.text = cd1.InitDir
.Replacement.text = thisdir
End With
cd1.FileName = cd1.InitDir & namevar & "-" & dtdate & "_Format" & ".doc"
'Name savFileName As InitDir + cd1.FileName + ".doc"
cd1.ShowSave
ActiveDocument.SaveAs FileName:=cd1.FileName
Unload frmSave
 
1. I am not sure exactly the order of what you are doing. I am a little confused as to WHAT is running the process. Is this a saving Form? What is this form doing really, and what is calling IT? Perhaps what ever is calling the form should be doing the save. I don't know....but in any case...

2. First of all PLEASE use Option Explicit. As PHV mentions it goes at the top of your module. Better yet, set it so that ALL future module use it. In the Editor go Tools > Options and check Require Variable Dclaration. Now all future module will have it. NOTE: this will NOT go back and put Option Explicit is existing modules.

3. As stated, I would not call the SaveAs dialog at all. I am not sure what the file name is, as:
Code:
ActiveDocument.SaveAs FileName:=cd1.FileName
seem like funny syntax to me. Here is a simple sample:
Code:
Dim filename As String
Dim response
Dim msg As String
[COLOR=red]' get your filename however you are doing it[/colro red]
filename = "blah blkah"
msg = "This file will be saved as " & filename & _
   Is this acceptable?  Press Yes if you wish the file to be saved as " & filename & ".  Pressing No will " & _
"close this document without saving."

response = Msgbox(msg, vbYesNo)
If response = vbYes Then
   ActiveDocument.SaveAs Filename:= filename
Else
   ActiveDocument.Close
End If

Now, you may want to change it so it does not close, or return it to the user, or whatever. I am not sure what you are doing really. But this way you can tell the user - it is going to be saved as..... or....what?

Gerry
 
Let me provide some back ground on my project.

There is a macro running on every one of my templates that starts up on the opening of the document. This macro gathers information such as client ID, CLient name an Date of service. What I need now is ensure that the file is saved in a specific location using a specific naming struction for the file name. I thought I could accomplish this by opening an additional macro at the close of the document. This would allow my users time to fill out the doc completely before saving.

Sorry for confusing the matter.
 
Not a problem. Use the Document_Close event in the ThisDocument module. It will fire when the document is, well, closed.

However...what happens if the users saves the file before closing it? Will you not have to train the users to NOT save the file, and just close it? This seems like a not so great idea.

How much access to the file do you have? You could rewrite the FileSave command as well, and use that to explicitly save the file where you want, named as you want it. Then you could use the Document_Close event to verify that it WAS saved to the correct location.

Gerry
 
I again appreceiate your help I try to implement your suggestions. I do like the idea of rewritting the FileSave command so I can save the file where I want. Would still be able to specify the Drive and folder?
 
Would you provide a little more detail on how I can activate my save. I only have a Module1 showing and I am unclear how to add the event to it.
 
When every i add a bookmark to my .dot. I changes to erro bookmark not found. But I am able to right click on where bookmark should be and select toggle field codes to get back to the bookmark I created. Then if run my macro I get the error message Run-Time Eror '5941'

The requested member of the selection does not exist.

Find below the code for my Macro:


Private Sub cmdcancel_Click()
' Close the Form without doing anything
frmFormat.Hide
ActiveDocument.Close
End Sub
'==================================
'==================================
Private Sub cmdcancel_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
txtbox.Text = "This will allow you to Exit the form and document. " + _
" Click this Button to Cancel"
End Sub
'==================================
'==================================
Private Sub CmdFill_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
txtbox.Text = " Fill in whatever fields you want to fill in or leave them blank. " + _
"Click this Button to get a form that includes only the fields that you have entered. " + _
"This will not automatically save. It is for printing but you can also manually save it. "
End Sub
'==================================
'==================================
Private Sub cmdprintform_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
txtbox.Text = " Click this Button to Manually print the blank Document. " + _
"There is no need to enter data in any of the fields."
End Sub
'==================================
'==================================
Private Sub cmdSave_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
txtbox.Text = "This will Save the Form AFTER you have filled in all the text boxes." + _
" It will DEFAULT to your Home Directory if you are not presently in your home directory."
End Sub
'==================================
'==================================
Private Sub CmdFill_Click()
namevar = frmFormat.txtname.Text
idvar = frmFormat.txtID.Text
Adatevar = frmFormat.txtAdate.Text
dtdate = Now
' If Proceedvar = 0 Then ' TESTED FOR BLANK FIELDS
' Now that all fields are filled
' Move collected data to document
While pgnum < 10
With ActiveDocument
.Bookmarks("name").Range.Text = namevar
.Bookmarks("number").Range.Text = idvar
.Bookmarks("Adate").Range.Text = Adatevar
End With
pgnum = pgnum + 1
Wend
Unload frmFormat
' End If ' Ends the Test for blank fields
' Go to the end of the Document and prepare for note entry
Selection.GoTo What:=wdGoToLine, Which:=wdGoToNext, Name:=1
End Sub
'==================================
'==================================
Private Sub cmdprintform_Click()
Blank = " "

' If Proceedvar = 0 Then ' TESTED FOR BLANK FIELDS
' Now that all fields are filled
' Move collected data to document
While pgnum < 10
With ActiveDocument
.Bookmarks("name").Range.Text = namevar
.Bookmarks("number").Range.Text = idvar
.Bookmarks("Adate").Range.Text = Adatevar
End With

pgnum = pgnum + 1
Wend
Unload frmFormat
' End If ' Ends the Test for blank fields
' Go to the end of the Document and prepare for note entry
Selection.GoTo What:=wdGoToLine, Which:=wdGoToNext, Name:=1
End Sub
'==================================
'==================================
Private Sub cmdSave_Click()
pgnum = 1
namevar = frmFormat.txtname.Text
idvar = frmFormat.txtID.Text
Adatevar = frmFormat.txtAdate.Text
dtdate = Now
Proceedvar = 1
Dim oldname, savFileName As Boolean

'THE NEXT BUTTON WAS JUST PRESSED
'check to see that all fields are filled in with something
'If a field is blank do not proceed

If frmFormat.txtID = "" Then
MsgBox ("You need to fill in a Patient ID Number")
txtID.SetFocus
Proceedvar = 0
End If
If frmFormat.txtname = "" Then
MsgBox ("You need to fill in a name")
Proceedvar = 0
txtname.SetFocus
End If
'
If Proceedvar = 1 Then ' TESTED FOR BLANK FIELDS
' Now that all fields are filled
' Move collected data to document
While pgnum < 10
With ActiveDocument
.Bookmarks("name").Range.Text = namevar
.Bookmarks("number").Range.Text = idvar
.Bookmarks("Adate").Range.Text = Adatevar
End With

pgnum = pgnum + 1
Wend
Selection.GoTo What:=wdGoToLine, Which:=wdGoToNext, Name:=1
' next we will bring up the "Save As" dialog box
cd1.Filter = "Microsoft Word Document (*.doc)"
UserName = Environ("username")
Pathvar = CurDir
Testvar = "I:\" + UserName
Mylen = Len(Testvar)
MyNew = Mid(Pathvar, 1, Mylen)
Mycheck = Pathvar Like Testvar
If MyNew = Testvar Then
cd1.InitDir = Pathvar
Else
cd1.InitDir = Testvar
End If
' if the user clicks cancel on the Save As dialog box,
' we do not want to save
With Selection.Find
.Text = cd1.InitDir
.Replacement.Text = thisdir
End With
cd1.filename = namevar + ".Ft" + ".doc"
'Name savFileName As InitDir + cd1.FileName + ".doc"
cd1.ShowSave
ActiveDocument.SaveAs filename:=cd1.filename
Unload frmFormat
End If ' Ends the Test for blank fields

' Go to the end of the Document and prepare for note entry

End Sub
'==================================
'==================================
Private Sub txtbox_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
txtbox.Text = " Point to any button to get a description of its functions. Click on any button to activate the button or Tab to the button and press ENTER."
End Sub

Private Sub txtID_Change()

End Sub

'==================================
'==================================
Private Sub UserForm_Initialize()
txtbox.Text = " Point to any button to get a description of its functions. Click on any button to activate the button or Tab to the button and press ENTER."
End Sub
 
I have worked through one of my issues. I was inseting the wrong type of bookmark. Now am inserting the bookmarks from the menu optio Insert > Bookmarks. However, my document has ten pages but the macro only replaces the book mark on the last page.
 
1. WHAT "wrong" type of bookmark???????? Since you did not post the code showing how you inserting these, I am very curious as to what you were doing.


2. Since you did not post the code for these insertions, are you sure that the bookmarks were actually inserted?

3. The code you posted only deals with three bookmarks. WHAT other ones?

4. I made a comment earlier regarding the multiple uses of Bookmarks("whatever").Range.Text. It often removes the bookmark. Yes, it inserts the text in the proper place, but it overwrites the bookmark itself. There is a way around that.


Gerry
 
I'm using MS Word 2003 there I add bookmarks to the header and body of a template (Format.dot).

I used the Bookmard otion found under Insert menu option.

Once they were added I save the document and I run my macro.
The macro operates correctly with a couple of exceptions. Despite that, the document displayed the data enter through the macro.

When I open the document again the macro opens, however the templates header displays "Error! Bookmark not defined." where the (name), (number)and (Adate) were.

Exceptions:

The macro does not print the data to every page of the document. -The not page 7 of 11 has different header and the prints on 8 to 11.

There is a second form that opens when the document is closed and prompts the user to save the document to a predetermined location. The form opens but is recieving the not data.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top