Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
first of all thanks in advance for your reply.
I have this problem, i made (using something of already existing) a script that export a single document in excel, and than it send a mail with attachment. ( It's works : D )
But I have same problems when i try to change and improve my script.
In particular: at the moment i want to iterate in same field ("LOCATION", there are 15 locations), set an other field with a set analysis like this:
ActiveDocument.GetField("POST_DATE").Select _
ActiveDocument.Evaluate("=max({<LOCATION = {'TRIAL'}>}POST_DATE)")
the problem is that when i set second field (POST_DATE), first selection in LOCATION disappear, so my export become wrong (see code): (
Second problem is that I would like to reuse the file filePath = "C:\Test.xls" and i want write all exports in different sheets, it's possible using variable to naming sheets?
Also for this set analysis:
ActiveDocument.Evaluate("=max({<LOCATION = {'TRIAL'}>}POST_DATE)") i need to substitute {'TRIAL'} with some variables...
Thanks in advance for any suggests
This code works:
Sub SendGMail()
' Object creation
Set objMsg = CreateObject("CDO.Message")
Set msgConf = CreateObject("CDO.Configuration")
' Server Configuration
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "username@gmail.com"
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 1
msgConf.Fields.Update
'inizio script creazione excel può essere fatto a parte
'aggiunta delle righe per cancellare il file se esiste
Dim fs, filespec
filespec = "C:\Test.xls"
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(filespec) = True Then
fs.DeleteFile filespec
Set fs = Nothing
End If
'Set the path where the excel will be saved
filePath = "C:\Test.xls"
'Create the Excel spreadsheet
Set excelFile = CreateObject("Excel.Application")
excelFile.Visible = true
'Create the WorkBook
Set curWorkBook = excelFile.WorkBooks.Add
'Create the Sheet
Set curSheet = curWorkBook.WorkSheets(1)
ActiveDocument.GetField("LOCATION").Select _
ActiveDocument.Evaluate("TRIAL")
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.GetField("POST_DATE").Select _
ActiveDocument.Evaluate("=max({<LOCATION = {'TRIAL'}>}POST_DATE)")
ActiveDocument.GetApplication.WaitForIdle
'Get the chart we want to export
Set tableToExport = ActiveDocument.GetSheetObject("CH05")
Set chartProperties = tableToExport.GetProperties
tableToExport.CopyTableToClipboard true
'Get the caption
chartCaption = tableToExport.GetCaption.Name.v
'MsgBox chartCaption
'Set the first cell with the caption
curSheet.Range("A1") = chartCaption
curSheet.Paste curSheet.Range("A2")
excelFile.Visible = true
'Save the file and quit excel
curWorkBook.SaveAs filePath
curWorkBook.Close
excelFile.Quit
'Cleanup
Set curWorkBook = nothing
Set excelFile = nothing
'fine script creazione excel
objMsg.To = "to@destination.it"
objMsg.From = "PresidentObama@usa.us"
objMsg.Subject = "Ordini creati con delivery date < submit date"
objMsg.HTMLBody = "Estrazione relativa al " & Date() & " : ordini con deliveryDate < submit date "
objMsg.Sender = "Igor Valle"
objMsg.AddAttachment "C:\Test.xls"
Set objMsg.Configuration = msgConf
' Send
objMsg.Send
' Clear
Set objMsg = nothing
Set msgConf = nothing
End Sub
A small example (i think it's a bug of qlikview ).
When I select (using a button, instead of vb script) a field (location) an other one (date), an after change selection using an other button ( other location, other date), it doesn't work.
It works only when I use a intermediate button how delete all active selections.
This is similar to what happens also in vb script...
Is it a bug in your opinion? Any way to modify my vb script and make it work?
Any workaround for bug in my attachment or suggest regarding my first post?
Thanks to all in advance.