Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

nihalbuddy09
Contributor II

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

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

1 Solution

Accepted Solutions

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

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

18 Replies
franky_h79
Honored Contributor

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

First:

Replace this line:

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


with:

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


Second:

No clue, Maybe someone else can tell you whats wrong with your conditional reload!


nihalbuddy09
Contributor II

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

Wow.. It did worked. Thank you very much Frank.

Could you please advise me how to set my condition in this macro.

Regards,

Nihal

nihalbuddy09
Contributor II

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

Sorry It's my bad, I have just noticed your attachment. I will check on this now.

Thank you

franky_h79
Honored Contributor

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

Sorry,No clue,

Maybe someone else can tell you whats wrong with your conditional reload!

franky_h79
Honored Contributor

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

i guess my attachement is not what you are looking for. I did not exactly read your post :-)

nihalbuddy09
Contributor II

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

Yes you are right. I hope that someone else will guide me how to set my condition.

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

You couldn't grab a table-value in this way else you will need peek() for it. Beside this it's not necessary to create the formatting-stuff within the variable else you could do the check directly within the if-statement like:

Condition:

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

ISQVRELOADED:

LOAD $(vISQVReloaded_TMP) as ISQVRELOADED_TMP

autogenerate(1);

if floor(ReloadTime()) = Today() then

SET TriggerError;

else

errormode=0;

...

- Marcus

nihalbuddy09
Contributor II

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

Sorry Marcus, I was not clear on my requirement in my original post.

Actually, we were using that condition in QV and calling the variable(vISQVReloaded_TMP) in NPrinting task. Based on this condition, NPrinting task will create the report and save the file. If the condition doesn’t met then NPrinting task won’t create the report.

Due to the increase in file size, from last couple of days we are unable to create the report by using NPrinting task, and the reason we found is that NPrinting 16 is only in 32 bit. Therefore it can only have 4 GB of RAM.

To overcome the present situation, we are using this macro to save the file by just reloading the application through NPrinting reload task. Now we stuck how to set and call that same condition in this macro, so that it will work how it was before.

Please advise if there are any conditions that will set in my scenario. Thank you for your time towards this issue.

- Nihal

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

If you need the variable for further purposes than you could keep it, for example in this way:

Condition:

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

ISQVRELOADED:

LOAD $(vISQVReloaded_TMP) as ISQVRELOADED_TMP

autogenerate(1);

if $(vISQVReloaded_TMP) then

SET TriggerError;

else

errormode=0;

...

- Marcus