Problem with responding to “Save As” dialog box

Topic

New Reply

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

Reply | Quote

Replies

What exactly do you mean by “it crashes”?

Steve

Reply | Quote

Sorry – I get:
Run-time error ‘1004’:
Method ‘Save as’ of ‘object_workbook’ failed

Reply | Quote

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

Reply | Quote

Can you post your macro or a sanitized version of your workbook containing the macro?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Glad that it worked.

Steve

Reply | Quote