Code help (office 2000)

Topic

New Reply

I’ve got the following macro which is supposed to save a file as Citco, if the name already exist then its supposed to ask the user whether or not to overwrite, if the user clicks on yes it should overwrite else it should allow the user to enter a name and save it as that, else it should save it with am empty space as the name.

What it does is prompting the user that there is another file with the same name exist and even allows the user to enter another name but then it displays an error message saying that it cannot save.

I’m sure there are loads of error on this macro coz I combined two different macros to create this.
Would someone PLEASE me with this code?
Sub CitcoEx()

Dim strFileName As String
strFileName = “S:SRI_WORK_AREADOCUME~1” & “CitcoFax” & Format(Now, “DDMMYY”) & “.doc”
vResult = Dir(strFileName)
If vResult “” Then
vResult = MsgBox(“File ” & strFileName & _
” already exists, Would you like to overwrite that file?”, vbYesNo)
If vResult = vbYes Then
Application.DisplayAlerts = False
ActiveDocument.SaveAs FileName:=”S:SRI_WORK_AREADocuments” & strFileName, FileFormat:=wdFormatDocument
Application.DisplayAlerts = True
Else
strFileName = “S:SRI_WORK_AREADOCUME~1” _
& InputBox(“File ” & strFileName & ” already exists,” _
& Chr(10) & “Please enter another filename not including ” _
& Chr(34) & “.xls” & Chr(34) & “: “) & “.doc”
ActiveDocument.SaveAs FileName:=”S:SRI_WORK_AREADocuments” & strFileName, FileFormat:=wdFormatDocument
End If
‘Display message
Beep
MsgBox “File has been saved.”, vbInformation, “Export Confirmation”
Else
Application.DisplayAlerts = False
ActiveDocument.SaveAs FileName:=”S:SRI_WORK_AREADocuments” & strFileName, FileFormat:=wdFormatDocument
Application.DisplayAlerts = True
End If
End Sub

Reply | Quote

Replies

The Error message I get is:
Run-time error 5152:
This is not a valid file name
Try one or more of the following,

Reply | Quote

Princess,

Looking back at the full code in your original post, it looks like that particular line of code appears in three different places in the procedure, so it would helpful if we can narrow in on which of those three occurrences is causing the error.

Without having testrun the code, my guess is that the invalid file name has something to do with the instance of that statement that involves the InputBox function:

strFileName = “S:SRI_WORK_AREADOCUME~1” _
& InputBox(“File ” & strFileName & ” already exists,” _
& Chr(10) & “Please enter another filename not including ” _
& Chr(34) & “.xls” & Chr(34) & “: “) & “.doc”
ActiveDocument.SaveAs FileName:=”S:SRI_WORK_AREADocuments” & strFileName, FileFormat:=wdFormatDocument

– if I’m reading this correctly, it looks like you are saying
strFileName = “S:SRI_WORK_AREADOCUME~1″ & the text returned from the InputBox.
Then in the next statement you are saying to save the document as filename
S:SRI_WORK_AREADocuments” & strFileName

But since we’ve just previously assigned something like
S:SRI_WORK_AREADOCUME~1DocName.doc to strFileName
it seems like you’re saying save file as:
S:SRI_WORK_AREADocumentsS:SRI_WORK_AREADOCUME~1DocName.doc
– which definitely isn’t a valid filename!

Again this is based on just a quick read; hope this is to the point.

Gary

Reply | Quote

So What are you suggesting me to do? The error is on the third(Last) Save command. Do u think I should remove that line? What I want the last line to do is to save it with a space if no name is given. I have no clue on what Im doing here, so please let me know what I could do to prevent getting this error message.

Reply | Quote

Try this:

Dim strFileName As String
strFileName = “S:SRI_WORK_AREADOCUME~1” & “CitcoFax” & Format(Now, “DDMMYY”) & “.doc”
vResult = Dir(strFileName)
If vResult “” Then
vResult = MsgBox(“File ” & strFileName & _
” already exists, Would you like to overwrite that file?”, vbYesNo)
If vResult = vbYes Then
Application.DisplayAlerts = False
ActiveDocument.SaveAs FileName:=”S:SRI_WORK_AREADocuments” & strFileName, FileFormat:=wdFormatDocument
Application.DisplayAlerts = True
Else
strFileName = InputBox(“File ” & strFileName & ” already exists,” _
& Chr(10) & “Please enter another filename not including ” _
& Chr(34) & “.xls” & Chr(34) & “: “) & “.doc”
ActiveDocument.SaveAs FileName:=”S:SRI_WORK_AREADocuments” & strFileName, FileFormat:=wdFormatDocument
End If
‘Display message
Beep
MsgBox “File has been saved.”, vbInformation, “Export Confirmation”
Else
Application.DisplayAlerts = False
strFileName = “NoDate”
ActiveDocument.SaveAs FileName:=”S:SRI_WORK_AREADocuments” & strFileName, FileFormat:=wdFormatDocument
Application.DisplayAlerts = True
End If
End Sub

Notes:

• the first changed (bold) line has removed the path string on the front – your StrFileName variable only contains the document name, not the path, everywhere else so it’s getting doubled up, as previous post notes.
• The second changed (bold) line added respecifies strFileName to something different; your original code basically said “save as this name, unless already in use, in which case save as this name” without changing ‘this name’ – and therefore still trying to use an existing name! You can of course change ‘NoDate’ to whatever you want – but it must be something, just a space is not a valid name.
  [/list]Hope I’m making sense here – and that the code works!

Reply | Quote

THANK U GUYS!
Thanx BerylM, You were VERY helpful, I had to add the following line to the code you amended
strFileName=”CitcoFax” & Format(Now, “DDMMYY”) & “.doc”
before the first Active line and it works fine now.

Thank you sooooooo much

Reply | Quote

SORRY, one more question:
I don’t want the macro to save the document when the user selects cancel, how can I do this?

Reply | Quote

Thank you guys, NO need for the help for the above question coz I’ve done it myself (I am VERY exited)
Sub CitcoEx()
Dim strFileName As String
strFileName = “S:SRI_WORK_AREADOCUME~1” & “CitcoFax” & Format(Now, “DDMMYY”) & “.doc”
vResult = Dir(strFileName)
If vResult “” Then
vResult = MsgBox(“File ” & strFileName & _
” already exists, Would you like to overwrite that file?”, vbYesNoCancel)
If vResult = vbYes Then
Application.DisplayAlerts = False
strFileName = “CitcoFax” & Format(Now, “DDMMYY”) & “.doc”
ActiveDocument.SaveAs FileName:=”S:SRI_WORK_AREADocuments” & strFileName, FileFormat:=wdFormatDocument
Application.DisplayAlerts = True
ElseIf vResult = vbNo Then
strFileName = InputBox(“File ” & strFileName & ” already exists,” _
& Chr(10) & “Please enter another filename not including ” _
& Chr(34) & “.xls” & Chr(34) & “: “) & “.doc”
ActiveDocument.SaveAs FileName:=”S:SRI_WORK_AREADocuments” & strFileName, FileFormat:=wdFormatDocument
Else
MsgBox “You’ve choose not to save the file”
End If
Else
Application.DisplayAlerts = False
strFileName = “CitcoFax” & Format(Now, “DDMMYY”) & “.doc”
ActiveDocument.SaveAs FileName:=”S:SRI_WORK_AREADocuments” & strFileName, FileFormat:=wdFormatDocument
Application.DisplayAlerts = True
MsgBox “File has been saved.”, vbInformation, “Export Confirmation”
End If
End Sub

Reply | Quote

I’ll add my congratulations. There’s nothing quite as encouraging as getting the thing to work!

In the spirit of continuing education, I’d also like to point out that figuring out what a macro does can be very difficult when all the lines are jammed up against the left margin. For a good example plus some great suggestions, see http://www.mvps.org/word/FAQs/MacrosVBA/MaintainableCode.htm. I’d like to see your macro reposted with proper indentation.

Reply | Quote

Jay,

there is indentation in the original post, but it gets suppressed when it’s posted.

Putting code in [ pre ] [/ pre] tags (without the spaces) would have preserved the indentation.

Reply | Quote

Hi guys,
I

Reply | Quote

I would love to add the following code to the code i have now so that when the user clicks on cancel after selecting to give the file another name, it won’t save the file but would exit.

If strFileName = IsNotNull Then
ActiveDocument.SaveAs FileName:=”S:SRI_WORK_AREADocuments” & strFileName, FileFormat:=wdFormatDocument
End If
This code would be in the last Else If part but something is wrong with the way I’ve code it, Can anyone tell me what? PLEASE.

Reply | Quote

Yes, I followed your guidelines but I really don’t get it.
I giveup

Reply | Quote

the screen tip was “.doc”. I need something that is opposite to that, tried *.doc but didn’t work.

Thanx.

Reply | Quote

Pressing Cancel in an InputBox returns an empty string (“”), which is why strFileName is only “.doc” in that case. (You are concatenating the empty string returned by InputBox & “.doc”) There are three ways to deal with this:

(1) If strFileName = “.doc” Then Exit Sub

(2) If strFileName “.doc” Then
‘do something useful
End If

(3) If strFileName = “.doc” Then
MsgBox “Document will not be saved.”
Else
‘real code here
End If

Reply | Quote

Below is the code I’ve been working on:

If strFileName = “*.doc” Then
ActiveDocument.SaveAs FileName:=”S:SRI_WORK_AREADocuments” & strFileName, Forrmat:=wdFormatDocument
MsgBox “File has been saved.”, vbInformation, “Buttonwood Trade Administration System”
Else
MsgBox “You’ve clicked on cancel, the document has not been saved.”, vbInformation, “Buttonwood Trade Administration System”
End If
Now the cancel part is working but the problem is that, when I enter another name it doesn’t save it with that name, instead it displays the message after the else and exits.

What am I doing wrong here?

2 everyone

Reply | Quote

long code in “pre” tags” split

Hi guys!
It workd PERFECTLY fine, Thanx to y

Reply | Quote

I have keenly read the entire thread, and tested out the final macro. I can suggest one small improvement:

If I choose No, and then proceed to give the same file name as the existing file, it still overwrites the file. There should be code to compare the existing file name and the name provided, and if they are the same, it should again display the three choices dialog box. I would have gladly attempted this myself but for paucity of time today.

Cheers

Reply | Quote

Thanx Rajesh.
What ur saying is true, I’ve noticed that too, I’ll do something about it.
Thanx again.

Reply | Quote