Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
dawgfather
Creator
Creator

Export macro not copying title fields after upgrade to 12.50

I had a macro in version 12.10 (from 2018) that took a container and the different tabs/charts and exported them all to Excel. Everything worked well until I upgraded to 12.50 (April 2020) yesterday.

Now it seems that when I run the macro, the data is extracted, the sheets are created and named, but the only column headers that now export are on the tab that happens to be displayed in the container at the time of export. All the other sheets have blank column headers.

For example, I can click tab/chart 2 to view in QV. I run the macro and tabs 1,3,4,5,6,7 have no headers but tab 2 has headers. I then click tab/chart 5 and run the macro. Tabs 1,2,3,4,6,7 have no headers but tab 5 has headers.

Can anyone assist?

Here is my macro code that was working until the upgrade (apparently some HTML code is being stripped when I post, so it might not be 100% pasted)

 

sub ExportContainer3

  set oXL = CreateObject("Excel.Application")

  oXL.DisplayAlerts = False

  oXL.visible=True

  Dim oXLDoc 'as Excel.Workbook

  Dim i

  Set oXLDoc = oXL.Workbooks.Add

  '---------------------------------------

  Set ContainerObj = ActiveDocument.GetSheetObject("CT73")

    Set ContProp=ContainerObj.GetProperties

  aSheetObj=Array("CH883","CH884","CH885","CH886","CH887","CH888","CH889","CH890","CH891","CH892","CH893","CH894","CH895")

  '---------------------------------------

  for i=0 to UBound(aSheetObj)

  'ActiveDocument.GetApplication.WaitForIdle

  oXL.Sheets.Add

  oXL.ActiveSheet.Move ,oXL.Sheets( oXL.Sheets.Count )

        ContProp.SingleObjectActiveIndex = i

        ContainerObj.SetProperties ContProp

  Set oSH = oXL.ActiveSheet

     oSH.Range("A1").Select

     Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))

     obj.CopyTableToClipboard True

     oSH.Paste

     sCaption=obj.GetCaption.Name.v

     set obj=Nothing

  oSH.Rows("1:1").Select

  oXL.Selection.Font.Bold = True

     oSH.Cells.Select
    
  oXL.Selection.ColumnWidth = 100

     oXL.Selection.Columns.AutoFit
    
     oXL.Selection.Rows.AutoFit

     oSH.Range("A1").Select  

  oSH.Name=left(sCaption,30)

  set oSH=Nothing

  next

oXL.Sheets("Sheet1").Select

oXL.Sheets("Sheet1").Delete

end sub

2 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

Maybe activating and moving the waitforidle-statement below the setproperties on the container may helpful.

- Marcus

cwolf
Creator III
Creator III

The activation of a container object by setting the SingleObjectActiveIndex property works only for an container with grid layout. For a Container with a single object layout there is no possiblity to activate an object and bring it to the front via macro. As an workaround you can hide all objects of the container and show and activate only one object for exporting:

sub ExportContainer3

set oXL = CreateObject("Excel.Application")
oXL.DisplayAlerts = False
oXL.visible=True
set oXLDoc = oXL.Workbooks.Add

set app=ActiveDocument.GetApplication
set ContainerObj = ActiveDocument.GetSheetObject("CT73")
set ContProp=ContainerObj.GetProperties
maxi=ContProp.ContainedObjects.Count-1

'Hide all charts
for i=0 to maxi
	objid=ContProp.ContainedObjects.Item(i).Def.ObjectId
	set obj=ActiveDocument.GetSheetObject(objid)
	set prop=obj.GetProperties
	prop.GraphLayout.Frame.Show.Always = false
	prop.GraphLayout.Frame.Show.Expression.v = "1=0"
	obj.SetProperties prop
next

'Export all charts
for i=0 to maxi
	objid=ContProp.ContainedObjects.Item(i).Def.ObjectId
	set obj=ActiveDocument.GetSheetObject(objid)
	set prop=obj.GetProperties
	prop.GraphLayout.Frame.Show.Always = true
	obj.SetProperties prop
	obj.Activate

	app.WaitForIdle
	obj.CopyTableToClipboard True
	app.WaitForIdle

	oXL.Sheets.Add
	oXL.ActiveSheet.Move ,oXL.Sheets( oXL.Sheets.Count )
	set oSH = oXL.ActiveSheet
	oSH.Range("A1").Select
	oSH.Paste
	sCaption=obj.GetCaption.Name.v
	oSH.Rows("1:1").Select
	oXL.Selection.Font.Bold = True
	oSH.Cells.Select
	oXL.Selection.ColumnWidth = 100
	oXL.Selection.Columns.AutoFit
	oXL.Selection.Rows.AutoFit
	oSH.Range("A1").Select  
	oSH.Name=left(sCaption,30)

	prop.GraphLayout.Frame.Show.Always = false
	obj.SetProperties prop
next

'Show all charts
for i=0 to maxi
	objid=ContProp.ContainedObjects.Item(i).Def.ObjectId
	set obj=ActiveDocument.GetSheetObject(objid)
	set prop=obj.GetProperties
	prop.GraphLayout.Frame.Show.Always = true
	obj.SetProperties prop
next

oXL.Sheets("Tabelle1").Select
oXL.Sheets("Tabelle1").Delete

end sub