Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Bucharest on Sept 18th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Macro not functioning

Hi guys. I've written the following macro to switch to another tab, download a pivot chart into excel and switch back

Sub Export_register_to_spreadsheet

ActiveDocument.ActivateSheet("Introduction")

ActiveDocument.ActivateSheet("Tabletab")

      ActiveDocument.GetSheetObject("TB30").restore

     'Set the path where the excel will be saved

     filePath = "H:\TestBB.xls"

     'Create the Excel spreadsheet

     Set excelFile = CreateObject("Excel.Application")

     excelFile.Visible = true

     'Create the WorkBook

     Set WorkBook = excelFile.WorkBooks.Add

     'Create the Sheet

     Set Sheet = WorkBook.WorkSheets(1)

     'Get the chart we want to export

     Set tableToExport = ActiveDocument.GetSheetObject("TB30")

     Set chartProperties = tableToExport.GetProperties

   tableToExport.CopyTableToClipboard true

     'Get the caption

     chartCaption = tableToExport.GetCaption.Name.v

       'Set the first cell with the caption

     Sheet.Range("A1") = chartCaption

     'Paste the rest of the chart

     Sheet.Paste Sheet.Range("A2")

     excelFile.Visible = true

ActiveDocument.GetSheetObject("TB30").minimize

     'Cleanup

     Set WorkBook = nothing

     Set excelFile = nothing

End Sub

Unfortunately when i click it, the code page pops up and highlights tableToExport.CopyTableToClipboard true

Sometime it works after a couple of tries but sometimes not at all. Can anyone help?

5 Replies
vikasmahajan

As per File attached please check your setting for document.

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
Anonymous
Not applicable
Author

I have allowed the system access.. it works sometimes and doesn't work others.

vikasmahajan

Go to Document Setting -> Untick -> Macro Override Security

and

Go to User Preferences -> Untick -> Confirm Launch from macro  option and try

Please note as per setting macro is working it never happens some time it will and some time not !

Try this.

Vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
Not applicable
Author

HI ,

  Have you selected the "System Access" and "Allow System Access" in both Requested Module Security and Current Local Security respectively .?

//Yusuf

Not applicable
Author

Check the Excel Object is closed after each operation. set to nothing. check the task manager for its running instances.