提供大家一個簡單的滙出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
For just a few hours now, QlikView 12, the latest version of one of the world’s leading Business Intelligence platforms, has been available on the market. Besides several innovations familiar from the beta version, the software now offers additional interesting functions.
