Hi roboticus,

Sorry for the unclear terminology. And sorry for this long long question. I hope I can describe the question more clear this time.

I tried to record the macro in both excel and word. And I attached some associated results as below.
If I select the picture and copy it to the clipboard. The macro just shows "Selection.Copy", while copying the chart objects will show "ActiveSheet.ChartObjects" or "ActiveChart.ActiveArea.Copy", which can give me some detailed information about the selected object.
And if I tried to copy picture several times, the macro just record "Selection.Copy" several times.
I mean that copy chart object can show me which one has been copied each time. And I can get member name( "ChartObjects" ) of the worksheet. So I can use "$xlsSheet->ChartObjects" to get all charts.
But if I tried to copy picture objects, I cannot get these critical information. Without the member/object information like ("ChartObjects"), how can I loop all picture objects?


recording macro as below: 

######################################################################
+#########

### In excel
###
### select picture in the 1st sheet, and paste it to 2nd sheet.
### Then select another picture in 2nd sheet

Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("picTest").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("D7").Select
    ActiveSheet.Paste
    Selection.Copy
    Application.Goto Reference:="Macro1"
End Sub

######################################################################
+##########

### In word
###
### at the end of word file, recording the marco
### Ctrl+V to paste the picture which was copied in excel file
### Then, alignment, paragraph

Sub Macro2()
'
' Macro2 Macro
'
'
    Selection.PasteAndFormat (wdPasteDefault)
    Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
    Selection.MoveLeft Unit:=wdCharacter, Count:=1
    Selection.TypeParagraph
End Sub


######################################################################
+#########
######################################################################
+#########

### In excel
###
### select chart in the one sheet.
Sub Macro2()
'
' Macro2 Macro
'

'
    Sheets("picTest").Select
    ActiveSheet.ChartObjects("Chart 9""").Activate
    ActiveSheet.ChartObjects("Chart 9").Activate
    ActiveChart.ChartArea.Copy
    Application.Goto Reference:="Macro2"
End Sub

######################################################################
+##########

### In word
###
### paste the picture which copied in the excel

Sub Macro3()
'
' Macro3 Macro
'
'
    Selection.TypeParagraph
    Selection.MoveUp Unit:=wdLine, Count:=1
    Selection.PasteAndFormat (wdChartLinked)
End Sub

[download]


Actually, I have many pictures in different sheets. And I will insert them in different place in word file. So maybe saving the picture in the work directory is better than saving in clipboard. But I don't think this is a question, because if I can get the picture object, anywhere is OK.



I attached my code below, please kindly give some helps about this. Thanks. 
#####################################################
###########     loading module   
#####################################################
use strict;
use Win32::OLE;
use Win32::OLE qw(in with);
use Win32::OLE::Const;
$Win32::OLE::Warn = 3; 
use Win32::OLE::Const 'Microsoft.Excel';
use Win32::OLE::Const 'Microsoft.Word'; 
use Win32::OLE::Const 'Microsoft Office'; 
###########     loading module   end


#####################################################
###########     workdir and input file definition   
#####################################################
my $workdir = "E:\\workdir\\";
my $excelFileName = "test.xlsx";
###########     workdir and input file definition   


#####################################################
###########      open the excel file 
#####################################################
my $srcExcelName = $workdir.$excelFileName;
my $excelObject=Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');
$excelObject->{DisplayAlerts} = 1;
my $xlsBook=$excelObject->WorkBooks->Open($srcExcelName);
my $xlsSheet = $xlsBook->Worksheets("picTest");

my $iCount=0;
my $picFileType="BMP";
my @picFileArray;
#foreach my $picObj ( $xlsSheet->Pictures ){
#foreach my $picObj ( $xlsSheet->Shapes ){
#  print "aaaaaaaaaaa".$iCount."\n";
#  my $picSaveName = $workdir."aa".$iCount++.".".$picFileType;
#}

foreach my $ChartObj (in $xlsSheet->ChartObjects) { 
    my $savename = $workdir."aa".$iCount++.".".$picFileType;
    $ChartObj->Chart->Export({
        FileName    => $savename,
        FilterName  => $picFileType,
        Interactive => 0});
     push @picFileArray, $savename;
}


#####################################################
###########      open the word  file for saving
#####################################################
my $wordBasicName=$excelFileName;
$wordBasicName =~s/\.xlsx?$//;
my $saveWordName=$workdir.$wordBasicName.".docx";
my $wordObject = CreateObject Win32::OLE 'Word.Application' or die $!;
$wordObject->{'Visible'} = 1;
my $document = $wordObject->Documents->Add;
my $selection = $wordObject->Selection;
###########      open the word  file for saving

foreach ( @picFileArray ){
    insert_picture( $selection, $_ );
}

#########################################################
#############    sub function for insert_picture      
#########################################################
sub insert_picture {
my $selection = shift;
my $picFileName = shift;
  $selection -> TypeParagraph;
  $selection -> InlineShapes->AddPicture({FileName=>$picFileName});
  $selection -> TypeParagraph;
}
#############    sub function for insert_picture      ################
+#

[download]

In reply to Re^4: How to get picture in MS excel and insert them into MS word file by the module win32::OLE? by gongcw
in thread How to get picture in MS excel and insert them into MS word file by the module win32::OLE? by gongcw

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.