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.

			    
						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.

Save values in the unprotected file.