Solved: Macro to export data to excel and send - Qlik Community

Report Inappropriate Content · ‎2016-07-01

Hello,

I am new to QlikView and want to make a macro that would automatically copy/paste data from yesterday to excel, which would then be sent to MS.Outlook both as an attachment and as a picture in the E-mail's body section.

While writing the macro and searching through forums for answers I stumbled on three roadblocks:

1. How can I make my macro check beforehand, whether yesterday's data has been loaded ? (There is one entry that is generated and could be used as a reference)

2. At the beginning of a new month, the macro should still take last month's data, how could I structure the "IF/THEN" argument to help with this problem? (e.g. check if the "Day" filter selection has only 1 day and thus the macro understands that it should take last month)

3. is it possible and, if it is, how could I paste the tables either from QV or Excel to Outlook's E-mail body section?

(Maybe there is a way to directly change the Email body through VBScript?)

Since the code is quite long, I have attached a dummy file with it.

Thank you beforehand for your invested time!

Yours faithfully,

Arturs A.

Report Inappropriate Content · ‎2016-07-07

Okay, so I managed to solve it all by myself.

1. I made a new variable to sum the contents of the data stream that is being updated and it looks something like this:

If ActiveDocument.Variables("PrevDay").GetContent.String > 0 Then

Call GenerateMailReport

else

Msgbox "Yesterday's Data not available!!"

2. Simply made a new variable that is =("CurrentDate"-1) and after a if/then analysis I prompt to use it.

set y=ActiveDocument.Fields("M1")

set m=ActiveDocument.Fields("D1")

y.Select ActiveDocument.Evaluate("Month(num(today()))")

m.Select ">= 1" & "<=" & ActiveDocument.Evaluate("num(day(today()))")

If ActiveDocument.Variables("nMonth").GetContent.String = 1 Then '<----if the number of selected days is 1, then start the magic

y.Select ActiveDocument.Variables("PrevMonth").GetContent.String '<------Check the variable that has the prev. month selected

m.Select ">= 1" & "<=" & ActiveDocument.Evaluate("num(day(today()-1))") '<------- selected the whole previous month (if there are daily selections)

3. From the numerous possibilities I decided to export QV tables as images and then add them to the e-mail's body.

Export Images:

ActiveDocument.GetSheetObject("ObjectID").Restore '<------ The table has to be open in order to be exported

ActiveDocument.GetApplication.WaitForIdle '<-------- sometimes the script is working so fast, that it does not export

ActiveDocument.GetSheetObject("ObjectID").ExportBitmapToFile "C:\Path\FileName.PNG"

ActiveDocument.GetSheetObject("ObjectID").Minimize

Add to outlook:

objMail.body = ("EmailBody")

objMail.HTMLBODY = "<HTML><BODY>"<img align=baseline border=1 hspace=2 src='C:\Path\FileName.png'>"</BODY></HTML>

However, I can't seem to be able to get the Macro to filter the last month if it has selected the first day of a new month.

Any assistance on this?

This is my current choice of script:

set y=ActiveDocument.Fields("M1")

set m=ActiveDocument.Fields("D1")

y.Select ActiveDocument.Evaluate("Month(num(today()))")

m.Select ">= 1" & "<=" & ActiveDocument.Evaluate("num(day(today()-6))") '<---- manually added to set the date at 1

If ActiveDocument.Variables("nMonth").GetContent.String = 1 Then

y.Select ActiveDocument.Evaluate("Month(num(today()-1))") '<-------- doesn't select the previous month.

View solution in original post

Report Inappropriate Content · ‎2016-07-07

Okay, so I managed to solve it all by myself.

1. I made a new variable to sum the contents of the data stream that is being updated and it looks something like this:

If ActiveDocument.Variables("PrevDay").GetContent.String > 0 Then

Call GenerateMailReport

else

Msgbox "Yesterday's Data not available!!"

2. Simply made a new variable that is =("CurrentDate"-1) and after a if/then analysis I prompt to use it.

set y=ActiveDocument.Fields("M1")

set m=ActiveDocument.Fields("D1")

y.Select ActiveDocument.Evaluate("Month(num(today()))")

m.Select ">= 1" & "<=" & ActiveDocument.Evaluate("num(day(today()))")

If ActiveDocument.Variables("nMonth").GetContent.String = 1 Then '<----if the number of selected days is 1, then start the magic

y.Select ActiveDocument.Variables("PrevMonth").GetContent.String '<------Check the variable that has the prev. month selected

m.Select ">= 1" & "<=" & ActiveDocument.Evaluate("num(day(today()-1))") '<------- selected the whole previous month (if there are daily selections)

3. From the numerous possibilities I decided to export QV tables as images and then add them to the e-mail's body.

Export Images:

ActiveDocument.GetSheetObject("ObjectID").Restore '<------ The table has to be open in order to be exported

ActiveDocument.GetApplication.WaitForIdle '<-------- sometimes the script is working so fast, that it does not export

ActiveDocument.GetSheetObject("ObjectID").ExportBitmapToFile "C:\Path\FileName.PNG"

ActiveDocument.GetSheetObject("ObjectID").Minimize

Add to outlook:

objMail.body = ("EmailBody")

objMail.HTMLBODY = "<HTML><BODY>"<img align=baseline border=1 hspace=2 src='C:\Path\FileName.png'>"</BODY></HTML>

However, I can't seem to be able to get the Macro to filter the last month if it has selected the first day of a new month.

Any assistance on this?

This is my current choice of script:

set y=ActiveDocument.Fields("M1")

set m=ActiveDocument.Fields("D1")

y.Select ActiveDocument.Evaluate("Month(num(today()))")

m.Select ">= 1" & "<=" & ActiveDocument.Evaluate("num(day(today()-6))") '<---- manually added to set the date at 1

If ActiveDocument.Variables("nMonth").GetContent.String = 1 Then

y.Select ActiveDocument.Evaluate("Month(num(today()-1))") '<-------- doesn't select the previous month.

Macro to export data to excel and send

Related Topics