Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste chart images macro fails
Hi all,
We are using Excel for reporting in our organization. Basically there is a Java web application which instantiates Excel 2007 with an XLAM. This XLAM opens a Report Template(an .xls, .xlsm file, name supplied as command line parameter). The Template has a datasheet where we have multiple ranges and the Reportsheet which shows the final report based on the data from the Datasheet. The Reportsheet contains formulas to show the final output. It could contain Charts/Graphs also. The data is fetched from SQL server and is pasted on the Ranges. And finally when all the data is pasted and Macro is run the Template is saved as a Final Report somewhere on the Report Server. The Excel runs in the background in all this process. There could be multiple instances of Excel running on the same box. The instances run under separate WindowStation. In this process a macro,PasteChartAsGraphic, present in the template is invoked by the XLAM. The macro basically is about pasting the images of the charts present in the template and later deleting the original charts. So the template would be left with the chart images instead of actual chart objects after the macro is Run. The macro executes successfully for most of the times but failes randomly with different errors. Last time it failed with the following error. Excel ends up in showing the following dialog box. "Microsoft Visual Basic". The dialog contained the following text content: [Run-time error '-2147417848 (80010108)':##Method 'CopyPicture' of object 'ChartObject' failed][&Continue][&End][&Debug][&Help] I can post the macro code if anybody is interested in looking into it. If anybody have any idea please let me know as it is a blocking issue in our process. Any help appreciated. Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste chart images macro fails
Errors like 2147417848 (80010108)' I usally find are caused by one of two
reasons 1) The window or form lost the focus. Some other window has activated another window taking the focus. Tthe code is pasting the picture into the wrong active window. Make sure you are referenceing the window/form by an object name and not using the active window. 2) A similar problem to one abvove if you have multiple forms and you don't have the priledge from one form to access another form because the form is declared as private. "Anand Nichkaode" wrote: Hi all, We are using Excel for reporting in our organization. Basically there is a Java web application which instantiates Excel 2007 with an XLAM. This XLAM opens a Report Template(an .xls, .xlsm file, name supplied as command line parameter). The Template has a datasheet where we have multiple ranges and the Reportsheet which shows the final report based on the data from the Datasheet. The Reportsheet contains formulas to show the final output. It could contain Charts/Graphs also. The data is fetched from SQL server and is pasted on the Ranges. And finally when all the data is pasted and Macro is run the Template is saved as a Final Report somewhere on the Report Server. The Excel runs in the background in all this process. There could be multiple instances of Excel running on the same box. The instances run under separate WindowStation. In this process a macro,PasteChartAsGraphic, present in the template is invoked by the XLAM. The macro basically is about pasting the images of the charts present in the template and later deleting the original charts. So the template would be left with the chart images instead of actual chart objects after the macro is Run. The macro executes successfully for most of the times but failes randomly with different errors. Last time it failed with the following error. Excel ends up in showing the following dialog box. "Microsoft Visual Basic". The dialog contained the following text content: [Run-time error '-2147417848 (80010108)':##Method 'CopyPicture' of object 'ChartObject' failed][&Continue][&End][&Debug][&Help] I can post the macro code if anybody is interested in looking into it. If anybody have any idea please let me know as it is a blocking issue in our process. Any help appreciated. Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste chart images macro fails
Thx for the quick response.
The code copies the chart image and pastes it there on the same sheet as that of the original chart object. There are no forms involved in this operation. -Thx Anand "joel" wrote: Errors like 2147417848 (80010108)' I usally find are caused by one of two reasons 1) The window or form lost the focus. Some other window has activated another window taking the focus. Tthe code is pasting the picture into the wrong active window. Make sure you are referenceing the window/form by an object name and not using the active window. 2) A similar problem to one abvove if you have multiple forms and you don't have the priledge from one form to access another form because the form is declared as private. "Anand Nichkaode" wrote: Hi all, We are using Excel for reporting in our organization. Basically there is a Java web application which instantiates Excel 2007 with an XLAM. This XLAM opens a Report Template(an .xls, .xlsm file, name supplied as command line parameter). The Template has a datasheet where we have multiple ranges and the Reportsheet which shows the final report based on the data from the Datasheet. The Reportsheet contains formulas to show the final output. It could contain Charts/Graphs also. The data is fetched from SQL server and is pasted on the Ranges. And finally when all the data is pasted and Macro is run the Template is saved as a Final Report somewhere on the Report Server. The Excel runs in the background in all this process. There could be multiple instances of Excel running on the same box. The instances run under separate WindowStation. In this process a macro,PasteChartAsGraphic, present in the template is invoked by the XLAM. The macro basically is about pasting the images of the charts present in the template and later deleting the original charts. So the template would be left with the chart images instead of actual chart objects after the macro is Run. The macro executes successfully for most of the times but failes randomly with different errors. Last time it failed with the following error. Excel ends up in showing the following dialog box. "Microsoft Visual Basic". The dialog contained the following text content: [Run-time error '-2147417848 (80010108)':##Method 'CopyPicture' of object 'ChartObject' failed][&Continue][&End][&Debug][&Help] I can post the macro code if anybody is interested in looking into it. If anybody have any idea please let me know as it is a blocking issue in our process. Any help appreciated. Thanks in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste chart images macro fails
You can still loose the focus in the middle of a copy and paste operation.
window is a multi-task protected operating system and other process are going to run while the copy and paste is occuring. The 2147417848 (80010108) error is probably due to the security features of windows that aren't allowing you to write data to a process that isn't owned by excel. Another window has become the active window and by pasting into the active window you are writing data to a window that the excel process doesn't have permission to write. "Anand Nichkaode" wrote: Thx for the quick response. The code copies the chart image and pastes it there on the same sheet as that of the original chart object. There are no forms involved in this operation. -Thx Anand "joel" wrote: Errors like 2147417848 (80010108)' I usally find are caused by one of two reasons 1) The window or form lost the focus. Some other window has activated another window taking the focus. Tthe code is pasting the picture into the wrong active window. Make sure you are referenceing the window/form by an object name and not using the active window. 2) A similar problem to one abvove if you have multiple forms and you don't have the priledge from one form to access another form because the form is declared as private. "Anand Nichkaode" wrote: Hi all, We are using Excel for reporting in our organization. Basically there is a Java web application which instantiates Excel 2007 with an XLAM. This XLAM opens a Report Template(an .xls, .xlsm file, name supplied as command line parameter). The Template has a datasheet where we have multiple ranges and the Reportsheet which shows the final report based on the data from the Datasheet. The Reportsheet contains formulas to show the final output. It could contain Charts/Graphs also. The data is fetched from SQL server and is pasted on the Ranges. And finally when all the data is pasted and Macro is run the Template is saved as a Final Report somewhere on the Report Server. The Excel runs in the background in all this process. There could be multiple instances of Excel running on the same box. The instances run under separate WindowStation. In this process a macro,PasteChartAsGraphic, present in the template is invoked by the XLAM. The macro basically is about pasting the images of the charts present in the template and later deleting the original charts. So the template would be left with the chart images instead of actual chart objects after the macro is Run. The macro executes successfully for most of the times but failes randomly with different errors. Last time it failed with the following error. Excel ends up in showing the following dialog box. "Microsoft Visual Basic". The dialog contained the following text content: [Run-time error '-2147417848 (80010108)':##Method 'CopyPicture' of object 'ChartObject' failed][&Continue][&End][&Debug][&Help] I can post the macro code if anybody is interested in looking into it. If anybody have any idea please let me know as it is a blocking issue in our process. Any help appreciated. Thanks in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste chart images macro fails
As I have mentioned in my original posts there could be mulitple instaces of
Excel running at the same time. Each instance runs in in a separate window station. With separate windowstation and only Excel process running under it do you think that Excel could lose focus and someother application could get it? The Windowstation is a non interactive desktop. -Thx Anand "joel" wrote: You can still loose the focus in the middle of a copy and paste operation. window is a multi-task protected operating system and other process are going to run while the copy and paste is occuring. The 2147417848 (80010108) error is probably due to the security features of windows that aren't allowing you to write data to a process that isn't owned by excel. Another window has become the active window and by pasting into the active window you are writing data to a window that the excel process doesn't have permission to write. "Anand Nichkaode" wrote: Thx for the quick response. The code copies the chart image and pastes it there on the same sheet as that of the original chart object. There are no forms involved in this operation. -Thx Anand "joel" wrote: Errors like 2147417848 (80010108)' I usally find are caused by one of two reasons 1) The window or form lost the focus. Some other window has activated another window taking the focus. Tthe code is pasting the picture into the wrong active window. Make sure you are referenceing the window/form by an object name and not using the active window. 2) A similar problem to one abvove if you have multiple forms and you don't have the priledge from one form to access another form because the form is declared as private. "Anand Nichkaode" wrote: Hi all, We are using Excel for reporting in our organization. Basically there is a Java web application which instantiates Excel 2007 with an XLAM. This XLAM opens a Report Template(an .xls, .xlsm file, name supplied as command line parameter). The Template has a datasheet where we have multiple ranges and the Reportsheet which shows the final report based on the data from the Datasheet. The Reportsheet contains formulas to show the final output. It could contain Charts/Graphs also. The data is fetched from SQL server and is pasted on the Ranges. And finally when all the data is pasted and Macro is run the Template is saved as a Final Report somewhere on the Report Server. The Excel runs in the background in all this process. There could be multiple instances of Excel running on the same box. The instances run under separate WindowStation. In this process a macro,PasteChartAsGraphic, present in the template is invoked by the XLAM. The macro basically is about pasting the images of the charts present in the template and later deleting the original charts. So the template would be left with the chart images instead of actual chart objects after the macro is Run. The macro executes successfully for most of the times but failes randomly with different errors. Last time it failed with the following error. Excel ends up in showing the following dialog box. "Microsoft Visual Basic". The dialog contained the following text content: [Run-time error '-2147417848 (80010108)':##Method 'CopyPicture' of object 'ChartObject' failed][&Continue][&End][&Debug][&Help] I can post the macro code if anybody is interested in looking into it. If anybody have any idea please let me know as it is a blocking issue in our process. Any help appreciated. Thanks in advance. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste chart images macro fails
YES! YES! YES!
I would need to see the copy and paste code to help fix the problem. "Anand Nichkaode" wrote: As I have mentioned in my original posts there could be mulitple instaces of Excel running at the same time. Each instance runs in in a separate window station. With separate windowstation and only Excel process running under it do you think that Excel could lose focus and someother application could get it? The Windowstation is a non interactive desktop. -Thx Anand "joel" wrote: You can still loose the focus in the middle of a copy and paste operation. window is a multi-task protected operating system and other process are going to run while the copy and paste is occuring. The 2147417848 (80010108) error is probably due to the security features of windows that aren't allowing you to write data to a process that isn't owned by excel. Another window has become the active window and by pasting into the active window you are writing data to a window that the excel process doesn't have permission to write. "Anand Nichkaode" wrote: Thx for the quick response. The code copies the chart image and pastes it there on the same sheet as that of the original chart object. There are no forms involved in this operation. -Thx Anand "joel" wrote: Errors like 2147417848 (80010108)' I usally find are caused by one of two reasons 1) The window or form lost the focus. Some other window has activated another window taking the focus. Tthe code is pasting the picture into the wrong active window. Make sure you are referenceing the window/form by an object name and not using the active window. 2) A similar problem to one abvove if you have multiple forms and you don't have the priledge from one form to access another form because the form is declared as private. "Anand Nichkaode" wrote: Hi all, We are using Excel for reporting in our organization. Basically there is a Java web application which instantiates Excel 2007 with an XLAM. This XLAM opens a Report Template(an .xls, .xlsm file, name supplied as command line parameter). The Template has a datasheet where we have multiple ranges and the Reportsheet which shows the final report based on the data from the Datasheet. The Reportsheet contains formulas to show the final output. It could contain Charts/Graphs also. The data is fetched from SQL server and is pasted on the Ranges. And finally when all the data is pasted and Macro is run the Template is saved as a Final Report somewhere on the Report Server. The Excel runs in the background in all this process. There could be multiple instances of Excel running on the same box. The instances run under separate WindowStation. In this process a macro,PasteChartAsGraphic, present in the template is invoked by the XLAM. The macro basically is about pasting the images of the charts present in the template and later deleting the original charts. So the template would be left with the chart images instead of actual chart objects after the macro is Run. The macro executes successfully for most of the times but failes randomly with different errors. Last time it failed with the following error. Excel ends up in showing the following dialog box. "Microsoft Visual Basic". The dialog contained the following text content: [Run-time error '-2147417848 (80010108)':##Method 'CopyPicture' of object 'ChartObject' failed][&Continue][&End][&Debug][&Help] I can post the macro code if anybody is interested in looking into it. If anybody have any idea please let me know as it is a blocking issue in our process. Any help appreciated. Thanks in advance. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste chart images macro fails
Sure.
Here it is. Public Sub pasteChartAsGraphic() 'On Error GoTo errPasteChartAsGraphic Dim RptObj As Workbook Dim objChart As ChartObject Dim objSheetPasteAsGraph As Worksheet Dim blnAdvHide As Boolean Dim blnSimpleHide As Boolean Set RptObj = ThisWorkbook For Each objSheetPasteAsGraph In RptObj.Worksheets If objSheetPasteAsGraph.Visible = xlSheetHidden Then blnSimpleHide = True objSheetPasteAsGraph.Visible = xlSheetVisible End If If objSheetPasteAsGraph.Visible = xlSheetHidden Then blnAdvHide = True objSheetPasteAsGraph.Visible = xlSheetVisible End If objSheetPasteAsGraph.Activate For Each objChart In ActiveSheet.ChartObjects() objChart.Activate If objChart.Height 0 And objChart.Width 0 Then objChart.CopyPicture Appearance:=xlPrinter, Format:=xlPicture objSheetPasteAsGraph.Pictures.Paste.Select Application.CutCopyMode = False 'ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture 'ActiveSheet.Pictures.Paste.Select Selection.Height = objChart.Height Selection.Width = objChart.Width Selection.Top = objChart.Top Selection.Left = objChart.Left Selection.Border.LineStyle = objChart.Border.LineStyle Selection.Interior.ColorIndex = objChart.Interior.ColorIndex Selection.Interior.Pattern = objChart.Interior.Pattern Selection.Shadow = objChart.Shadow objChart.Delete End If Next Set objChart = Nothing If blnSimpleHide = True Then blnSimpleHide = False objSheetPasteAsGraph.Visible = xlSheetHidden End If If blnAdvHide = True Then blnAdvHide = False objSheetPasteAsGraph.Visible = xlSheetVeryHidden End If Next Set objSheetPasteAsGraph = Nothing Exit Sub errPasteChartAsGraphic: Set objSheetPasteAsGraph = Nothing Set objChart = Nothing End Sub I have been getting random errors for different code stamements like while setting height, width, top, etc. -Thx Anand "joel" wrote: YES! YES! YES! I would need to see the copy and paste code to help fix the problem. "Anand Nichkaode" wrote: As I have mentioned in my original posts there could be mulitple instaces of Excel running at the same time. Each instance runs in in a separate window station. With separate windowstation and only Excel process running under it do you think that Excel could lose focus and someother application could get it? The Windowstation is a non interactive desktop. -Thx Anand "joel" wrote: You can still loose the focus in the middle of a copy and paste operation. window is a multi-task protected operating system and other process are going to run while the copy and paste is occuring. The 2147417848 (80010108) error is probably due to the security features of windows that aren't allowing you to write data to a process that isn't owned by excel. Another window has become the active window and by pasting into the active window you are writing data to a window that the excel process doesn't have permission to write. "Anand Nichkaode" wrote: Thx for the quick response. The code copies the chart image and pastes it there on the same sheet as that of the original chart object. There are no forms involved in this operation. -Thx Anand "joel" wrote: Errors like 2147417848 (80010108)' I usally find are caused by one of two reasons 1) The window or form lost the focus. Some other window has activated another window taking the focus. Tthe code is pasting the picture into the wrong active window. Make sure you are referenceing the window/form by an object name and not using the active window. 2) A similar problem to one abvove if you have multiple forms and you don't have the priledge from one form to access another form because the form is declared as private. "Anand Nichkaode" wrote: Hi all, We are using Excel for reporting in our organization. Basically there is a Java web application which instantiates Excel 2007 with an XLAM. This XLAM opens a Report Template(an .xls, .xlsm file, name supplied as command line parameter). The Template has a datasheet where we have multiple ranges and the Reportsheet which shows the final report based on the data from the Datasheet. The Reportsheet contains formulas to show the final output. It could contain Charts/Graphs also. The data is fetched from SQL server and is pasted on the Ranges. And finally when all the data is pasted and Macro is run the Template is saved as a Final Report somewhere on the Report Server. The Excel runs in the background in all this process. There could be multiple instances of Excel running on the same box. The instances run under separate WindowStation. In this process a macro,PasteChartAsGraphic, present in the template is invoked by the XLAM. The macro basically is about pasting the images of the charts present in the template and later deleting the original charts. So the template would be left with the chart images instead of actual chart objects after the macro is Run. The macro executes successfully for most of the times but failes randomly with different errors. Last time it failed with the following error. Excel ends up in showing the following dialog box. "Microsoft Visual Basic". The dialog contained the following text content: [Run-time error '-2147417848 (80010108)':##Method 'CopyPicture' of object 'ChartObject' failed][&Continue][&End][&Debug][&Help] I can post the macro code if anybody is interested in looking into it. If anybody have any idea please let me know as it is a blocking issue in our process. Any help appreciated. Thanks in advance. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste chart images macro fails
This should fix the problem
Public Sub pasteChartAsGraphic() 'On Error GoTo errPasteChartAsGraphic Dim RptObj As Workbook Dim objChart As ChartObject Dim objSheetPasteAsGraph As Worksheet Dim blnAdvHide As Boolean Dim blnSimpleHide As Boolean Set RptObj = ThisWorkbook For Each objSheetPasteAsGraph In RptObj.Worksheets If objSheetPasteAsGraph.Visible = xlSheetHidden Then blnSimpleHide = True objSheetPasteAsGraph.Visible = xlSheetVisible End If If objSheetPasteAsGraph.Visible = xlSheetHidden Then blnAdvHide = True objSheetPasteAsGraph.Visible = xlSheetVisible End If With objSheetPasteAsGraph For Each objChart In .ChartObjects() With objChart If .Height 0 And .Width 0 Then .CopyPicture Appearance:=xlPrinter, Format:=xlPicture Set NewPict = .Pictures.Paste Application.CutCopyMode = False With NewPict .Height = objChart.Height .Width = objChart.Width .Top = objChart.Top .Left = objChart.Left .Border.LineStyle = objChart.Border.LineStyle .Interior.ColorIndex = objChart.Interior.ColorIndex .Interior.Pattern = objChart.Interior.Pattern .Shadow = objChart.Shadow End With objChart.Delete End If End With Next Set objChart = Nothing If blnSimpleHide = True Then blnSimpleHide = False objSheetPasteAsGraph.Visible = xlSheetHidden End If If blnAdvHide = True Then blnAdvHide = False objSheetPasteAsGraph.Visible = xlSheetVeryHidden End If End With Next Set objSheetPasteAsGraph = Nothing Exit Sub errPasteChartAsGraphic: Set objSheetPasteAsGraph = Nothing Set objChart = Nothing End Sub "Anand Nichkaode" wrote: Sure. Here it is. Public Sub pasteChartAsGraphic() 'On Error GoTo errPasteChartAsGraphic Dim RptObj As Workbook Dim objChart As ChartObject Dim objSheetPasteAsGraph As Worksheet Dim blnAdvHide As Boolean Dim blnSimpleHide As Boolean Set RptObj = ThisWorkbook For Each objSheetPasteAsGraph In RptObj.Worksheets If objSheetPasteAsGraph.Visible = xlSheetHidden Then blnSimpleHide = True objSheetPasteAsGraph.Visible = xlSheetVisible End If If objSheetPasteAsGraph.Visible = xlSheetHidden Then blnAdvHide = True objSheetPasteAsGraph.Visible = xlSheetVisible End If objSheetPasteAsGraph.Activate For Each objChart In ActiveSheet.ChartObjects() objChart.Activate If objChart.Height 0 And objChart.Width 0 Then objChart.CopyPicture Appearance:=xlPrinter, Format:=xlPicture objSheetPasteAsGraph.Pictures.Paste.Select Application.CutCopyMode = False 'ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture 'ActiveSheet.Pictures.Paste.Select Selection.Height = objChart.Height Selection.Width = objChart.Width Selection.Top = objChart.Top Selection.Left = objChart.Left Selection.Border.LineStyle = objChart.Border.LineStyle Selection.Interior.ColorIndex = objChart.Interior.ColorIndex Selection.Interior.Pattern = objChart.Interior.Pattern Selection.Shadow = objChart.Shadow objChart.Delete End If Next Set objChart = Nothing If blnSimpleHide = True Then blnSimpleHide = False objSheetPasteAsGraph.Visible = xlSheetHidden End If If blnAdvHide = True Then blnAdvHide = False objSheetPasteAsGraph.Visible = xlSheetVeryHidden End If Next Set objSheetPasteAsGraph = Nothing Exit Sub errPasteChartAsGraphic: Set objSheetPasteAsGraph = Nothing Set objChart = Nothing End Sub I have been getting random errors for different code stamements like while setting height, width, top, etc. -Thx Anand "joel" wrote: YES! YES! YES! I would need to see the copy and paste code to help fix the problem. "Anand Nichkaode" wrote: As I have mentioned in my original posts there could be mulitple instaces of Excel running at the same time. Each instance runs in in a separate window station. With separate windowstation and only Excel process running under it do you think that Excel could lose focus and someother application could get it? The Windowstation is a non interactive desktop. -Thx Anand "joel" wrote: You can still loose the focus in the middle of a copy and paste operation. window is a multi-task protected operating system and other process are going to run while the copy and paste is occuring. The 2147417848 (80010108) error is probably due to the security features of windows that aren't allowing you to write data to a process that isn't owned by excel. Another window has become the active window and by pasting into the active window you are writing data to a window that the excel process doesn't have permission to write. "Anand Nichkaode" wrote: Thx for the quick response. The code copies the chart image and pastes it there on the same sheet as that of the original chart object. There are no forms involved in this operation. -Thx Anand "joel" wrote: Errors like 2147417848 (80010108)' I usally find are caused by one of two reasons 1) The window or form lost the focus. Some other window has activated another window taking the focus. Tthe code is pasting the picture into the wrong active window. Make sure you are referenceing the window/form by an object name and not using the active window. 2) A similar problem to one abvove if you have multiple forms and you don't have the priledge from one form to access another form because the form is declared as private. "Anand Nichkaode" wrote: Hi all, We are using Excel for reporting in our organization. Basically there is a Java web application which instantiates Excel 2007 with an XLAM. This XLAM opens a Report Template(an .xls, .xlsm file, name supplied as command line parameter). The Template has a datasheet where we have multiple ranges and the Reportsheet which shows the final report based on the data from the Datasheet. The Reportsheet contains formulas to show the final output. It could contain Charts/Graphs also. The data is fetched from SQL server and is pasted on the Ranges. And finally when all the data is pasted and Macro is run the Template is saved as a Final Report somewhere on the Report Server. The Excel runs in the background in all this process. There could be multiple instances of Excel running on the same box. The instances run under separate WindowStation. In this process a macro,PasteChartAsGraphic, present in the template is invoked by the XLAM. The macro basically is about pasting the images of the charts present in the template and later deleting the original charts. So the template would be left with the chart images instead of actual chart objects after the macro is Run. The macro executes successfully for most of the times but failes randomly with different errors. Last time it failed with the following error. Excel ends up in showing the following dialog box. "Microsoft Visual Basic". The dialog contained the following text content: [Run-time error '-2147417848 (80010108)':##Method 'CopyPicture' of object 'ChartObject' failed][&Continue][&End][&Debug][&Help] I can post the macro code if anybody is interested in looking into it. If anybody have any idea please let me know as it is a blocking issue in our process. Any help appreciated. Thanks in advance. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste chart images macro fails
Ok, Thx.
Let me try it. -Thx Anand "joel" wrote: This should fix the problem Public Sub pasteChartAsGraphic() 'On Error GoTo errPasteChartAsGraphic Dim RptObj As Workbook Dim objChart As ChartObject Dim objSheetPasteAsGraph As Worksheet Dim blnAdvHide As Boolean Dim blnSimpleHide As Boolean Set RptObj = ThisWorkbook For Each objSheetPasteAsGraph In RptObj.Worksheets If objSheetPasteAsGraph.Visible = xlSheetHidden Then blnSimpleHide = True objSheetPasteAsGraph.Visible = xlSheetVisible End If If objSheetPasteAsGraph.Visible = xlSheetHidden Then blnAdvHide = True objSheetPasteAsGraph.Visible = xlSheetVisible End If With objSheetPasteAsGraph For Each objChart In .ChartObjects() With objChart If .Height 0 And .Width 0 Then .CopyPicture Appearance:=xlPrinter, Format:=xlPicture Set NewPict = .Pictures.Paste Application.CutCopyMode = False With NewPict .Height = objChart.Height .Width = objChart.Width .Top = objChart.Top .Left = objChart.Left .Border.LineStyle = objChart.Border.LineStyle .Interior.ColorIndex = objChart.Interior.ColorIndex .Interior.Pattern = objChart.Interior.Pattern .Shadow = objChart.Shadow End With objChart.Delete End If End With Next Set objChart = Nothing If blnSimpleHide = True Then blnSimpleHide = False objSheetPasteAsGraph.Visible = xlSheetHidden End If If blnAdvHide = True Then blnAdvHide = False objSheetPasteAsGraph.Visible = xlSheetVeryHidden End If End With Next Set objSheetPasteAsGraph = Nothing Exit Sub errPasteChartAsGraphic: Set objSheetPasteAsGraph = Nothing Set objChart = Nothing End Sub "Anand Nichkaode" wrote: Sure. Here it is. Public Sub pasteChartAsGraphic() 'On Error GoTo errPasteChartAsGraphic Dim RptObj As Workbook Dim objChart As ChartObject Dim objSheetPasteAsGraph As Worksheet Dim blnAdvHide As Boolean Dim blnSimpleHide As Boolean Set RptObj = ThisWorkbook For Each objSheetPasteAsGraph In RptObj.Worksheets If objSheetPasteAsGraph.Visible = xlSheetHidden Then blnSimpleHide = True objSheetPasteAsGraph.Visible = xlSheetVisible End If If objSheetPasteAsGraph.Visible = xlSheetHidden Then blnAdvHide = True objSheetPasteAsGraph.Visible = xlSheetVisible End If objSheetPasteAsGraph.Activate For Each objChart In ActiveSheet.ChartObjects() objChart.Activate If objChart.Height 0 And objChart.Width 0 Then objChart.CopyPicture Appearance:=xlPrinter, Format:=xlPicture objSheetPasteAsGraph.Pictures.Paste.Select Application.CutCopyMode = False 'ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture 'ActiveSheet.Pictures.Paste.Select Selection.Height = objChart.Height Selection.Width = objChart.Width Selection.Top = objChart.Top Selection.Left = objChart.Left Selection.Border.LineStyle = objChart.Border.LineStyle Selection.Interior.ColorIndex = objChart.Interior.ColorIndex Selection.Interior.Pattern = objChart.Interior.Pattern Selection.Shadow = objChart.Shadow objChart.Delete End If Next Set objChart = Nothing If blnSimpleHide = True Then blnSimpleHide = False objSheetPasteAsGraph.Visible = xlSheetHidden End If If blnAdvHide = True Then blnAdvHide = False objSheetPasteAsGraph.Visible = xlSheetVeryHidden End If Next Set objSheetPasteAsGraph = Nothing Exit Sub errPasteChartAsGraphic: Set objSheetPasteAsGraph = Nothing Set objChart = Nothing End Sub I have been getting random errors for different code stamements like while setting height, width, top, etc. -Thx Anand "joel" wrote: YES! YES! YES! I would need to see the copy and paste code to help fix the problem. "Anand Nichkaode" wrote: As I have mentioned in my original posts there could be mulitple instaces of Excel running at the same time. Each instance runs in in a separate window station. With separate windowstation and only Excel process running under it do you think that Excel could lose focus and someother application could get it? The Windowstation is a non interactive desktop. -Thx Anand "joel" wrote: You can still loose the focus in the middle of a copy and paste operation. window is a multi-task protected operating system and other process are going to run while the copy and paste is occuring. The 2147417848 (80010108) error is probably due to the security features of windows that aren't allowing you to write data to a process that isn't owned by excel. Another window has become the active window and by pasting into the active window you are writing data to a window that the excel process doesn't have permission to write. "Anand Nichkaode" wrote: Thx for the quick response. The code copies the chart image and pastes it there on the same sheet as that of the original chart object. There are no forms involved in this operation. -Thx Anand "joel" wrote: Errors like 2147417848 (80010108)' I usally find are caused by one of two reasons 1) The window or form lost the focus. Some other window has activated another window taking the focus. Tthe code is pasting the picture into the wrong active window. Make sure you are referenceing the window/form by an object name and not using the active window. 2) A similar problem to one abvove if you have multiple forms and you don't have the priledge from one form to access another form because the form is declared as private. "Anand Nichkaode" wrote: Hi all, We are using Excel for reporting in our organization. Basically there is a Java web application which instantiates Excel 2007 with an XLAM. This XLAM opens a Report Template(an .xls, .xlsm file, name supplied as command line parameter). The Template has a datasheet where we have multiple ranges and the Reportsheet which shows the final report based on the data from the Datasheet. The Reportsheet contains formulas to show the final output. It could contain Charts/Graphs also. The data is fetched from SQL server and is pasted on the Ranges. And finally when all the data is pasted and Macro is run the Template is saved as a Final Report somewhere on the Report Server. The Excel runs in the background in all this process. There could be multiple instances of Excel running on the same box. The instances run under separate WindowStation. In this process a macro,PasteChartAsGraphic, present in the template is invoked by the XLAM. The macro basically is about pasting the images of the charts present in the template and later deleting the original charts. So the template would be left with the chart images instead of actual chart objects after the macro is Run. The macro executes successfully for most of the times but failes randomly with different errors. Last time it failed with the following error. Excel ends up in showing the following dialog box. "Microsoft Visual Basic". The dialog contained the following text content: [Run-time error '-2147417848 (80010108)':##Method 'CopyPicture' of object 'ChartObject' failed][&Continue][&End][&Debug][&Help] I can post the macro code if anybody is interested in looking into it. If anybody have any idea please let me know as it is a blocking issue in our process. Any help appreciated. Thanks in advance. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste chart images macro fails
Hi,
Sorry for the late reply. I was stuck in other things and couldn't find time to try you solution. Y'day I tried you solution and it fialed with "'CopyPicture' of object 'ChartObject' " error. Any idea what could be the reason for this. -Thx Anand "Anand Nichkaode" wrote: Ok, Thx. Let me try it. -Thx Anand "joel" wrote: This should fix the problem Public Sub pasteChartAsGraphic() 'On Error GoTo errPasteChartAsGraphic Dim RptObj As Workbook Dim objChart As ChartObject Dim objSheetPasteAsGraph As Worksheet Dim blnAdvHide As Boolean Dim blnSimpleHide As Boolean Set RptObj = ThisWorkbook For Each objSheetPasteAsGraph In RptObj.Worksheets If objSheetPasteAsGraph.Visible = xlSheetHidden Then blnSimpleHide = True objSheetPasteAsGraph.Visible = xlSheetVisible End If If objSheetPasteAsGraph.Visible = xlSheetHidden Then blnAdvHide = True objSheetPasteAsGraph.Visible = xlSheetVisible End If With objSheetPasteAsGraph For Each objChart In .ChartObjects() With objChart If .Height 0 And .Width 0 Then .CopyPicture Appearance:=xlPrinter, Format:=xlPicture Set NewPict = .Pictures.Paste Application.CutCopyMode = False With NewPict .Height = objChart.Height .Width = objChart.Width .Top = objChart.Top .Left = objChart.Left .Border.LineStyle = objChart.Border.LineStyle .Interior.ColorIndex = objChart.Interior.ColorIndex .Interior.Pattern = objChart.Interior.Pattern .Shadow = objChart.Shadow End With objChart.Delete End If End With Next Set objChart = Nothing If blnSimpleHide = True Then blnSimpleHide = False objSheetPasteAsGraph.Visible = xlSheetHidden End If If blnAdvHide = True Then blnAdvHide = False objSheetPasteAsGraph.Visible = xlSheetVeryHidden End If End With Next Set objSheetPasteAsGraph = Nothing Exit Sub errPasteChartAsGraphic: Set objSheetPasteAsGraph = Nothing Set objChart = Nothing End Sub "Anand Nichkaode" wrote: Sure. Here it is. Public Sub pasteChartAsGraphic() 'On Error GoTo errPasteChartAsGraphic Dim RptObj As Workbook Dim objChart As ChartObject Dim objSheetPasteAsGraph As Worksheet Dim blnAdvHide As Boolean Dim blnSimpleHide As Boolean Set RptObj = ThisWorkbook For Each objSheetPasteAsGraph In RptObj.Worksheets If objSheetPasteAsGraph.Visible = xlSheetHidden Then blnSimpleHide = True objSheetPasteAsGraph.Visible = xlSheetVisible End If If objSheetPasteAsGraph.Visible = xlSheetHidden Then blnAdvHide = True objSheetPasteAsGraph.Visible = xlSheetVisible End If objSheetPasteAsGraph.Activate For Each objChart In ActiveSheet.ChartObjects() objChart.Activate If objChart.Height 0 And objChart.Width 0 Then objChart.CopyPicture Appearance:=xlPrinter, Format:=xlPicture objSheetPasteAsGraph.Pictures.Paste.Select Application.CutCopyMode = False 'ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture 'ActiveSheet.Pictures.Paste.Select Selection.Height = objChart.Height Selection.Width = objChart.Width Selection.Top = objChart.Top Selection.Left = objChart.Left Selection.Border.LineStyle = objChart.Border.LineStyle Selection.Interior.ColorIndex = objChart.Interior.ColorIndex Selection.Interior.Pattern = objChart.Interior.Pattern Selection.Shadow = objChart.Shadow objChart.Delete End If Next Set objChart = Nothing If blnSimpleHide = True Then blnSimpleHide = False objSheetPasteAsGraph.Visible = xlSheetHidden End If If blnAdvHide = True Then blnAdvHide = False objSheetPasteAsGraph.Visible = xlSheetVeryHidden End If Next Set objSheetPasteAsGraph = Nothing Exit Sub errPasteChartAsGraphic: Set objSheetPasteAsGraph = Nothing Set objChart = Nothing End Sub I have been getting random errors for different code stamements like while setting height, width, top, etc. -Thx Anand "joel" wrote: YES! YES! YES! I would need to see the copy and paste code to help fix the problem. "Anand Nichkaode" wrote: As I have mentioned in my original posts there could be mulitple instaces of Excel running at the same time. Each instance runs in in a separate window station. With separate windowstation and only Excel process running under it do you think that Excel could lose focus and someother application could get it? The Windowstation is a non interactive desktop. -Thx Anand "joel" wrote: You can still loose the focus in the middle of a copy and paste operation. window is a multi-task protected operating system and other process are going to run while the copy and paste is occuring. The 2147417848 (80010108) error is probably due to the security features of windows that aren't allowing you to write data to a process that isn't owned by excel. Another window has become the active window and by pasting into the active window you are writing data to a window that the excel process doesn't have permission to write. "Anand Nichkaode" wrote: Thx for the quick response. The code copies the chart image and pastes it there on the same sheet as that of the original chart object. There are no forms involved in this operation. -Thx Anand "joel" wrote: Errors like 2147417848 (80010108)' I usally find are caused by one of two reasons 1) The window or form lost the focus. Some other window has activated another window taking the focus. Tthe code is pasting the picture into the wrong active window. Make sure you are referenceing the window/form by an object name and not using the active window. 2) A similar problem to one abvove if you have multiple forms and you don't have the priledge from one form to access another form because the form is declared as private. "Anand Nichkaode" wrote: Hi all, We are using Excel for reporting in our organization. Basically there is a Java web application which instantiates Excel 2007 with an XLAM. This XLAM opens a Report Template(an .xls, .xlsm file, name supplied as command line parameter). The Template has a datasheet where we have multiple ranges and the Reportsheet which shows the final report based on the data from the Datasheet. The Reportsheet contains formulas to show the final output. It could contain Charts/Graphs also. The data is fetched from SQL server and is pasted on the Ranges. And finally when all the data is pasted and Macro is run the Template is saved as a Final Report somewhere on the Report Server. The Excel runs in the background in all this process. There could be multiple instances of Excel running on the same box. The instances run under separate WindowStation. In this process a macro,PasteChartAsGraphic, present in the template is invoked by the XLAM. The macro basically is about pasting the images of the charts present in the template and later deleting the original charts. So the template would be left with the chart images instead of actual chart objects after the macro is Run. The macro executes successfully for most of the times but failes randomly with different errors. Last time it failed with the following error. Excel ends up in showing the following dialog box. "Microsoft Visual Basic". The dialog contained the following text content: [Run-time error '-2147417848 (80010108)':##Method 'CopyPicture' of object 'ChartObject' failed][&Continue][&End][&Debug][&Help] I can post the macro code if anybody is interested in looking into it. If anybody have any idea please let me know as it is a blocking issue in our process. Any help appreciated. Thanks in advance. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste chart images macro fails
In Excel 2007 I have to put this in a retry loop. It usually works on the second or third try. Same with setting oPoint.Datalabel.Top or .Left, I need to retry. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy/Paste in VBA fails? | Excel Programming | |||
Chart.Export images are shrinking as I export more images | Charts and Charting in Excel | |||
Copy and paste images | Excel Discussion (Misc queries) | |||
Macro fails in Pie Chart - Can Microsoft / MVP please comment | Charts and Charting in Excel | |||
Paste method fails in VBA | Excel Programming |