QlikView 語法攻略 - export Excel and Email

提供大家一個簡單的滙出EXCEL 加選擇條件, 並且可以EMAIL 的Marco 語法.
下方語法是使用VBScript , 要注意要選擇System Access 跟Allow system access

另外,如果你不是使用button 呼叫Marco時, 而是Reload 後就要執行Marco 的話,
原則上無法背景處理,及QMC 上排程.
所以要另外下指令,而指令的差異是/r 變成 /l  指的是 Open, Reload *
所以注意我在MARCO 裡加了ActiveDocument.Save  
                                         ActiveDocument.GetApplication.Quit 
讓APP可以關閉

 請參考指令方法Command line execute Reload / partial Reload 指令更新資料

Sub  ExportM1023
 
 
     set f = ActiveDocument.Fields("User.BusinessUnitIdName") 'User.BusinessUnitIdName 是欄位名稱
     f.Select    "業務二處"  
     f.ToggleSelect "業務三處"   '多選擇資料時用
   
set obj = ActiveDocument.GetSheetObject("CH01")
obj.ExportBiff "c:\temp_del\DataM1023.xls"  '暫存的excel 檔案
    'MsgBox("Export done")
 End Sub





Sub mailM1023
Dim objEmail

Const cdoSendUsingPort = 2 ' Send the message using SMTP
Const cdoBasicAuth = 1     ' Clear-text authentication
Const cdoTimeout = 60       ' Timeout for SMTP in seconds

     mailServer = "yourMailserver" ' inpurt your MailServer
     SMTPport = 25
     mailusername = "yourMailUser" ' inpurt your MailServer user 認證用
     mailpassword = "Password"     ' inpurt your Mail  user password 認證用

     mailto = "xxx@mail.com"  ' inpurt who you want to email
   
     mailSubject = "QlikView mailSubject"  ' inpurt mailSubject
       mailBody = "***** This is an automated message from qlikview  ******"  & Chr(13) & Chr(10)
   
Set objEmail = CreateObject("CDO.Message")
Set objConf = objEmail.Configuration
Set objFlds = objConf.Fields

With objFlds
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = mailServer
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SMTPport
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = cdoTimeout
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasicAuth
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = mailusername
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = mailpassword
    .Update
End With

objEmail.To = mailto
objEmail.From = mailusername
objEmail.Subject = mailSubject
objEmail.TextBody = mailBody
objEmail.AddAttachment "C:\temp_del\DataM1023.xls" 'mail 出暫存的excel 檔案
objEmail.Send

'MsgBox("Message Sent")
Set objFlds = Nothing
Set objConf = Nothing
Set objEmail = Nothing

ActiveDocument.Save  
ActiveDocument.GetApplication.Quit   

END Sub

 其它類似語法  滙出成TXT 檔 export txt file format from MARCO