topic Re: Exportar varias tablas a un archivo html in QlikView

Exportar varias tablas a un archivo html

Wed, 25 Feb 2015 12:52:06 GMT

Hola,

comparto con todos el código que usar para mandar mails con las tablas pegadas en el cuerpo del mensaje.

Lo que necesito es que en el html se peguen varias tablas, no solo una, y a poder ser poder insertar el texto que yo quiera entre las tablas.

De momento solo consigo poner una tabla pero necesita unas 4 o 5.

Gracias!!!

MI CODIGO:

'// ****************************************************************

'// Enviador Mails Pegados Cuerpo

'// ****************************************************************

sub SendMail

if Time()>TimeValue("10:15:00") and Time()<TimeValue("21:30:00")Then

' Object creation

Set objMsg = CreateObject("CDO.Message")

Set msgConf = CreateObject("CDO.Configuration")

strDate = CDate(Date)

set cs = ActiveDocument.GetSheetObject("CH80")

cs.ExportHtml "C:\Directory\Pruebas\informe.html"

objMsg.CreateMHTMLBody "file://C:\Directory\Pruebas\informe.html"

' Server Configuration

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "server***********"

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "mimai********l"

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "pass******"

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 1

msgConf.Fields.Update

' Email

objMsg.To = "mmail***********"

objMsg.From = "mail**********.com"

objMsg.Subject = "Reporte " & Now()

objMsg.Sender = "Reportes"

Set objMsg.Configuration = msgConf

' Send

objMsg.Send

' Clear

Set objMsg = nothing

Set msgConf = nothing

end if

end sub

Re: Exportar varias tablas a un archivo html

Anonymous — Wed, 25 Feb 2015 16:58:18 GMT

Create a cycle.

Re: Exportar varias tablas a un archivo html

Thu, 26 Feb 2015 06:59:26 GMT

Con esto ya lo soluciono todo....

Re: Exportar varias tablas a un archivo html

marcus_sommer — Thu, 26 Feb 2015 12:33:29 GMT

Directly with qv-functionalities it's only possible to export one object after another to html. So it seems that the only way is to manipulate the html. Maybe with the following main-logic:

- export all your wished objects to html (in a certain order?)

- open the first one (as master)

set fso = CreateObject("Scripting.FileSystemObject")

set HtmlFile = fso.OpenTextFile("D:\Path\File.html", 2, true)

HtmlContent= HtmlFile.readall

- split this by  into two pieces (vStart & vEnd)

- open the next file

- cut the area from  to  into a variable (vBetween)

- put the variables together and write it in this or a new file + save & close

HtmlFile.write vStart & vBetween & vEnd

HtmlFile.close

It's not very beautiful but I hope it worked.

- Marcus

Re: Exportar varias tablas a un archivo html

Thu, 26 Feb 2015 12:39:31 GMT

Thanks but.... Can you write it in my code?

Re: Exportar varias tablas a un archivo html

marcus_sommer — Thu, 26 Feb 2015 13:04:40 GMT

set cs = ActiveDocument.GetSheetObject("CH80")

cs.ExportHtml "C:\Directory\Pruebas\informe.html"

.... your another objects ... maybe exported in a loop ...

set fso = CreateObject("Scripting.FileSystemObject")

set HtmlFile = fso.OpenTextFile("D:\Path\File.html", 2, true)

HtmlContent= HtmlFile.readall

split HtmlContent into the variables per left/right/mid with instr(HtmlContent, "")

open the next export-file and cut this table with the same method

put all together and write it within the txt-file and save it and then

objMsg.CreateMHTMLBody "file://C:\Directory\Pruebas\informe.html"

- Marcus

Re: Exportar varias tablas a un archivo html

Thu, 26 Feb 2015 13:26:53 GMT

'// ****************************************************************

'// Enviador Mails Pegados Cuerpo

'// ****************************************************************

sub SendMail

if Time()>TimeValue("10:15:00") and Time()<TimeValue("21:30:00")Then

' Object creation

Set objMsg = CreateObject("CDO.Message")

Set msgConf = CreateObject("CDO.Configuration")

strDate = CDate(Date)

set cs = ActiveDocument.GetSheetObject("CH80")

cs.ExportHtml "C:\Directory\Pruebas\informe.html"

set fso = CreateObject("Scripting.FileSystemObject")

set HtmlFile = fso.OpenTextFile("D:\Path\File.html", 2, true)

HtmlContent= HtmlFile.readall

objMsg.CreateMHTMLBody "file://C:\Directory\Pruebas\informe.html"

' Server Configuration

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "server***********"

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "mimai********l"

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "pass******"

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 1

msgConf.Fields.Update

' Email

objMsg.To = "mmail***********"

objMsg.From = "mail**********.com"

objMsg.Subject = "Reporte " & Now()

objMsg.Sender = "Reportes"

Set objMsg.Configuration = msgConf

' Send

objMsg.Send

' Clear

Set objMsg = nothing

Set msgConf = nothing

end if

end sub

Can you continue? i don't know how i ican do it.

Thank you very much

Re: Exportar varias tablas a un archivo html

marcus_sommer — Fri, 27 Feb 2015 09:58:17 GMT

Not very nice - but it worked.

sub CreateHtmlBodyMail

dim doc, obj, objArr, file, path

dim i

dim vStart, vBetween, vEnd, TempHTML, HtmlContent, fso

set doc = ActiveDocument

path = "D:\"

objArr = array("TB01", "CH01", "CS01")

set fso = CreateObject("Scripting.FileSystemObject")

for i = 0 to ubound(objArr)

set obj = doc.GetSheetObject(objArr(i))

file = "HTML " & i

obj.ExportHtml path & file & ".html"

doc.GetApplication.Sleep 500

set TempHTML = fso.OpenTextFile(path & file & ".html", 1, true)

HtmlContent = TempHTML.readall

if i = 0 then

vStart = mid(HtmlContent, 1, instr(HtmlContent, "")) & chr(10)

vEnd = chr(10) & mid(HtmlContent, instr(HtmlContent, "") + 18)

elseif i > 0 then

vBetween = vBetween & mid(HtmlContent, instr(HtmlContent, ""), instr(HtmlContent, "") + 18) & chr(10) & chr(10)

end if

TempHTML.close

HtmlContent = vStart & vBetween & vEnd

set TempHTML = fso.OpenTextFile(path & "Master.html", 2, true)

TempHTML.write HtmlContent

TempHTML.close

Dim objEmail, config

Set objEmail = CreateObject("CDO.Message")

config = "http://schemas.microsoft.com/cdo/configuration/"

objEmail.Configuration.Fields.Item(config & "sendusing") = 2 'http://msdn.microsoft.com/en-us/library/exchange/ms873037%28v=exchg.65%29.aspx

objEmail.Configuration.Fields.Item(config & "smtpserver") = "YourMailServer" 'Name or IP of Remote SMTP Server

objEmail.Configuration.Fields.Item(config & "smtpserverport") = 25 'Server port (typically 25)

objEmail.Configuration.Fields.Item(config & "smtpusessl") = False 'Use SSL for the connection (False or True)

objEmail.Configuration.Fields.Item(config & "smtpconnectiontimeout") = 60 'Connection Timeout in seconds (the maximum time CDO will try to establish a connection to the SMTP server)

objEmail.Configuration.Fields.Update 'End remote SMTP server configuration section

objEmail.From = "YourFrom"

objEmail.To = "YourTo"

objEmail.Subject = "E-Mail with HTML-Body with embedded QV-Objects"

objEmail.CreateMHTMLBody "file://" & path & "Master.html"

objEmail.Send

end sub

- Marcus

Re: Exportar varias tablas a un archivo html

Fri, 27 Feb 2015 10:58:46 GMT

You are the MASTER. Thank you very much

Re: Exportar varias tablas a un archivo html

Wed, 18 Mar 2015 16:49:38 GMT

Hello again!

Before writing the second table in the html file a sign "< " appears

How I can remove it?

Thanks!!!

Re: Exportar varias tablas a un archivo html

marcus_sommer — Thu, 19 Mar 2015 08:36:59 GMT

Change in the code:

if i = 0 then

vStart = mid(HtmlContent, 1, instr(HtmlContent, "")) & chr(10)

if i = 0 then

vStart = mid(HtmlContent, 1, instr(HtmlContent, "") - 1) & chr(10)

- Marcus

Re: Exportar varias tablas a un archivo html

Thu, 19 Mar 2015 09:49:26 GMT

Thanks Marcus Sommer!

if i = 0 then

vStart = mid(HtmlContent, 1, instr(HtmlContent, "") + 1) & chr(10)

It works too

Re: Exportar varias tablas a un archivo html

Tue, 24 Jan 2017 00:25:59 GMT

Hi Marcus,

I'm having error when i insert charts or graphs using this macro. How do we solve this problem?

Thanks in advance

Re: Exportar varias tablas a un archivo html

marcus_sommer — Tue, 24 Jan 2017 07:33:56 GMT

What for an error and where?

- Marcus

Re: Exportar varias tablas a un archivo html

Wed, 25 Jan 2017 00:13:03 GMT

Here's the error i'm receiving when executing the macro.

It seems like it can't convert CHART / GRAPH to image and to html using the script.

- Irvin

Re: Exportar varias tablas a un archivo html

marcus_sommer — Wed, 25 Jan 2017 09:20:43 GMT

I'm not absolutely sure but I think that a graphic-chart couldn't be exported into html - if you try it manually by right-click on the object and choosing export you will get only graphic fileformats as a choice in opposite to tables which offers various fileformats including html.

If you really want to include a graphic-chart within the mail you will need to extend the macro with a logic to detect the chart-type respectively you add this as a fixed information and embed this picture then between your other content - maybe something like this:

- Marcus

Re: Exportar varias tablas a un archivo html

nesawant — Wed, 31 May 2017 09:03:36 GMT

Hi Marcus,

Is there a way to get rid of

that appear at the end of each table?

Also, I need my table captions with same background color as it appears in my dashboard.

Any inputs on this please?

Best Regards,

Neha

Re: Exportar varias tablas a un archivo html

marcus_sommer — Wed, 31 May 2017 09:43:22 GMT

I think for the first topic you will need an editor like notepad ++ to see which chars/object cause the displaying of this place-holder. Maybe the lengths of the various html-parts must be a bit adjusted.

For the second topic you need to add your formatting within the html probably with some string-functions and replacing the set formattings. If you searched with google to html-formattings you will find various sources how it could be done but it will be probably no funny work. Another way might be to include a link to a css-file within the html but this requirred a public place for the css and that the receiver of the mails are online.

- Marcus

Re: Exportar varias tablas a un archivo html

nesawant — Wed, 31 May 2017 09:53:11 GMT

Thanks Marcus.

My first question did not appear correctly.

I get No Selections at the end of every table. How can I get rid of it?

-Neha

Re: Exportar varias tablas a un archivo html

marcus_sommer — Wed, 31 May 2017 10:07:17 GMT

You could disable them within the user-properties in tab export in the bottom-left area.

- Marcus