Saving other workbooks from vba


#1

i want to generate reports in xls format from my code . i want to save these files as normal excel files . i added following code given at point not 11.8 of xlspadlock user guide

Dim xlspadlock As Object
Set xlspadlock = Application.COMAddIns(“GXLS.GXLSPLock”).Object
xlspadlock.setoption option:=“1”, Value:=“1”

however i get following error meesage

run time error ‘9’
Subscript out of range at line no 2 of above code

please help


#2

The code snippet is incomplete. From the guide:
image
You have to trap such a runtime error 9 because the xlspadlock object is not available before you compile your workbook with XLS Padlock.


#3

have done the trapping . the reports gets generated and get saved as normal save . however even after saving the workbook generated as normal excel files . a dialog box open for secure save
code used by me is as follows

fs = Application.GetSaveAsFilename(inm, " (*.xls), .xls")
gs = ActiveWorkbook.Path
gs1 = WorksheetFunction.Concat(gs, “\gtsr”)
Workbooks.Add (gs1)
Set sws = ActiveWorkbook
On Error Resume Next
xlspadlock.setoption option:=“1”, Value:=“1”
sws.SaveAs Filename:=CStr(fs)
sws.Close
xlspadlock.setoption option:=“1”, Value:=“0”


#4


#5

the workbook i generated from vba code got saves as normal excel file successfully . however after that above window asking for secure save opens unnecessarily . Please resolve


#6

Please look into the matter


#7

Your code is incomplete. The definition of the xlspadlock object is missing: see our code in previous post.


#8

I have included the definition at module level


#9

i have included following code at module level
since i need to generat excel sheets from two different subs

Public xlspadlock As Object

the code is otherwise working fine , the excel reports i am generating are getting saved as normal excel file. only problem is opening of un required secure save window


#10

Please resolve


#12

please see the matter and resolve

when i have copied the code given in your guide the excel genrated gets saved as normal excel file
after that a secure save dialouge box opens which user have to cancel to move further


#13

As explained, if the dialog box still pops up, that means the setoption wasn’t called. DO not define the xlspadlock object globally but locally just before using it.