topic Re: An Export to Excel Macro is not working "On Post Reload".. Can someone help please? in QlikView

An Export to Excel Macro is not working "On Post Reload".. Can someone help please?

Wed, 19 Feb 2014 13:10:47 GMT

Hi,

I am trying to auto export the data to excel with a Macro.. but the macro is not working with "on post reload".

I tried this manually and also tried with a batch file reload.. but nothing is working....

However the macro is exporting the data and storing it to the mentioned path if i am running a test in the module.

Please help... and thanks in advance..

Regards,

Shailesh

Re: An Export to Excel Macro is not working "On Post Reload".. Can someone help please?

urivera — Wed, 19 Feb 2014 13:12:51 GMT

Hi,

Try on open with a trigger in Property documents/Trigger/on Open.

Ulises

Re: An Export to Excel Macro is not working "On Post Reload".. Can someone help please?

Wed, 19 Feb 2014 13:18:37 GMT

Thanks Ulises..

But i tried that also...

and just for everyone's reference, please note that i have already tried below trouble shoots:-

Tried "onpostreload" and "onopen"
"macro override security" is checked in Security
"Module" and "file" is checked in user preference >> settings
Tried /l instead of /r in batch reload

Hope this helps!!!

Regards,

Shailesh

Re: An Export to Excel Macro is not working "On Post Reload".. Can someone help please?

marcus_sommer — Wed, 19 Feb 2014 14:25:06 GMT

Runs your auto-open-export with the same user as manually executed? If not security settings and access rights could be cause this issue.

- Marcus

Re: An Export to Excel Macro is not working "On Post Reload".. Can someone help please?

jerrysvensson — Wed, 19 Feb 2014 14:25:13 GMT

Can you add your macrocode here?

Re: An Export to Excel Macro is not working "On Post Reload".. Can someone help please?

Thu, 20 Feb 2014 05:45:31 GMT

thanks marcus... but still not working...

Thanks Jerry.... please find below the macro code but also note that i am not able to trigger any action "on post reload"..tried opening an URL, tried export also in external tab... but noting is working.. it seems that there is some issue with the settings and not to the macro code.. because its working fine from test button....

Hope u or someone will be able to help me out on this...

Thanks in advance....

SUB ExcelExpwCaption

'Set v=ActiveDocument.Variables("vRunMacro")

'vcheck=v.GetContent.String

'if vcheck="True" then

'Set the path where the excel will be saved

filePath = "E:\Qlick View Jugad\Test Document 1\Test1.xlsx"

'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)

'Get the chart we want to export

Set tableToExport = ActiveDocument.GetSheetObject("CH01")

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

'Paste the rest of the chart

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

ActiveDocument.Save

Application.Quit

' end if

End Sub

Re: An Export to Excel Macro is not working "On Post Reload".. Can someone help please?

marcus_sommer — Thu, 20 Feb 2014 08:08:15 GMT

Are you sure that none trigger worked? Comment the export-routine out and put a simple msgbox in. Also create a simply action like a selection on any field to see which trigger worked.

Also take a look at user properties by the security tab.

- Marcus

Re: An Export to Excel Macro is not working "On Post Reload".. Can someone help please?

jerrysvensson — Thu, 20 Feb 2014 09:11:11 GMT

Try this

It will create a textfile. You can actually use this a debug tool since MsgBox will not work very well if you run things in the background.

Is E a local drive or mapped drive?

sub testFileCreate

strPath = "c:\test.txt"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile(strPath)

objFile.Write " Before Create the Excel spreadsheet"

Set excelFile = CreateObject("Excel.Application")

objFile.Write "After Create the Excel spreadsheet

objFile.Write " before Create the WorkBook"

Set curWorkBook = excelFile.WorkBooks.Add

'And so on ...

objFile.Close
set objFile = nothing

end sub

Re: An Export to Excel Macro is not working "On Post Reload".. Can someone help please?

Thu, 20 Feb 2014 09:45:59 GMT

this macro is working fine if i click on "test" in the edit module interface .. but not working if i assign that macro to a button ...

And E dive is local drive...

Thanks...

Re: An Export to Excel Macro is not working "On Post Reload".. Can someone help please?

jerrysvensson — Thu, 20 Feb 2014 09:51:22 GMT

Do you call testFileCreate in OnPostReload?

Re: An Export to Excel Macro is not working "On Post Reload".. Can someone help please?

Thu, 20 Feb 2014 10:00:46 GMT

thanks u so much jerry... i got the issue... i was not using the correct macro name...

thanks once again.... u really helped...