Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Visual Basic Script and loop with set analysis

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

' Email

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

2 Replies
Not applicable
Author

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?

Not applicable
Author

Any workaround for bug in my attachment or suggest regarding my first post?

Thanks to all in advance.