LockXLS On-line Help Library

Welcome
Getting Started
Using LockXLS
How to ...
Samples
About LockXLS
Purchase
Contact
Save values in the unprotected file

Save values in the unprotected file.

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:

Sub Button1_Click()
' 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
  End If
Next

Sheets(asSheetNames).Copy

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
Call oLockXLS.UnlockWorkbook(wb)
Set oWorkbook = Nothing
' release LockXLS Runtime object
Set oLockXLS = Nothing
' -------------------------------------------

' clear all formulas
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False

Dim ws As Worksheet

For Each ws In wb.Worksheets
  Call ClearSheetFormulas(ws)
Next

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

' save this workbook near current file in the same format
Call wb.SaveAs(ThisWorkbook.Path & "\UnlockedFile", ThisWorkbook.FileFormat)
' close new workbook
wb.Close

Set wb = Nothing
End Sub


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
  End If
Next
End Sub

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.