Enabling the "Save as" option for end user with conditions in place

XLS Padlock
1

Hi,

I want the end user to be able to save the file through the “Save As” option in ONLY the “.csv format” with 2 conditions in place:

  1. The user should not be able to access the macro.

  2. The user can only use the “save as” option if the file has less than 40 rows.

Kindly let me know how I can achieve this. Thanks!

2

You’ll have to use VBA code to check for the number of rows, and then to save the workbook file as CSV format. That’s possible to do it.
Finally, with XLS Padlock, you can lock access to the VBA project or even compile the macro with our VBA compiler, so that customers can’t modify the macro.

3

Thank you for the reply but I still face an issue.

I’ve written a VBA code(works perfectly) which exports a csv(utf-8) format excel workbook in the same folder as the original.

But when I build the .exe application using padlock and run the same macro. The csv(utf-8) file that gets saved is now all symbols unlike the first CSV I saved without using xls padlock.

How can I solve this?

4

Do you think you could share the VBA code you use to export to CSV with us?

5

Please find the code below. In the normal excel workbook , a .csv copy of the workbook gets saved in the same folder each time the macro is run. But in the XLSPadlock compiled workbook, nothing gets saved when the macro is run.

We have purchased xlspadlock, and now we need to cater to the end user ASAP, your swift response on this is highly appreciated.

The VBA code:

Sub ExportAsCSVincurrentfolder()

Dim thisWb As Workbook, d As Integer
Dim x As Long
Dim rWork As Range

Set rWork = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)
x = rWork.Count 'Does not allow more than 45 rows and 12 columns +
If x < 590 Then

Set XLSPadlock = Application.COMAddIns("GXLS.GXLSPLock").Object    'We disabled this line when running in regular non-xls excel
XLSPadlock.SetOption Option:="1", Value:="1"     'We disabled this line when running in regular non-xls excel

Set thisWb = ActiveWorkbook

ActiveWorkbook.ActiveSheet.UsedRange.Copy

Set TempWB = Application.Workbooks.Add(1)
With TempWB.Sheets(1).Range("A1")
    .PasteSpecial Paste:=xlPasteValues

End With
d = InStrRev(thisWb.FullName, “.”)
ActiveWorkbook.SaveAs FileNAME:=Left(thisWb.FullName, d - 1) & Format(Now, " yyyyddmm hhmmss") & Mid(thisWb.FullName, d) & “.csv”

ActiveWorkbook.Close savechanges:=False


 Else
MsgBox "Error!!"
  
End If

End Sub

7

The problem comes from thisWb.FullName in

d = InStrRev(thisWb.FullName, “.”)
ActiveWorkbook.SaveAs FileNAME:=Left(thisWb.FullName, d - 1) & Format(Now, " yyyyddmm hhmmss") & Mid(thisWb.FullName, d) & “.csv”

XLS Padlock works with virtualization: when the compiled workbook is run, the Excel file is virtualized at a random location. It is never unpacked to the hard disk and thus, ThisWorkbook.FullName will not point to a physical location on the disk but to the virtual location.

As a workaround, if you want to get the path to the compiled workbook (which is your EXE file), XLS Padlock provides you with VBA code that you can use in your workbooks:

Public Function PathToFile(Filename As String)
Dim XLSPadlock As Object
On Error GoTo Err
Set XLSPadlock = Application.COMAddIns(“GXLSForm.GXLSFormula”).Object
PathToFile = XLSPadlock.PLEvalVar(“EXEPath”) & Filename
Exit Function
Err:
PathToFile = “”
End Function

You can then call the function:

Sub Test_File()
DoSomethingWith(PathToFile(“data.xls”))
End Sub

In your case, I would use:

ActiveWorkbook.SaveAs FileNAME:=PathToFile(Left(thisWb.FullName, d - 1) & Format(Now, " yyyyddmm hhmmss") & Mid(thisWb.FullName, d) & ".csv")