Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Guidok
Contributor II
Contributor II

Macro doesn't work on postreload

Hi,

I've a macro for exporting an excel file which works fine when I run the script manually.

When I add it to a postreload trigger, it doesn't.

I've checked the document properties -- security --> Export allowed

Also under user preferences --> security --> Module and File are checked

Any idea suggestions why the macro doesn't seem to work as a postreload?

Regards,

Guido

 

35 Replies
Guidok
Contributor II
Contributor II
Author

I've checked the Can Execute External Programs:

 QV.JPG

Didn't generate the Excel file.

I've renamed the settingsfile and ran the script again. I got a message about the language settings and changed it to English and after that I got a message about allowing Macro's which I agreed and an other warning about giving access which I also excepted. Then the reload started but no Excel file.

When I run the macro manually, I get the message box and after that I get a window for running the macro. I changed the settings for only running in safe mode and then the Excel file was created.

marcus_sommer
MVP & Luminary
MVP & Luminary

Just try it again but not with the export else with the msgbox and then selection-statement. Also another check that really the right macro-routine is called from the OnPostReload trigger. Further if there other actions which are also called by opening/reloading the application remove them.

Another check is - what happens if you don't execute the batch and don't starts the macro manually in the application else just reload the app - the macro fires or not?

- Marcus

Guidok
Contributor II
Contributor II
Author

The macro works when I don't do the export. It works with the reload script and also after starting the reload manually.

So there's something with the excel export... but what?

marcus_sommer
MVP & Luminary
MVP & Luminary

There might be various reasons. At first would be a check if any new Excel instance will be started - you could see it within the taskmanager. If not your create-statement might be jammed through any security settings (it's an ActiveX feature and needs to be enabled - and there might be differences if a call comes from the UI or from a batch and further important is of course the USER which called the batch) and/or there might be any popup's which you didn't see if you set the visibility and the messages to FALSE.

Another point is your code itself - there are at least some references which look unusual for me. Means for example the paste-statement which hasn't a reference to a sheet:

objExcel.Range("A1").PasteSpecial

else only to the application. If you say but it worked manually I would repeat the above mentioned that there might be differences between an UI call and a batch call (for example in regard in early/late binding of features). I'm not an expert here but I struggled already in this regard ...

But is there something within the code Excel should show it to you (after enabling the visibility and the messages) and also an On Error Resume Next and then msgbox err.number  & " - " & err.description should give some valuable hints.

- Marcus

Surya
Creator II
Creator II

Hello..,

Before also im trying Macro.

its doesnt work on QMC ..Its working fine in Development.

 

so we need to reload by creating Batch only..its woking with Batch

Surya
Creator II
Creator II

Hello.,

sub Export
set obj1 = ActiveDocument.GetSheetObject("CH197")  //chart ID

strDate = Day(Date()) &"-"& month(date()) &"-"& Year(Date())

obj1.ExportBiff "C:\User 4\Surya\"&strDate&".xlsx"   //Path

end sub

marcus_sommer
MVP & Luminary
MVP & Luminary

The qmc doesn't execute any macros. You need to use any batches which use the desktop client to execute a macro whereby those batches may be triggered from the qmc per EXECUTE:

Run-Excel-macro-via-QMC 

- Marcus

Guidok
Contributor II
Contributor II
Author

It works now. Not sure what did the trick... maybe a reboot I did.

Brett_Bleess
Support (Former)
Support (Former)

Adding a link to an Article with some specific instructions on how to go about things for others that check this thread in the future:

OnPostReload trigger in 'batch' process setup 

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Guidok
Contributor II
Contributor II
Author

When my laptop is locked, the script starts but I get the following message:

Copy to clipboard failed

When I run it manually or when it's not locked it does work.

There are serveral exports that run, but they all look like this:

sub Aanvullen
'msgbox "startAanvullen"
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = True
Set xlDoc = objExcel.Workbooks.Add
set obj = ActiveDocument.GetSheetObject("CH02")
obj.CopyTableToClipboard true
objExcel.Worksheets(1).Columns("A:C").Numberformat = "@"
objExcel.Range("A1").PasteSpecial 1
objExcel.Worksheets(1).Rows("1:1000").RowHeight = 15
objExcel.Worksheets(1).Columns("A:C").Autofit
xlDoc.SaveAs "X:\Uitwissel\Logistieke Controles\Aanvullen.xlsx"
xlDoc.Close
end sub