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()
Dim nSheetCount As Integer
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
Set oWorkbook = Nothing
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
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
' collect all sheet names to be copied
Dim asSheetNames() As StringDim nSheetCount As Integer
' exclude 'Main' sheet
nSheetCount = ActiveWorkbook.Worksheets.Count - 1ReDim 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 = xlCalculationManualApplication.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.CloseSet 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.