topic Export to Excel through macro in qlickview 9 in QlikView

Export to Excel through macro in qlickview 9

Tue, 15 Sep 2009 17:21:45 GMT

Previously we are using Qlikview 7.5 and able to send mail through macro, but in Qlikview 9 we are getting error

obj.CopyTableToClipboard true

in macro, kindly help it out. Code is written below:-

function SheetIt(ExcelDoc,SheetName,ChartName,Category)
ExcelDoc.Sheets(SheetName).activate
ExcelDoc.Sheets(SheetName).activate
' msgbox SheetName
ExcelDoc.Sheets(SheetName).activate
set obj = ActiveDocument.getsheetobject(ChartName)
obj.CopyTableToClipboard true
ExcelDoc.Sheets(SheetName).paste
ExcelDoc.Sheets(SheetName).cells.select
ExcelDoc.Sheets(SheetName).cells.mergecells = false
ExcelDoc.Sheets(SheetName).Cells.EntireRow.RowHeight = 12.75
ExcelDoc.Sheets(SheetName).Cells.EntireColumn.AutoFit
'msgbox "Category " & Category
ExcelDoc.Sheets(SheetName).name = Category
end function

Export to Excel through macro in qlickview 9

Thu, 17 Sep 2009 01:04:10 GMT

instruction

obj.CopyTableToClipboard true
obj.CopyBitmapToClipboard

are creating problems, stop hanging execution of the macro.

Sulucion:

To make way for the execution have put the MsgBox (), which you comment on, if this is uncomment the macro generates runs smoothly, but as it is annoying to be solving the message rremplasala by:


ActiveDocument.ShowPopup idObj, 2000

Re:Export to Excel through macro in qlickview 9

Thu, 17 Sep 2009 11:28:36 GMT

Kindly elaborate where i have to put the code

I have uncomment the msgbox also, but after clicking on popup still stuck at same error - obj.CopyTableToClipboard true

Export to Excel through macro in qlickview 9

biester — Thu, 17 Sep 2009 12:23:30 GMT

Hi,

I enclosed a demo with a macro that should do what you want - I modified it a little, but it works (to test it, excel file C:\test\test.xls with at least one sheet named "OldSheetName" is required). It does NOT add any worksheets of course, but that was also not proposed by your code fragment.

So on the whole the thing should work. Of course, sometimes in QV9, when a macro isn't executing correctly, it's necessary to put the mystical "WaitForIdle" here and there - but noone could still tell me when and where (Support: once told me "every time when a sheet object is accessed").

Perhaps that helps,

Rgds,
Joachim

Export to Excel through macro in qlickview 9

Thu, 17 Sep 2009 13:01:20 GMT

Thanks for your valuable reply,We are creating five sheets and then mail it through macro, i am putting full code at bottom

sub test2

set XLApp = CreateObject("Excel.Application")
XLApp.Visible = False

set XLDoc = XLApp.Workbooks.Add

dim a,text,tm
a=Array("CH18","CH14","CH26","CH30","CH31") '"CH27")

for i = 0 to 4
b=a(i)
m = i
m = i + 1

if b="CH18" then
text = "DailyTracker"
else if b="CH14" then
text = "AR"
else if b="CH26" then
text = "ValSales"
else if b="CH30" then 'CH27
text = "QtySales"
else if b="CH31" then 'CH27
text = "CustomerQtySales"
end if
end if
end if
end if
end if
' msgbox text

SheetIt XLDoc,"Sheet" &m,b,text

NEXT
tm=second(time())
strFile = "C:\ToSent\" & getVariable("User1") &tm & ".xls"

XLDoc.SaveAs strfile
XLDoc.Close
XLApp.Quit

sendMail strFile
rem ** let QV sleep for 10 seconds **
ActiveDocument.GetApplication.Sleep 10000

'msgbox "fordelete"
' msgbox strFile
deleteReport strFile
ActiveDocument.Fields("Commonterritory").Clear

end sub

function SheetIt(ExcelDoc,SheetName,ChartName,Category)
ExcelDoc.Sheets(SheetName).activate
ExcelDoc.Sheets(SheetName).activate
msgbox SheetName
ExcelDoc.Sheets(SheetName).activate
set obj = ActiveDocument.getsheetobject(ChartName)

rem ** let QV sleep for 10 seconds **
ActiveDocument.GetApplication.Sleep 10000

obj.CopyTableToClipboard true

ExcelDoc.Sheets(SheetName).paste
ExcelDoc.Sheets(SheetName).cells.select
ExcelDoc.Sheets(SheetName).cells.mergecells = false
ExcelDoc.Sheets(SheetName).Cells.EntireRow.RowHeight = 12.75
ExcelDoc.Sheets(SheetName).Cells.EntireColumn.AutoFit
msgbox "Category " & Category
ExcelDoc.Sheets(SheetName).name = Category
end function

function sendMail(reportFiles)
'msgbox reportFiles
Dim objEmail
Dim strMailTo

Const cdoSendUsingPort = 2 ' Send the message using SMTP
'Const cdoBasicAuth = 1 ' Clear-text authentication
Const cdoTimeout = 60 ' Timeout for SMTP in seconds
'Const cdoPort = 25 ' Port number for SMTP

Const cdoNTLM = 2

'Const cdoDSNSuccessFailOrDelay = 14 'Success, failure or delay

'Get Selected values from field "mailto"
Set objSelected = ActiveDocument.Fields("mailto").GetSelectedValues

if objSelected.Count = 0 then ' Nothing Selected
'msgbox ("No e-mail reciepient selected")
exit function
else
'Sending mail
Set objEmail = CreateObject("CDO.Message")
Set objConf = objEmail.Configuration
Set objFlds = objConf.Fields

With objFlds
'---------------------------------------------------------------------
' SMTP server details
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = getVariable("mailServer")
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoNTLM
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = getVariable("SMTPport")
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = cdoTimeout
.Update
'---------------------------------------------------------------------
End With

'For i = 0 to objSelected.Count-1 ' create mailTo list
' strMailTo = strMailTo & objSelected.item(i).Text & ";"
' msgbox "in for loop" & strMailTo
'next
strMailTo = objSelected.item(i).Text

'strMailTo = left(strMailTo,len(strMailTo)-1) ' remove the last ; in list
'msgbox strMailTo
objEmail.To = strMailTo
objEmail.From = """QlikView Admin"" <Qlik@piind.com>"
objEmail.Subject = getVariable("mailSubject")
objEmail.TextBody = getVariable("Body")

' for each item in reportFiles ' Add selected reports to mail
' if item <>"" then
'msgbox "befire attachemnt" & reportFiles
objEmail.AddAttachment reportFiles
' end if
' next
objEmail.Send

Set objFlds = Nothing
Set objConf = Nothing
Set objEmail = Nothing
end if
rem ** let QV sleep for 10 seconds **
'ActiveDocument.GetApplication.Sleep 10000

'msgbox ("Mail Sent")

end function

function deleteReport(rFile)
set oFile = createObject("Scripting.FileSystemObject")

currentStatus = oFile.FileExists(rFile)

if currentStatus = true then
oFile.DeleteFile(rFile)
end if
set oFile = Nothing
end function

function getVariable(varName)

set v = ActiveDocument.Variables(varName)
getVariable = v.GetContent.String
end function

function getText(obj)
set mytext = ActiveDocument.GetSheetObject(obj)
prop = mytext.GetProperties
getText = prop.Layout.Text.v
end function

sub selectcuser
ActiveDocument.Save
'set v2 = ActiveDocument.Variables("test")
'ActiveDocument.Fields("Email").Select v2.GetContent.String

'if tym > "08:30" and tym < "09:00" then
Set Euser = ActiveDocument.Fields("Email").GetPossibleValues

For l=0 to Euser.Count -1
'msgbox Euser.Item(l).Text
ActiveDocument.Fields("mailto").Select Euser.Item(l).Text
'Select v2.GetContent.String
'Select Euser.Item(l).Text
'
ActiveDocument.Fields("Email").Select Euser.Item(l).Text
'Select Euser.Item(l).Text
'Select Euser.Item(l).Text
call test2
Next
'end if

end sub

Export to Excel through macro in qlickview 9

biester — Thu, 17 Sep 2009 13:33:55 GMT

Hi,

the full code would have been helpful earlier, but at first quick sight I would say it should be ok and working (though I wonder why you activate the sheet thrice).

But try to replace the "Sleep" with ActiveDocument.GetApplication.WairForIdle

Rgds,
Joachim

Export to Excel through macro in qlickview 9

Thu, 17 Sep 2009 13:59:05 GMT

with ActiveDocument.GetApplication.WaitFor Idle

giving error

Object doesn't support this property or method: 'ActiveDocument.GetApplication.WairForIdle'

Rest of the code working well with 7.5 ver but on 9 its giving error-- is some memory issue or something else can cause

Same code with other files are wkg but there table data is very less.

Export to Excel through macro in qlickview 9

biester — Thu, 17 Sep 2009 14:09:48 GMT

I can hardly believe that Sleep worked and ActiveDocument.GetApplication.WaitForIdle does not work! Did you really type it correctly WaitForIdle and NOT as in your post: WaitFor Idle ?

Can't imagine that it's a memory issue, but not impossible, 9.0 is buggy enough. I'd hand it to the support.

Rgds,
Joachim

Export to Excel through macro in qlickview 9

Thu, 17 Sep 2009 15:20:44 GMT

ya, i type it correctly

Export to Excel through macro in qlickview 9

biester — Thu, 17 Sep 2009 15:30:08 GMT

Well. I'm out of ideas now and it's hard to guess without the whole app. As said I would give it to the support.. Perhaps someone else here has a heureka ..

Rgds,
Joachim

Export to Excel through macro in qlickview 9

Thu, 17 Sep 2009 19:05:12 GMT

how to add attachment to reply qvw report

Export to Excel through macro in qlickview 9

biester — Thu, 17 Sep 2009 19:09:05 GMT

If you mean you want to add an attachment here: you must "Reply" (not possible with Quick Reply) and can add an attachment on the OptionsTab there.

Rgds,
Joachim

Export to Excel through macro in qlickview 9

Fri, 18 Sep 2009 11:34:18 GMT

I am attaching the qvw file for same.

Its picks data from various sourse , generate excel file and mail

Its required c:\ToSent folder for same

Export to Excel through macro in qlickview 9

Tue, 22 Sep 2009 14:00:07 GMT

Any solution

Export to Excel through macro in qlickview 9

Fri, 25 Sep 2009 11:40:47 GMT

I am attaching the qvw file for same.

Its picks data from various sourse , generate excel file and mail

Its required c:\ToSent folder for same

Export to Excel through macro in qlickview 9

Fri, 25 Sep 2009 16:32:03 GMT

I am attaching the qvw file for same.

Its picks data from various sourse , generate excel file and mail

Its required c:\ToSent folder for same

Export to Excel through macro in qlickview 9

Tue, 29 Sep 2009 21:50:50 GMT

You do one thing..first pay the license cost..cut copy paste dont work always...it is security issue

Export to Excel through macro in qlickview 9

Tue, 29 Sep 2009 21:52:54 GMT

Lots of programing is being done in the given code..so its better read and understand code line by line..then try to correct it..no copy paste....

Export to Excel through macro in qlickview 9

Wed, 30 Sep 2009 12:40:07 GMT

I already mentioned same file wkg with 7.5 but giving error in 9.

So its not security issue it could be memory/clipbord issue

Export to Excel through macro in qlickview 9

rajeshvaswani77 — Thu, 12 Jan 2012 11:57:55 GMT

Hi jaswinder,

Would this macro work from Publisher?

Thanks,

Rajesh Vaswani