Save Active workbook, Add a password via a prompt and stop creating backups

Hi there,

I currently have a macro that opens all the files in a folder, formats the data within the sheet and then saves each file before moving on to the next.

This is the line where it saves each workbook.

ActiveWorkbook.Close SaveChanges:=True

I would like to Save the workbook with its current file name, add a password to open the file via an on screen prompt, and stop the file from always creating a backup once opened.

Thanks for your help!!!



The full code,

Sub RUNMACROALLFILES()
Dim objFSO, fso
Set objFSO = CreateObject("Scripting.FileSystemObject")

Set myFolder = objFSO.GetFolder("F:\WhereMyStuffLives\")

Application.ScreenUpdating = False

For Each myFile In myFolder.Files
If myFile.Name Like "*.xlsx" Then
Workbooks.Open Filename:=myFile
For Each sh In ActiveWorkbook.Sheets

Application.Run ("PERSONAL.XLSB!FORMAT_ACCESS_QRY")

Next
ActiveWorkbook.Close SaveChanges:=True

End If
Next
Application.ScreenUpdating = True

End Sub

I don't think you can set the properties for saving as part of the close method, maybe save it and then close:

Please Login or Register  to view this content.

yudlugar, that worked a treat! Thank you!

One last question, I now get the message A file named "XXXXXX" already exists in this location Do you want to replace it? Y, N or Cancel. Can I select Yes within the Macro code?

Thanks again!!

I'm not sure which property lets you set that prompt, I'm sure there is one. You can turn off display alerts to go with the default value (which is yes) though:

Please Login or Register  to view this content.

Unfortunately the files don't save with the amended code... but this has saved heaps of time already - Thanks!