Macro: Password protection for xlsx file and setting a reload condition in macro

[nihalbuddy09]

Hi All,

By the help of our community we are able to use the below macro to export the table into xlsx format. But we are still in search for the macros that will allow to save this output file along with a 'password protection' and also a 'condition' to reload the application.

Someone please advise how can I give the password to my output file and save, also how could I set the conditions in macro so that my application will be reloaded only when this condition get's met.

Condition:

let vISQVReloaded_TMP =(TEXT(DATE(ReloadTime(), 'YYYYMMDD')) = TEXT(DATE(Today(), 'YYYYMMDD')));

ISQVRELOADED:

LOAD $(vISQVReloaded_TMP) as ISQVRELOADED_TMP

autogenerate(1);

if wildmatch(ISQVRELOADED_TMP,'*-1*')=0 then

SET TriggerError;

else

errormode=0;

*****

Existing Xlsx macro which I am using now:

myFile = "Account_" & cDate(DATE) & ".xlsx"

FUNCTION cDate(myDate)

    strYear = YEAR(myDate)

    IF LEN(MONTH(myDate)) = 1 THEN

        strMonth = "0" & MONTH(myDate)

    ELSE

        strMonth = MONTH(myDate)

    END IF

    IF LEN(Day(myDate)) = 1 THEN

        strDay = "0" & Day(myDate)

    ELSE

        strDay = Day(myDate)

    END IF

    cDate = strYear & "-" & strMonth & "-" & strDay

END FUNCTION

myFile = "Account_" & cTime(Now) & ".xlsx"

FUNCTION cTime(myTime)

    strHour = Hour(myTime)

    IF LEN(Hour(myTime))= 1 THEN

        strHour = "0" & Hour(myTime)

    ELSE

        strHour = Hour(myTime)

    END IF

    strMinute = Minute(myTime)

    IF LEN(Minute(myTime))= 1 THEN

        strMinute = "0" & Minute(myTime)

    ELSE

        strMinute = Minute(myTime)

    END IF

    strSecond = Second(myTime)

    IF LEN(Minute(myTime))= 1 THEN

        strSecond = "0" & Second(myTime)

    ELSE

        strSecond = Second(myTime)

    END IF

    cTime = strHour & "-" & strMinute & "-" & strSecond

END FUNCTION

sub exportExel

set obj = ActiveDocument.GetSheetObject("CH05")

set objExcel = CreateObject("Excel.Application")

objExcel.Visible = False

objExcel.Workbooks.Add

Set ASheet = objExcel.ActiveSheet

ASheet.Application.DisplayAlerts = False

ASheet.Range("A1").Select

obj.CopyTableToClipboard true

ASheet.Paste

ASheet.SaveAs "D:\Account_A1_" & cDate(DATE) & "_" & cTime(Now) & ".xlsx"

objExcel.Quit

end sub

I have found this macro for password protection and trying to incorporate this into my macro but unable to do it successfully.

"

Function SaveWorkbookAsPasswordProtected

ActiveWorkbook.SaveAs Filename:="...\protected.xlsx", Password:="1234", WriteResPassword:="1234"

End Function

"

Please help me on this.

Thanks

Nihal

[nihalbuddy09]

Thank you Marcus.

How can I use this variable in my VBscript please, so that I can run the macro and create the excel file when this condition is successful.

I found these examples but not sure how to use it in my macro.

"Set Test = Activedocument.Variables("Test")

MsgBox Test.GetContent.String"

"SUB AAA

set v = ActiveDocument.GetVariable("vvv")

v.SetContent "999",true

msgbox("Value Output " & v.GetContent.String)

END SUB "

-Nihal

[marcus_sommer]

Try it with:

set v = ActiveDocument.Variables("vISQVReloaded_TMP").GetContent.String

- Marcus

[nihalbuddy09]

Sorry, it gives me an error as Object required: '[string: "0"]'

I have tried to debug this error but could not found anything yet, as of now I set this variable at the beginning of my macro and also tried setting this within the sub.

I am not much familiar with macros could you please advise where I am doing wrong.

- Nihal

[marcus_sommer]

Try it without the set statement or change the call slightly - this means either:

v = ActiveDocument.Variables("vISQVReloaded_TMP").GetContent.String

msgbox v

or

set v = ActiveDocument.Variables("vISQVReloaded_TMP")

vv = v.GetContent.String

msgbox vv

- Marcus

[nihalbuddy09]

set v = ActiveDocument.Variables("vISQVReloaded_TMP")

vv = v.GetContent.String

If I call only first two lines then it works and I see the 'Trigger Error' as expected. But unfortunately the output excel file still getting created.

For example:

let vISQVRELOADED_TMP=  "Either '0' or '-1' ";

if '$(vISQVRELOADED_TMP)'=0 then

SET TriggerError;

else

errormode=0;

If vISQVRELOADED_TMP = 0 then it gives the result as 'TriggerError' and the macro should not create the excel file on the folder.

If vISQVRELOADED_TMP = -1 then it will run the macro script and this should create the excel file on the folder.

But at this point of time after I call the variable in my macro, in both scenarios the excel file is getting created which we don't wanted.

Any other suggestions to achieve this in macro please and thanks a lot for your time.

- Nihal

[marcus_sommer]

You wrapped the variable with single-quotes which defines the content as string. This means it couldn't be compared against a number. The already above suggested way of:

let vISQVReloaded_TMP = floor(ReloadTime()) = Today();

if $(vISQVReloaded_TMP) then

...

should work.

- Marcus

[nihalbuddy09]

Hi Marcus,

I was just displaying it for example purpose only actually I am not using the single-quotes in variable. Your suggested condition is working fine.

How can I stop creating the excel file when this condition shows 'Trigger error'.

- Nihal

[marcus_sommer · Accepted solution]

Do I understand it right that you are using an OnPostReload trigger for your export-routine? Then you need to transfer your condition also within the macro like:

sub export

if var = true then

     export

else

     nothing

end if

end sub

Another way might be just to break the script with a artificial script error like:

if var = false then

     I'm an error statement on which the script will break ...

else

     normal loading

end if

- Marcus

[nihalbuddy09]

Thanks a lot Marcus for your great suggestions and support towards my issue.Macro is perfectly working now...

Have a great day..

Regards,

Nihal