Hi, I have a protected workbook in wich a macro creates and saves a new xlsx workbook. So I enabled either Padlock options “Do not allow loading/saving other workbooks (even with VBA code)” and “Allow loading/saving other workbooks through VBA SetOption helper” as suggested by the guide.
The code I wrote obtain the path where the new workbook will be saved (Application.GetSaveAsFilename) and then it checks if there is already a file with same name so it asks to user if he wants to replace or not the file.
The code (semplified) is the following:
vFile = Application.GetSaveAsFilename( _ InitialFileName:=sFilename, _ fileFilter:=sFileFilter, _ Title:="Select path") debug.print Dir(vFile)
I use this code also for exporting PDF files and always worked fine (fileFilter=“PDF Files (*.pdf), *.pdf”).
But when I use the same code to export xlsx files (fileFilter=“Excel Files (*.xlsx), *.xlsx”), a strange thing happened. After running the code posted before, the protected document creates a hidden file with the specified name and dimension 0 KB (invisible when I open the destination folder). This imply that every time is asked to user that a file with same name already exists, also if the folder is empty.
This happed only in the protected document (not in normal Excel) and BEFORE every line of code that creates the document.
Example: I create a new empty folder on desktop called “TEST”. Call the following macro, select the folder “TEXT” as destination and click “Save” in the saving window:
Sub testExportXls() Dim vFile As Variant vFile = Application.GetSaveAsFilename( _ InitialFileName:="TestFile.xlsx", _ fileFilter:="Excel Files (*.xlsx), *.xlsx", _ Title:="Select path") End Sub
Obviously the folder “TEST” is empty (you can verify opening it).
But if you run again the macro, you can see in the saving window that there is a file with dimension 0 KB named as specified.
As described before, this not happen on normal Excel, but only on protected workbook with XLS Padlock (version 2020.1).
Thank you for the help