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

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

nihalbuddy09 — Wed, 25 Nov 2020 16:16:04 GMT

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

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

Frank_Hartmann — Thu, 27 Sep 2018 13:18:29 GMT

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!

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

nihalbuddy09 — Thu, 27 Sep 2018 13:34:35 GMT

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

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

Regards,

Nihal

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

nihalbuddy09 — Thu, 27 Sep 2018 13:37:09 GMT

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

Thank you

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

Frank_Hartmann — Thu, 27 Sep 2018 13:37:14 GMT

Sorry,No clue,

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

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

Frank_Hartmann — Thu, 27 Sep 2018 13:37:59 GMT

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

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

nihalbuddy09 — Thu, 27 Sep 2018 13:52:15 GMT

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

marcus_sommer — Thu, 27 Sep 2018 14:11:03 GMT

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

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

nihalbuddy09 — Fri, 28 Sep 2018 13:44:53 GMT

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

marcus_sommer — Fri, 28 Sep 2018 14:12:16 GMT

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

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

nihalbuddy09 — Fri, 28 Sep 2018 17:16:40 GMT

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

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

marcus_sommer — Sun, 30 Sep 2018 12:41:59 GMT

Try it with:

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

- Marcus

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

nihalbuddy09 — Wed, 03 Oct 2018 13:26:57 GMT

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

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

marcus_sommer — Wed, 03 Oct 2018 15:50:43 GMT

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

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

msgbox v

set v = ActiveDocument.Variables("vISQVReloaded_TMP")

vv = v.GetContent.String

msgbox vv

- Marcus

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

nihalbuddy09 — Thu, 04 Oct 2018 11:45:17 GMT

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

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

marcus_sommer — Thu, 04 Oct 2018 12:34:26 GMT

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

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

nihalbuddy09 — Thu, 04 Oct 2018 14:58:34 GMT

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

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

marcus_sommer — Thu, 04 Oct 2018 15:17:15 GMT

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

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

nihalbuddy09 — Sat, 06 Oct 2018 07:01:40 GMT

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

Have a great day..

Regards,

Nihal