Having Trouble Saving Copy of a Sheet (export report)

Hi @gdgsupport and All ,
YOUR SOFTWARE IS AWESOME! I do need a bit of help please.

I’m having trouble making a quick copy of a sheet and then saving that file. I used to use secure save files, but this app I don’t actually allow any saving, they always load the ‘Original’ version each time.
Here are my important settings:










This code copies the sheet to a new workbook, then attempts to save that workbook with protections temporarily disabled.
desc=”test”
Fname = Application.GetSaveAsFilename(InitialFileName:=desc & Format(Now, “mmddyyyy.hhmm”) & “.xlsx”, Filefilter:=“Excel Files (*.xlsx), *.xlsx”)
If Fname = “” Or Fname = “False” Then
MsgBox “Cancelled!”, vbOKOnly, “Innova”
s_xls = False 'failed; ds 02/08/17
Exit Function
End If
ws.Copy
Set Destwb = ActiveWorkbook

'Saving the Workbook ds 02/04/19
Dim XLSPadlock As Object
In my tests I have also tried ONLY doing SetOption 2 or ONLY SetOption 1 before and after the save.

I also tried .SaveAs using CreateBackup:=False
Once in a great while it works, but basically it’s just leaving these extension-less files instead of excel files.




Thanks for any advice. We’re launching this product tomorrow and this is the only thing I can’t figure out.

Have a great day!

Dan

UPDATE

So after hours and hours of playing with this, it seems that if I use ANY FILENAME that is not the same as whatever file they chose in the application.getsaveasfilename() dialog box, it worked. This lead me to believe that the application.getsaveasfilename() was ‘reserving’ that file name and XLSP was trying to save to a file location that was ‘reserved’. I ended up resolving this by adding a SPACE after whatever filename they chose and I’m hoping to find a better solution, but for now they will get a file that’s ALMOST what they named it.

So if they wanted “C:\test\my_file.xlsx”, it will actually be “C:\test\my_file .xlsx” (there’s a spacebar after my_file and before .xlsx).

Hope this helps someone!

@gdgsupport, is there a magic way around this with XLSPadlock, or will that need to be reviewed further in later sessions/testing/versions?

Thanks

Dan Strong, excelvbaisfun.com

Interesting bug, but looks like it is more related to Excel internals than XLS Padlock’s ones. application.getsaveasfilename() probably reserves the filename as you said…

It is indeed weird. I’ll likely make a workaround that copies or renames it after the file saveas and share it in the group. Perhaps we can have a tips section or if its useful enough it could go into documentation (up to you if course).

Thanks for being awesome @gdgsupport!

Dan