topic Re: Automation Macro in QlikView

Automation Macro

Mon, 04 May 2015 09:19:03 GMT

im having trouble trying to automate a report emailing solution.

What i currently have is a Customer field "CustomerID' and the associated Customer Email Field "CustomerEmail".

I need a macro that will for loop thru CustomerID and for every possible selection export specific table to excel file, attach it to email and send it off to CustomerEmail adress.

Any help?

The following script is the one i use to export each file to its own:

What happens is :

Variable EmailChart = my chart ID object that i export

Variable EmailXLSpath = path where all my excel files is dumped

This will loop thru CustomerID, export each object to CustomerID's name as a Excel file.

After this script is run it must attach each file to its own email (draft if possible) with the "to" adress of CustomerEmail

FUNCTION getVariable(varName)
set v = ActiveDocument.Variables(varName)
getVariable = v.GetContent.String
END FUNCTION   
   
Sub ExportXLS
    pathcorp = getVariable("EmailXLSpath")
    set corp = ActiveDocument.GetSheetObject(getVariable("EmailChart"))
    ActiveDocument.Fields("CustomerID").Clear
    set val=ActiveDocument.Fields("CustomerID").GetPossibleValues
    set XLApp = CreateObject("EXCEL.application")
    XLApp.Visible = FALSE
    set XLDoc = XLApp.Workbooks.Add
   
    for i=0 to val.Count-1
    set MyTable = ActiveDocument.GetSheetObject(getVariable("EmailChart"))
    path = getVariable("EmailXLSpath")
    ActiveDocument.Fields("CustomerID").Select val.Item(i).Text
    MyTable.ExportBiff  path & val.Item(i).Text &".xls"
   
   
     next

Set XLApp = Nothing
Set Table = Nothing

Msgbox "Exported Sucessfully"
end sub

Re: Automation Macro

marcus_sommer — Mon, 04 May 2015 10:13:25 GMT

Have a look here: Automated Excel Export and Email Macro.

- Marcus

Re: Automation Macro

Mon, 04 May 2015 10:27:15 GMT

Hi Marcus, thanx for the link i have seen that one before and altho it helped me it wont solve my problem as the "to email adress" is fixed, i need one that can "autocomplete" the "to adress" with my CustomerEmail field

Re: Automation Macro

marcus_sommer — Mon, 04 May 2015 10:50:14 GMT

In general you could use the same methods like above used within the export-code - reading/selecting fields/listboxes and variables to grab the email.

But I like to use a tablebox in such cases then all values in a row are already connected. Here a short snippet from such a logic:

Set Table_One = ActiveDocument.GetSheetObject("TB12") 'Variable initialisieren --> Listbox der Gebiete
Column_One = Table_One.GetColumnCount 'Variable Wert zuweisen --> Anzahl Spalten der Listbox auslesen
Row_One = Table_One.GetRowCount ''Variable Wert zuweisen --> Anzahl Zeilen der Listbox auslesen
Set ZellMatrix_One = Table_One.GetCells2(0,0,Column_One,Row_One) ' Variable initialisieren --> Zellschnittpunkt zuweisen

'erneutes Auslesen der tabellenbox "Versand-Matrix"
For i_Row = 1 to Row_One - 1 '0 to Row_One - 1
    For i_Col = 4 to 4 '0 to Column_One - 1
        Zeit_2 = Now()
           send_vk = (ZellMatrix_One(i_Row)(i_Col -4).Text) 'Variable Wert zuweisen --> ausgelesenes Vertriebskanal
           send_be_One = (ZellMatrix_One(i_Row)(i_Col -3).Text) 'Variable Wert zuweisen --> Struktur-Bereich (Variante 1)
           send_be_Two = (ZellMatrix_One(i_Row)(i_Col - 2).Text) 'Variable Wert zuweisen --> Struktur-Bereich (Variante 2)
           send_art = (ZellMatrix_One(i_Row)(i_Col - 1).Text) 'Variable Wert zuweisen --> Berichtsart
           TB = (ZellMatrix_One(i_Row)(i_Col).Text) 'Variable Wert zuweisen --> Tabellenbox-ID (enthät die Mailadressen)

- Marcus