Copy to clipboard (excel macro) not functioning after upgrade to Publisher

[avantime]

Hi,

After updating the server with a Publisher license and moving all Excel generating tasks from Windows Task Scheduler to Supporting Tasks in QMC the export is failing due to the fact that the clipboard is not functioning.

For example, if I copy text 'abc' in notepad, later, when the Excel files are generated, they will contain only the 'abc' string. It`s like the QMC does not have permissions to clear and populate the clipboard.

Any ideas?

[avantime]

I am using QV 12.0.20300.0 SR4

Macro code is:

sub Excel

Set WshShell = CreateObject("WScript.Shell")
WshShell.Run "cmd.exe /c echo. >NUL | clip", 0, True

filePath = ""
 
Set excelFile = CreateObject("Excel.Application")

excelFile.Visible = False
excelFile.DisplayAlerts = False

Set curWorkBook = excelFile.WorkBooks.Add

aSheetObj=Array("CH02","CH01")

for i=0 to UBound(aSheetObj)

excelFile.Sheets.Add   
Set curSheet = excelFile.ActiveSheet
curSheet.Range("A1").Select    
Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))
obj.CopyTableToClipboard True
ActiveDocument.GetApplication.WaitForIdle
curSheet.Paste
ActiveDocument.GetApplication.WaitForIdle
sCaption=obj.GetCaption.Name.v
set obj=Nothing     
curSheet.Rows("1:10000").RowHeight = 15
curSheet.Rows("1:1").Select
excelFile.Selection.Font.Bold = True       
curSheet.Cells.Select
excelFile.Selection.Columns.AutoFit 
curSheet.Range("A1").Select     
curSheet.Name=left(sCaption,30)   
set curSheet=Nothing  

next

curWorkBook.WorkSheets("Sheet1").Delete

curWorkBook.SaveAs filePath, 56
curWorkBook.Close
excelFile.Quit


Set curWorkBook = nothing
Set excelFile = nothing
    
Set objMsg = CreateObject("CDO.Message")
Set msgConf = CreateObject("CDO.Configuration")

msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = -
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = ""
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = -
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = -
msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = ""
msgConf.Fields.Update
''
objMsg.To = ""
objMsg.CC = ""
objMsg.From = ""
objMsg.Subject = ""


objMsg.HTMLBody = ""
objMsg.AddAttachment ""
objMsg.Sender = ""

Set objMsg.Configuration = msgConf

objMsg.Send

ActiveDocument.GetApplication.Sleep 5000

Set objMsg = nothing
Set msgConf = nothing

Set fso = CreateObject("Scripting.FileSystemObject")
fso.DeleteFile ""

ActiveDocument.GetApplication.Sleep 1000

ActiveDocument.GetApplication.Quit

END SUB

[jerrysvensson]

And you ran Task scheduler with the same account as QlikView publisher is running now?

[avantime]

yes

[marcus_sommer]

Is there any user logged in when this task runs. The Qlik services itself doesn't need any user to run because they are defined as a service. But that doesn't mean that any from there triggered tool runs with that user - AFAIK it doesn't else the windows default user will be taken. And this user might not have the proper access rights / configurations ...

One configuration which might impact this is the office-clipboard ... maybe there is something wrong.

If none of these hints are helping and/or you haven't the time to follow all the possibilities you could apply as workaround a writing of your tables - no copy & paste else write them cell for cell. Within the above links is also an example for this approach.

- Marcus

[peterwh]

Hello,

it's indeed very strange. How do you start the macro?

I had some problems with macros and supporting tasks after our update from 12.00SR5 to 12.20SR6. So  there are my two points, that got me stucked:

• Macro isn't executed if it's called after a Reload (open Dashboard, reload script, try to execute a simple macro => fail)
• The reload throug supporting tasks doesn't do anything, if "/r" is used (it's a bug, appearently fixed with SR8). We had to add "/nodata" to the commandline

But these problems occur from taskscheduler too...

Kind regards
Peter

[jerrysvensson]

Follow up on Marcus.
When you ran Task scheduler, was there a user logged on when the job ran?
If so, then it is the office-clipboard issue.

[avantime]

Task scheduler tasks run under Administrators user account.

It works whether I am logged in or not.