Tom's Guide | Tom's Hardware | Tom's Games
![]() |
![]() |
![]() |
I have a macro that opens several files and then saves them in another folder with another (similar) name, and then it closes the file again.
However every time the file is open excel asks me if I want to update the values and the macro stops until I click no or yes to continue.
Then before the file is saved with a different name the macro stops again and asks if it is ok to save under the name I have set it to save to because that name already exits. This is true because I want the file to be saved over another file that already exists.
So I want the macro not to stop and ask me this question and to automatically tell excel not to update values and that is ok to save over a file that already exists. So it will run smoothly till all the files are saved under the other folder and name
Is there a way to do this? or another way to save with other name?Thanks!! ;)
here is my VB code
[CODE]Sub Saves_as_Prior()
'
' Guardar templates actuales como priors
' Macro recorded 2/28/2008 by Mayra Hurtado'Turn off screen flickering
Application.ScreenUpdating = False'Diesel
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\Diesel.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\Diesel ant.xls"
ActiveWindow.Close'FH Extra
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\MEXEXTRA08BudFH.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\MEXEXTRA08BudFH ant.xls"
ActiveWindow.Close'DSL Extra
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\MEXEXTRADSL08Bud.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\MEXEXTRADSL08Bud ant.xls"
ActiveWindow.Close
'GAS Extra
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\MEXEXTRAGAS08Bud.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\MEXEXTRAGAS08Bud ant.xls"
ActiveWindow.Close
'Passback
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\Passback Act.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\Passback ant.xls"
ActiveWindow.Close
'35
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\Plta 35 DM.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\Plta 35 DM ant.xls"
ActiveWindow.Close
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\Plta 35 Mx.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\Plta 35 MX ant.xls"
ActiveWindow.Close
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\Plta 35 US.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\Plta 35 US ant.xls"
ActiveWindow.Close
'57
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\Plta 57 DM.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\Plta 57 DM ant.xls"
ActiveWindow.Close
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\Plta 57 Mx.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\Plta 57 MX ant.xls"
ActiveWindow.Close
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\Plta 57 US.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\Plta 57 US ant.xls"
ActiveWindow.Close
'58
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\Plta 58 DM.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\Plta 58 DM ant.xls"
ActiveWindow.Close
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\Plta 58 Mx.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\Plta 58 MX ant.xls"
ActiveWindow.Close
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\Plta 58 US.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\Plta 58 US ant.xls"
ActiveWindow.Close
'59
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\Plta 59 DM.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\Plta 59 DM ant.xls"
ActiveWindow.Close
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\Plta 59 Mx.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\Plta 59 MX ant.xls"
ActiveWindow.Close
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\Plta 59 US.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\Plta 59 US ant.xls"
ActiveWindow.Close'Sn luis
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\snluis.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\snluis ant.xls"
ActiveWindow.Close
End Sub
[/CODE]
MHT

Try adding
Application.DisplayAlerts = False
after
Application.ScreenUpdating = Falsethen add
Application.DisplayAlerts = True
before
End SubNote 1: If you use this method, and your code fails before it completes, it will probably leave DisplayAlerts = False and you may not get alerts that you want from other macros or even the application itself.
You may want to add an error handling routine to capture the error so your code sets DisplayAlerts = True before the sub ends.
Note 2: Save a copy of all of those workbooks in a safe place before trying the new code in case something goes terribly wrong.
Note 3: I *think* I have a routine someplace that copies and renames files without opening them. If I can locate it, I'll post it here.

I found the code I use to copy/rename files and modified it to fit your file paths and names.
This is what I think you are essentially doing:
1 - Copying all files from:
O:\Archivos 2008\Templates\Act2 - Putting them in:
O:\Archivos 2008\Templates\Prior3 - Adding " ant" to the file name just before the .xls
If that is the case, then this code should do the same thing.
However, I must warn you of something:
The first thing this code does is delete all the files in O:\Archivos 2008\Templates\Prior
If you can live without your ant files while the new ones are created, fine. If not, we could (with code) copy the existing ant files off to some remote location, create the new ones and then delete the old ones. A little redundancy never hurts!
I strongly suggest that you make back-ups of your files prior to running this code and put them someplace other than O:\Archivos 2008\Templates\Prior. This code worked on my machine, with my folders, but obviously any time you are deleting files with code (especially somebody else's code <g>) you should be extra careful.
Let me know how it works for you.
Sub CreateAntFiles()
Dim i
Dim oldNAME As String
Dim sfol As String
Dim dfol As String'Define Source & Destination Folders
sfol = "O:\Archivos 2008\Templates\Act"
dfol = "O:\Archivos 2008\Templates\Prior"
'Delete ant Files
Set fso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
fso.DeleteFile (dfol & "\*.*")
'Copy Current Files
Set fso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
fso.CopyFile (sfol & "\*.*"), dfol'Rename Files
With Application.FileSearch
.NewSearch
.LookIn = dfol
.FileType = msoFileTypeAllFiles
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
oldName = .FoundFiles(i)
Name oldName As Left(oldName, Len(oldName) - 4) & " ant.xls"
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

Thanks!!! you are great!! I have tried it and it works except it still ask me if i want to update values from links to other sources.
Is there a way to remove that?This is my code. will this work?
Sub Saves_as_Prior()
'
' Guardar templates actuales como priors
' Macro recorded 2/28/2008 by Mayra HurtadoOn Error GoTo Erreur1
Application.DisplayAlerts = False
'turn off messages
Application.ScreenUpdating = False
'turn off messages
Application.ScreenUpdating = False
'Turn off screen flickering
'Diesel
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\Diesel.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\Diesel ant.xls"
ActiveWindow.Close'FH Extra
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\MEXEXTRA08BudFH.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\MEXEXTRA08BudFH ant.xls"
ActiveWindow.Close'DSL Extra
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\MEXEXTRADSL08Bud.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\MEXEXTRADSL08Bud ant.xls"
ActiveWindow.Close
'GAS Extra
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\MEXEXTRAGAS08Bud.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\MEXEXTRAGAS08Bud ant.xls"
ActiveWindow.Close
'Passback
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\Passback Act.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\Passback ant.xls"
ActiveWindow.Close
'Sn luis
Workbooks.Open Filename:="O:\Archivos 2008\Templates\Act\snluis.xls"
ActiveWorkbook.SaveAs Filename:= _
"O:\Archivos 2008\Templates\Prior\snluis ant.xls"
ActiveWindow.Close
Application.DisplayAlerts = True
'turn on messages
MsgBox "Files have been succesfuly copied", vbOKOnly
Exit Sub
Erreur1:
Application.DisplayAlerts = True
'turn on messages
Msg = MsgBox("Files have not been copied", vbOKOnly)
If Msg = vbOK Then
Exit Sub
End If
End Sub
MHT

You have Application.ScreenUpdating = False twice. No need for that.
Try: Workbooks.Open "O:\Archivos 2008\Templates\Act\Diesel.xls" ,0
i.e. Drop the Filename:= and add ,0
Do you plan to try my Copy and Rename code?

Hi!
Ok thanks for your advice.
I was planing to try your code today. I'll let you know how it works out.
Thanks again!
MHT

Ok, I am new to macro's but I have a similar question. I recorded a Macro to do a Save As and the file name. How do I have the macro save the excel file as the next available number? For example, 0823-123 is the last file in the folder. The next time the macro runs I want it to Save As 0823-124, then 0823-125..... I do not want to write over any existing files in the folder.

Your question is a bit different than the OP's so I'm going to suggest that you repost your question under a new subject, perhaps something about incrementing the filename when saving.
Please include the code you are using (or at least the Save As section) and we'll see if we can offer some suggestions on how to modify it.

I have another question on regards to this.
Sometimes I want this option deactivated as the name will not change.
I want to add a check box that when check, this macro will be active. However if I uncheck the box I want it to not run during saving.
Is this possible?
Thanks in advance.
MHT

Maybe some type of UserForm could do this, but I don't know too much about UserForms so I can't say for sure.
I guess since a check box would be a "manual action" you could try a couple of things:
- Add some code to your macro to pop up a dialog box asking if the name should change and use the answer to steer the code to either save with the name change or not.
- Write a simple macro (and assign it to a button) that will put a value in a cell and have your code check that cell and branch depending on the value.
The pop-up box would be "safer" in that the user would be prompted every time and forced to make a decision.

![]() |
![]() |
![]() |

This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |