Visual Basic Script and loop with set analysis

Mon, 08 Aug 2011 14:03:08 GMT

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

Re: Visual Basic Script and loop with set analysis

Tue, 09 Aug 2011 07:23:44 GMT

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?

Re: Visual Basic Script and loop with set analysis

Fri, 12 Aug 2011 14:38:49 GMT

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

Thanks to all in advance.

topic Visual Basic Script and loop with set analysis in QlikView

Visual Basic Script and loop with set analysis

Re: Visual Basic Script and loop with set analysis

Re: Visual Basic Script and loop with set analysis