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

[Frank_Hartmann]

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]

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]

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

Thank you

[Frank_Hartmann]

Sorry,No clue,

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

[Frank_Hartmann]

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

[nihalbuddy09]

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

[marcus_sommer]

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]

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

[marcus_sommer]

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