Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code help (office 2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29>
    I've got the following macro which is supposed to save a file as Citco<ddmmyy>, 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 <img src=/S/help.gif border=0 alt=help width=23 height=15> 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

  2. #2
    BAM
    Guest

    Re: Code help (office 2000)

    Can you tell us which line of your code is generating the error?

    When you run the macro, a message should appear. When it does there is a button labeled "Debug". When you click it, the VBA Editor will open and highlight the line generating the problem in yellow.

    What I recommend from there is to place your cursor in the name of the command/action that is being carried out and press <F1> This will open the Help manual and take you to the page for the command your cursor is in.

    Take a look at the syntax and compare it with what yours, also click any Example links (If present they will be listed under the Title.)

    If that doesn't shed any light on anything then I recommend turning on the Macro recorder and creating a macro that steps through what the line of code should do. So for example it looks like the primary goal of the macro is to save a file. So if the error is in one of those lines, record a macro that uses File/Save As, routes to the folder the file should be saved in, provide a name for the file and Save it.

    Then Stop the recording and compare the code you recorded to that you are using in the macro and see if you note any differences between the two.

    Let us know what you find and we'll go from there! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    ~~~~~~~~~~~~~~~~~~~~
    Cheers! <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help (office 2000)

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

  4. #4
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Code help (office 2000)

    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~1Doc Name.doc
    - which definitely isn't a valid filename!

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

    Gary

  5. #5
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help (office 2000)

    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.

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Code help (office 2000)

    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:
    <UL><LI>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.
    <LI>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! <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>
    Beryl M


  7. #7
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help (office 2000)

    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. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Thank you sooooooo much
    <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15>

  8. #8
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help (office 2000)

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

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #9
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help (office 2000)

    Thank you guys, NO need for the help for the above question coz I've done it myself (I am VERY exited) <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>
    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

  10. #10
    BAM
    Guest

    Re: Code help (office 2000)

    Glad you have the problem resolved. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Although this specific problem has been taken care of, part of what I was striving for in my initial post was to help you learn how to begin to determine where the problem lies and how to find the answers on your own.

    (I'm a big believer in "teaching you to fish" instead of just providing the nourishment. Guess that comes from my profession as a computer instructor. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> )

    In any event, if you want to learn, (that way you can resolve problems in minutes instead of waiting for hours for some one to come along and provide an answer <img src=/S/smile.gif border=0 alt=smile width=15 height=15>), you can pick up where we previously left off.

    As for recording a macro, you could create a new document based on another template and create your macro there. Then compare the recorded version to the macro and note any differences between the two versions.

    In this instance you see a different way of handling the path for the file name than the solution Beryl provided, but with VBA there many ways to achieve the same results.

    I

  11. #11
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Willow Grove, Pennsylvania, USA
    Posts
    205
    Thanks
    4
    Thanked 49 Times in 40 Posts

    Re: Code help (office 2000)

    I'll add my congratulations. There's nothing quite as encouraging as getting the <img src=/w3timages/censored.gif alt=censored border=0> 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 <A target="_blank" HREF=http://www.mvps.org/word/FAQs/MacrosVBA/MaintainableCode.htm>http://www.mvps.org/word/FAQs/Macros...inableCode.htm</A>. I'd like to see your macro reposted with proper indentation.

  12. #12
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help (office 2000)

    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.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  13. #13
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help (office 2000)

    Hi guys,
    I

  14. #14
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code help (office 2000)

    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.

  15. #15
    BAM
    Guest

    Re: Code help (office 2000)

    Here is a good opportunity to start learning VBA. <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

    In the VBA editor, click the gray bar to the left of the first line of code you added. I belive it would be this line:
    If strFileName = IsNotNull Then

    The line should turn maroon and there will be a maroon circle on the bar.

    This will allow you stop the macro at this point (called a breakpoint) so you can check things out. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Now:
    - Run the macro (Press <F5>)
    - Answer the prompts
    - When the macro reaches the line you have placed the breakpoint on, hover your mouse over strFileName.
    - You should see a screen tip that will provide you the current value of the strFileName variable.

    To remove the breakpoint, click the gray bar to the left of the line you set the breakpoint on.

    A few other items you can take a look at as well:

    Place your cursor in the word "InputBox" in your code and press <F1>. This will open the Help manual to the page on the InputBox function. Read over the specifics and then take a look at the "Example" link below the title of the page. This is mainly to review some examples on how the function is used and will help you learn the syntax, such as when do you need quotes, etc.

    Also, click the "See Also" link at the top and review the MsgBox topic. Between the two you will find a couple of methods you can use to handle this. (Well, it will give you a start anyway! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)
    ~~~~~~~~~~~
    Cheers! <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •