Macro question - copy / save excel to excel

[nancyo]

I have a macro which copies a worksheet from one excel workbook into a new workbook and saves as a new filename.

I modifed the code (message box) to allow for the user to select NO and stop the macro. Works fine.

The problem occurs when the user runs the macro a second time (may happen on rare occassions). An excel message box appears which states that the file already exists, and do we want to continue. If the user selects YES, the macro runs fine.

If the user selects NO or CANCEL, get a run time error '1004 . The section of the code for saving the file is highlighted.

I tried to modify the code to account for this, trying to add IF THEN statements to account for this messagebox. The problem is that the message box is NOT part of the code, but a function of excel.

Now, I can live with this, as only 3 people will be using this particular macro (I'm one of the 3), and I can show them what to do.

But, the AR part of me wants to have the macro run for all scenarios.

Any suggestions / comments?

[Joe Was]

Did you remember to de-activate and re-activate:

Sub
Application.ScreenUpdating = False
Application.DisplayAlerts = False

your code here!

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Hope this helps? JSW

[nancyo]

Joe Was - That's it!!!!

Now the message does not appear, so I am going to add more to my initial mesage box to let the use know that they can over-write a file.

SO EASY!!!!!!!!!! My god.....THANKS!!!!!!!!

[nancyo]

Double post - oops! In my excitement...!


[ This Message was edited by: nancyo on 2002-05-16 08:25 ]

[Adiv]

If you're getting a 1004 (Application-defined or object-defined error) you should address it and not cover it up. Can you display your subroutine? You shouldn't let Excel generate an alert about an existing file. You should use the Dir function to handle that situation proactively.

Just my $0.02

[NateO]

Nancy, if you want to give the user the option to cancel on duplicate, you can use an error trapper like below:

Sub fsy()

Application.ScreenUpdating = False

fname = Sheets(2).[a1].Value 'set filename as cell a1's value on 2nd sheet

Sheets(1).Copy 'Copy first sheet to a new workbook

ChDrive ("C") ' drive letter

ChDir ("c:my documents") ' target directory

If fname <> "" Then 'make sure fname is not blank

On Error GoTo 1

ActiveWorkbook.SaveAs FileName:=fname & ".xls"

Else: MsgBox "Please Enter a value in A1 and retry."

End If

End

1: ActiveWorkbook.Close False 'close new workbook, don't save

End Sub

[nancyo]

NateO - I will try this code this afternoon, not familiar with the format.

The modification to my code with the alerts is an "easy" solution, which does work, but I would like to learn how to code it.

Adiv - what is the dir function?

[ This Message was edited by: nancyo on 2002-05-16 09:56 ]

[Joe Was]

Nancyo,
You need to test your options if you de-activate the alerts. Your code may not do what you think!
If the Excel warning is just to let you know that you are over-writing then the Alert cancel is the right way to go. If you cancel your save code and your file handling works the way you want, even with a sloppy cancel condition, you can keep the cancel alert. As the cancel alert will exit your code even if it has a problem. In this case the problem does not matter?

If the code does not do what you want then the cancel alert will hide the problem not fix it. With out seeing y6our code we cannot provide the correct exit for your option.

You may or may not have a problem with the way your application works when using the cancel alert, but your code could still be sloppy. If it works only you will know its sloppy!

Repost if this bothers you with your code and we will try to sort it out. Hope this helps. JSW

[nancyo]

Here is my code, just a couple of comments:

1. I have some of this from suggestions, and some from actually recording.

2. The first part, up to the copy, is generating a message box which gives the user a choice of OK or cancel. If OK, then the code copies and saves. If cancel, then displays a different message and ends.

3. The copy part of the code was recorded (removes all formulas and replaces with values, to eliminate links).

4. This code is set up without the excel alerts, so the initial message box specifies this. The only thing this alert does is let the user know the file already exists. Only Management will be using this particular macro, so we will know if over-writing would be a problem (hardly ever).

5. If I can change the code to inlcude Nate's stuff, I can, if it makes it more professional and accurate.

Sub TCtoApproved_4()
'To automatically copy/save a new file in approved excel directory with TCs only
Application.ScreenUpdating = False 'disables "flashing" screen
Application.DisplayAlerts = False 'disables excel alerts
Dim Msg, Style 'allows choice of OK or CANCEL
Msg = "To Automatically Generate TYPICAL COUNTS In The Approved Excel Directory - OK" & vbCr & vbCr & "Warning - This Macro Will Over-Write Any Existing File Each Time It Is Run"
Style = vbOKCancel + vbExclamation 'allows typical counts to be generated or canceled
response = MsgBox(Msg, Style)
If response = vbOK Then
fname = ActiveSheet.[F17].Text
Range("A1:I52").Select
Selection.Copy
Workbooks.Add
Range("A1").Select
ActiveSheet.Paste
Range("F17").Select
Selection.Copy
Range("F17").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("D24:D33").Select
Application.CutCopyMode = False
Selection.Copy
Range("D24").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("F24:F33").Select
Application.CutCopyMode = False
Selection.Copy
Range("F24").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.SmallScroll Down:=21
Range("F52").Select
Application.CutCopyMode = False
Selection.Copy
Range("F52").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("I52").Select
Application.CutCopyMode = False
Selection.Copy
Range("I52").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ChDir "N:TYPCNTapprovedLATFLOWbetaSLBL"
ActiveWorkbook.SaveAs Filename:=fname & ".xls"
ActiveWorkbook.Close
Range("a1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Else 'typical counts NOT generated
MsgBox "Typical Counts Not Generated", 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True 'enables excel alerts
End If
End Sub



I don'tknow how to fix the format here...
[ This Message was edited by: nancyo on 2002-05-16 11:16 ]

[ This Message was edited by: nancyo on 2002-05-16 11:17 ]

[NateO]

The code below will allow for a cancel. Not sure what you mean by Dir, chDir is to change your active Excel directory. On my LAN, I have to change drives first or it's error city. They're both professional, depends what you want, if you want to allow a user to cancel change:

ChDir "N:TYPCNTapprovedLATFLOWbetaSLBL" 

ActiveWorkbook.SaveAs Filename:=fname & ".xls" 

ActiveWorkbook.Close

to

ChDir "N:TYPCNTapprovedLATFLOWbetaSLBL"

On Error Resume Next

ActiveWorkbook.SaveAs _

Filename:=Application.Substitute(fname, ".xls", "") & ".xls"

on error goto 0

ActiveWorkbook.Close False

_________________
Cheers, NateO

[ This Message was edited by: nateo on 2002-05-16 13:37 ]