This example shows how to extract values (not formulas) into another workbook and save it
as usual Excel workbook. This method allows to create a "screenshot" of your workbook. Your
customer can use this file to other PCs.
This project and all files (locked and unlocked ones) can be downloaded from
our website using this link lockxls_samples.msi.
Macro, which performs this operation, is assigned to button in our example:
' collect all sheet names to be copied
Dim asSheetNames() As String
Dim nSheetCount As Integer
' exclude 'Main' sheet
nSheetCount = ActiveWorkbook.Worksheets.Count - 1
ReDim asSheetNames(nSheetCount - 1)
Dim nIndex As Integer, nNameIndex As Integer
nNameIndex = 1
For nIndex = 1 To nSheetCount + 1
If ActiveWorkbook.Worksheets(nIndex).Name <> "Main" Then
asSheetNames(nNameIndex - 1) = ActiveWorkbook.Worksheets(nIndex).Name
nNameIndex = nNameIndex + 1
Dim wb As Workbook
' new workbook created as a result of the copy operation should be last
' in the workbooks list
Set wb = Workbooks(Workbooks.Count)
' make it unlocked
' declare variable for LockXLS Runtime object
Dim oLockXLS As Object
' create LockXLS Runtime object
Set oLockXLS = CreateObject("LockXLSRuntime.Connect")
' unlock new workbook
Set oWorkbook = Nothing
Private Sub ClearSheetFormulas(oSheet As Worksheet)
Dim oCell As Range
Dim vValue As Variant
For Each oCell In oSheet.UsedRange
If oCell.HasFormula Then
vValue = oCell.Value2
oCell.Formula = ""
oCell.Value2 = vValue
This code uses UnlockWorkbook method which unlocks the workbook. This method can be used ONLY inside
the locked workbook. Your customer's can't use it outside of the file from external macro to unlock your file.
Compare Excel Workbooks and VBA Projects
xlCompare - Compare and merge Excel Workbooks, Worksheets, Names and VBA projects, including
macros, forms and controls. 10+ Time saving commands.
Compile Excel workbook to EXE file
Looking for simple Excel Compiler? Visit xlCompiler website. Compile Excel workbook to standalone application.
Excel is not required.
Find out where result of a formula cames from
Dependency Auditor - Analyze and trace dependencies and
calculations in your workbook.
Complete list of the Spreadsheet Tools products
Visit Spreadsheet Tools home page to see
complete list of our products and company information.