Could XLS Padlock Support please explain (in simple English) what these codes mean and when can/must they be used (found in and copied from User Manual):
-
What is the difference between:
Set XLSPadlock = Application.COMAddIns(“GXLS.GXLSPLock”).Object
and
Set XLSPadlock = Application.COMAddIns(“GXLSForm.GXLSFormula”).Object
and in which circumstances must each one be used?
-
Can you describe the below Options and what do they set (Value=1 or 0):
XLSPadlock.SetOption Option:=“1”, Value:=“1”
XLSPadlock.SetOption Option:=“1”, Value:=“0”
XLSPadlock.SetOption Option:=“2”, Value:=“1”
XLSPadlock.SetOption Option:=“2”, Value:=“0”
XLSPadlock.SetOption Option:=“3”, Value:=""
XLSPadlock.SetOption Option:=“5”, Value:=Res
I am trying to save my compiled workbook with click on command button on user form, but can’t get it to work.
Thank you.
It is not that easy. You must use the code snippets that are provided in the user guide. Some of them use the GXLS.GXLSPLock object while others require the GXLSForm.GXLSFormula object. They are not interchangeable.
Same for the options, see the user guide that explains when they should be used.
Do you want to save the workbook as a normal workbook or an encrypted workbook format?
I am saving my workbook in encrypted format with extension .XLSC and also with the same name as .EXE workbook.
To achieve this, I am using code snippets found in the User Guide and also “devised” my own save code to perform the actual save procedure. My save procedure is a public function because I “call” it from various places in my program. I had to adjust my saving code a little bit because when a user (customer) launches my program for the very first time (after setup/installation with Paquet Builder), the .XLSC file does not exist yet and can not be found with “GetSecureWorkbookFilename”. (I thought it had something to do with file association in Paquet Builder).
My method/code:
Get the EXE file name (code from User Guide):
Public Function GetEXEFilename()
…rest of code
Find path to the .EXE file (code from User Guide):
Public Function PathToFile(Filename As String)
…rest of code
Get the name of the secure (.XLSC) workbook (code from User Guide):
Public Function GetSecureWorkbookFilename()
…rest of code
Save the encrypted workbook (code from User Guide):
Public Function SaveSecureWorkbookToFile(Filename As String)
…rest of code
My code to eventually save encrypted workbook (adapted to accommodate first time save):
Public Function SaveWorkbookToFolder()
Dim XLSPadlock As Object
Dim ThisFileName As String
Dim FileNameArray() As String
Dim BaseFileName As String
Set XLSPadlock = Application.COMAddIns(“GXLSForm.GXLSFormula”).Object
ThisFileName = GetEXEFilename
FileNameArray = Split(ThisFileName, “.”)
BaseFileName = FileNameArray(0) & “.xlsc”
On Error GoTo Err
If Dir((PathToFile(BaseFileName))) <> “” Then
SaveSecureWorkbookToFile GetSecureWorkbookFilename
Else
SaveSecureWorkbookToFile PathToFile(BaseFileName)
End If
Exit Function
Err:
BaseFileName = “”
End Function
This is where I don’t know whether I should use
Set XLSPadlock = Application.COMAddIns(“GXLSForm.GXLSFormula”).Object
or
Set XLSPadlock = Application.COMAddIns(“GXLS.GXLSPLock”).Object
Could you please advise.
Thank you.
I think in my case I don’t have to worry about “SetOptions” and “Values” for settings?
Looks like you did not understand how to use the code snippets. You have to copy the entire function into a VBA module and then invoke the function in your own code.
Just copy and paste the code from the doc:
Public Function SaveSecureWorkbookToFile(Filename As String)
Dim XLSPadlock As Object
On Error GoTo Err
Set XLSPadlock = Application.COMAddIns(“GXLS.GXLSPLock”).Object
SaveSecureWorkbookToFile = XLSPadlock.SaveWorkbook(Filename)Exit Function
Err:
SaveSecureWorkbookToFile = “”
End Function
Do not modify it and place it into a VBA module.
Then, you can invoke it:
Sub Test_Save()
SaveSecureWorkbookToFile (“d:\my documents\my save.xlsc”)
End Sub
Apologies, to save space in this thread, I used a bit of “shorthand” explaining what I am trying to achieve.
-
As mentioned, I compile my workbook with XLS Padlock and then create setup with Paquet Builder.
-
In Paquet Builder I specify the programs folder as default destination folder where a folder (MyFolder) will be created. The files MyFile.EXE, MyFile.XLSC and .DAT files will be stored inside this folder. The path I specify in Paquet Builder is: %PROGFILEDIR%\MyFolder\
-
When I install my program on my computer with Paquet Builder Setup (first time), only the .EXE file is initially installed (no XLSC file visible yet).
-
All ribbons and toolbars in Excel are disabled (condition set in XLS Padlock). I programmatically, with a command button on a user form, save the secured (.XLSC) workbook.
-
In my code, I do not do a “Save As” with a pre-defined file path and pre-specified file name like in your example (“d:\my documents\my save.xlsc”).
-
Because I intend distributing my program to other users and the fact that my program will be installed on different computers, I had to adapt my save procedure to work on any computer.
-
The code snippets I found in the User Guide were fully copied into modules in my project, exactly as they appear in the User Guide. My own “save” procedure is also in a module and is invoked by using a combination of the results obtained from the “User Guide” codes/modules (i.e. GetEXEFilename(), PathToFile(Filename As String), GetSecureWorkbookFilename() and SaveSecureWorkbookToFile(Filename As String)).
-
Explanation of my save module (code):
My code to save encrypted workbook (also to accommodate first time save):
Public Function SaveWorkbookToFolder() 'My own module
Dim XLSPadlock As Object
Dim ThisFileName As String
Dim FileNameArray() As String
Dim BaseFileName As String
Set XLSPadlock = Application.COMAddIns(“GXLSForm.GXLSFormula”).Object
ThisFileName = GetEXEFilename
FileNameArray = Split(ThisFileName, “.”)
BaseFileName = FileNameArray(0) & “.xlsc”
On Error GoTo Err
If Dir((PathToFile(BaseFileName))) <> “” Then
SaveSecureWorkbookToFile GetSecureWorkbookFilename
Else
SaveSecureWorkbookToFile PathToFile(BaseFileName)
End If
Exit Function
Err:
BaseFileName = “”
End Function
ThisFileName = GetEXEFilename = MyFile.exe - (User Guide code)
FileNameArray = Split(ThisFileName, “.”) = MyFile
BaseFileName = FileNameArray(0) & “.xlsc” = MyFile.xlsc
Your example: (“d:\my documents\my save.xlsc”)
PathToFile = (“d:\my documents") - (User Guide code)
PathToFile(BaseFileName) = (“d:\my documents\my save.xlsc”) - (My code for first time save)
GetSecureWorkbookFilename = (“d:\my documents\my save.xlsc”) - (User Guide Code)
SaveSecureWorkbookToFile GetSecureWorkbookFilename = (save protected workbook to [“d:\my documents\my save.xlsc”] for all subsequent saves - (User Guide Code))
I fully understand the code snippets you have provided (thanks). I have incorporated User Guide codes in my module “SaveWorkbookToFolder()” which I designed to overcome a “first time save”, as the .xlsc file does not exist yet after Paquet Builder installs my program (for the first time) with SetUp.
My question remains:
In my “SaveWorkbookToFolder()” module, should I use,
Set XLSPadlock = Application.COMAddIns(“GXLSForm.GXLSFormula”).Object
or
Set XLSPadlock = Application.COMAddIns(“GXLS.GXLSPLock”).Object…???
Thank you
Ps. The code in my module works 100% and gives the correct result (tested)…it is just that I don’t know whether I should use (“GXLSForm.GXLSFormula”).Object or (“GXLS.GXLSPLock”).Object?
The function SaveSecureWorkbookToFile uses GXLS.GXLSPLock, so do not change it.
Public Function SaveSecureWorkbookToFile(Filename As String)
Dim XLSPadlock As Object
On Error GoTo Err
Set XLSPadlock = Application.COMAddIns("GXLS.GXLSPLock").Object
SaveSecureWorkbookToFile = XLSPadlock.SaveWorkbook(Filename)Exit Function
Err:
SaveSecureWorkbookToFile = ""
End Function
Sir,
The code/module you provided, does not work on my computer. I get an error message:
“Compile Error…Code Incompatible…etc.”
If I replace the line……
SaveSecureWorkbookToFile = XLSPadlock.SaveWorkbook(Filename)
With
SaveSecureWorkbookToFile = GetSecureWorkbookFilename
in the module you provided, then it works 100%!
I also found the module “GetSecureWorkbookFilename” from code snippets in the User Guide. (I copied this whole module to my workbook).
I am also using “GXLS.GXLSPLock ” as you suggested.
Thank you.
1 Like