Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Maybe activating and moving the waitforidle-statement below the setproperties on the container may helpful.
- Marcus
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