I am using a complex workbook that at some point in time must export data from the protected file into a new to be created excel file. I have written vba procedures for that. When executing the vba procedures I do get after the export of the files the “standard xlspadlock” window to save the “local workbook” in the secure file mode. I do not need that window there. I have tried to use the option “do not prompt user for filename” which resolves the issue of exporting data without “secure file save” window, but then I cannot save the complete file anymore in a secure user save - which I also need to do !. So what can I do ? Does exist a command that I can add in my original vba code to avoid the secure save xlspad window from popping up ?
XLS Padlock will only allow you to save your protected workbook in the .xlsc format. As you say no matter what you do in any of the settings you will always get a window that you can only save as .xlsc.
As a work around what I have found if you have your settings for the Protected Workbook to save a file as an .xlsc file you can still write a VBA code to “save as” a .xls normal excel file to export what you need… example code would look something like this below and your VBA tells EXCEL to open a NEW EXCEL APPLICATION… If you dont do it this way you will always get a window to save your file as a .xlsc
If g_bExportOptionSelected Then
If optNewExport Then Set xlApp = New Excel.Application Set WbkTarget = xlApp.Workbooks.ADD(xlWBATWorksheet) 'copy header vData = shtData.Range("db").Offset(-1).Resize(1, shtData.Range("db").Columns.Count) WbkTarget.Sheets(1).Range("A1").Resize(UBound(vData), UBound(vData, 2)).Value = vData 'copy data vData = shtData.Range("db") WbkTarget.Sheets(1).Range("A2").Resize(UBound(vData), UBound(vData, 2)).Value = vData WbkTarget.Close Set xlApp = Nothing Call setScreenOn MsgBox "New Database Sucessfully Exported", vbInformation End If
Excellent Tip ! I used a somewhat different code and can now workaround this limitation.