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

### 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]

#####################################################
###########     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]

gongcw:

OK, I now understand what you want. It's not really a perl question at all--it's more of an Excel question. As I understand it, you want to be able to ask excel for the list of pictures in a sheet so you can move them to your word document.

I poked around and found a reference to Excel's object model and briefly looked around in it to find the appropriate collection to use. Unfortunately, I didn't find one explicitly for pictures. However, the OLEObjects collection page shows an example of adding a bitmap to a sheet. So you should be able to access the images through the OLEObjects collection.

The problem is that the OLEObjects collection might contain many odd things in it, and unless you know what the object is, you'll find it difficult to work with. In the OLEObjects collection example, though, it shows adding a gif image like this:

Worksheets(1).OLEObjects.Add FileName:="arcade.gif"

So if you iterate over the OLEObjects and check the FileName property of each object, you might find one with a .BMP extension. Be aware that many objects might not have a FileName property, so when you're checking, don't kill your application just because the FileName property doesn't exist.

If you can determine the OLEObject type of the objects you're interested in, then you'll be able to find more information via google on how to communicate with them. Dig through the Excel Object reference (link above) and you may find some good clues. Failing that, I'd post a question on an Excel guru board.

...roboticus

Hi roboticus,

Thanks very very much for your kindly help. After exploring some documents about OLEObjects, Pictures, Shapes and their members, this problem has been resolved now.

Thanks again.:)