Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Feb 2011
    Posts
    27
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Problem with responding to "Save As" dialog box

    Hi
    In VBA, I am saving a CSV file as an xls file before I process the data in it. After processing, I re-save the file to capture the changes. Now I get the "Save As" dialog box asking me if I want to replace the already saved file. If I choose "Yes" all is well. If I choose either "No" or "Cancel" it crashes.

    How can I work this out becuase there maybe times I'd rather not save the changes.
    Thanks!
    Rick

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    What exactly do you mean by "it crashes"?

    Steve

  3. #3
    Lounger
    Join Date
    Feb 2011
    Posts
    27
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Sorry - I get:
    Run-time error '1004':
    Method 'Save as' of 'object_workbook' failed

  4. #4
    2 Star Lounger
    Join Date
    Mar 2010
    Location
    Tampa, FL, USA
    Posts
    114
    Thanks
    11
    Thanked 10 Times in 9 Posts
    Can you post your macro or a sanitized version of your workbook containing the macro?
    PJ in FL

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    A runtime error is different than a "crash". The error indicates a problem with saving the workbook, either the name is invalid, or something else along those lines. As PJ indicates we would have to examine the code to see what is going on and possibly an indication of where the code fails (when you get the error you can click [debug] and see the offending line of code...

    Steve

  6. #6
    Lounger
    Join Date
    Feb 2011
    Posts
    27
    Thanks
    4
    Thanked 0 Times in 0 Posts
    This is a portion of my macro. The runtime error is generated on the last line when there is an existing file with the same name I am using to save the current workbook.

    Private Sub Ok_Click()
    Dim Workrange As Range
    Dim Oldsheet As String
    Dim newsheet As String
    Dim oldworkbook As String
    Dim Saveworkbook As String

    'macro to save CSV as an xls & add a sheet & return to the original sheet

    oldworkbook = ActiveWorkbook.Name
    'replace csv with xls as file extension
    Saveworkbook = Replace(oldworkbook, "csv", "xls")
    'save file as xls
    ActiveWorkbook.SaveAs Filename:=Saveworkbook, FileFormat:=xlExcel8
    Last edited by arjay13; 2011-05-18 at 09:00.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    What do you want the code to do if the file already exists and the user does NOT want to replace it?

    Steve
    PS you could do something like this:
    Code:
    Option Explicit
    Private Sub Ok_Click()
      Dim Workrange As Range
      Dim Oldsheet As String
      Dim newsheet As String
      Dim oldworkbook As String
      Dim Saveworkbook As String
      Dim iResponse As Integer
    'macro to save CSV as an xls & add a sheet & return to the original sheet
    oldworkbook = ActiveWorkbook.Name
    'replace csv with xls as file extension
    Saveworkbook = Replace(oldworkbook, "csv", "xls")
    'save file as xls
    'check if file exists
    If Dir(Saveworkbook) = "" Then
      'file does not exist
      ActiveWorkbook.SaveAs Filename:=Saveworkbook, FileFormat:=xlExcel8
    Else
      'File exists: Check if replacing
      iResponse = MsgBox(prompt:="File Already exists. Do you want to replace it?", _
        Buttons:=vbYesNo + vbQuestion)
        'Replace, ignore XL message
        If iResponse = vbYes Then
          Application.DisplayAlerts = False
          ActiveWorkbook.SaveAs Filename:=Saveworkbook, FileFormat:=xlExcel8
          Application.DisplayAlerts = True
        Else
          'File not saved...
          MsgBox "File Not saved..."
        End If
    End If
    End Sub
    Last edited by sdckapr; 2011-05-18 at 09:34. Reason: Added some code

  8. #8
    Lounger
    Join Date
    Feb 2011
    Posts
    27
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I'd like the macro to continue as if nothing happened. In other words, if I click on "no" or "cancel" I don't want the code to do anything- just proceed as if nothing happened.

  9. #9
    Lounger
    Join Date
    Feb 2011
    Posts
    27
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks sdckapr! That works perfectly. I am new to VBA and don't have an extensive knowledge of the syntax yet. I never would have come up with that solution. I will study it so I can get a little bit better. Thanks Again!
    Arjay

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Glad that it worked.

    Steve

Posting Permissions

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