VBA:CopyPNG to Ms Word Not Fully Work

Susanto3311
3StarLounger
Posts: 240
Joined: 17 Feb 2022, 05:16

VBA:CopyPNG to Ms Word Not Fully Work

Post by Susanto3311 »

hi all.
this code from hans, but not stable work..
the code use for export data/tabel from excel to ms word
sometime work sometimes not work
here code

Code: Select all

Sub CopyPNG()
    Dim oldselect As Range
    Dim rng As Range
    Set oldselect = Selection
Set wkbCrntWorkBook = ActiveWorkbook
   Set rng = Application.InputBox(Prompt:="Select a range to copy", Type:=8)
    rng.Select
    Selection.CopyPicture xlScreen, xlPicture
    rng.Offset(0, rng.Columns.Count).PasteSpecial
    Selection.Copy ' Here's the trick bit; we just copy the picture we have just pasted, and bingo ...
'    ' ... the image is now available as a PNG on the clipboard; multiple other formnat are also available
    Selection.Delete
    oldselect.Select
 Dim myword As Object
    Set myword = CreateObject("word.document")
    myword.Application.Visible = True
    myword.Application.Selection.PasteSpecial DataType:=15  ' datatype 15 is PNG
 End Sub

after run macro show message "Run Time error- "PasteSpecial method of Range class failed"
my workbook contains macro but not protected only to open workbook. The code is protected
if my workbook contains macro and save as in xlsm, the code not work.
my workbook maybe hidden column/rows..and maybe contains merge column/rows..
i hope someone help me out this case
note:
for reason i can't attachted file

.susant
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA:CopyPNG to Ms Word Not Fully Work

Post by HansV »

I'd test the macro in different situations.
When it fails, try to think what's different from the times it succeeds.
Can you find a pattern - is it only when the range contains hidden rows or columns, or when the range contains merged cells, or something else?
Best wishes,
Hans

Susanto3311
3StarLounger
Posts: 240
Joined: 17 Feb 2022, 05:16

Re: VBA:CopyPNG to Ms Word Not Fully Work

Post by Susanto3311 »

hi hans...
i have found my problem , the fact the range not contains hidden rows/column but the range contains merged cell.
please, check my attachment file

note:
merged cell from row 1 till row4
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA:CopyPNG to Ms Word Not Fully Work

Post by HansV »

I've tried several ranges including the cells in rows 1 to 4, but the code executes without error for me.
Best wishes,
Hans