Macro not functioning

[Anonymous]

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?

[vikasmahajan]

As per File attached please check your setting for document.

[Anonymous]

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

[#5]

HI ,

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

//Yusuf

[#6]

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