Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save As Issue (2003 sp3)

    Hi again,,,
    Say, I have an issue where I am saving a workbook to CSV by using a macro. If the file being saved already exists, I get the message basically saying the file already exists. Continue yes, no, cancel. if yes is selected all goes well. If No or cancel are selected I get the dreaded Runtime error 1004 method 'save as' of object '_workbook' failed. Debug takes me to the code. I have tried to capture the clicking of no or cancel in a macro, but to no avail. I figured that If I could capture the action I could create the code to exit the section and not get the error. I basically know I need to on action of clicking the no or cancel, exit the section of code.

    Here is the section that is failing...

    ActiveWorkbook.SaveAs Filename:= _
    "c:data" & Range("a2") & ".csv", FileFormat:=xlCSV _
    , CreateBackup:=False
    ActiveWindow.Close

    I search in the lounge for "save as" issues, but found none that applied.

    Thanks,
    Brad

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As Issue (2003 sp3)

    Try:

    <code>
    On Error Resume Next
    ActiveWorkbook.SaveAs Filename:= _
    "c:data" & Range("a2") & ".csv", FileFormat:=xlCSV _
    , CreateBackup:=False
    On Error GoTo 0
    ActiveWindow.Close

    </code>
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As Issue (2003 sp3)

    Legare,

    Worked great!

    thanks,
    Brad

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As Issue (2003 sp3)

    Do you actually WANT your users to be able to say No or Cancel?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As Issue (2003 sp3)

    That brings up an interesting point.... Had not actually thought about it. let me think and I'll get back to you.


    Thanks,
    Brad

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As Issue (2003 sp3)

    Jan,
    After following the flow of the work, we decided that they will need to make the decision regarding the "Yes" or "No" question. However, in the future if they decide to change, how would this be handled? I can always comment the lines out until needed.

    Thanks,
    Brad

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As Issue (2003 sp3)

    If you add
    Application.Displayalerts=False

    before the save, Excel will give no warning and perform the action that is default (you can see which that is by noting what button on the dialog is the default button).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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