foreach my $ChartObj (in $xlsSheet->ChartObjects) { 
    $ChartObj->Chart->Export({
        FileName    => $savename,
        FilterName  => $picFileType,
        Interactive => 0});
     push @picFileArray, $savename;
}
[download]

gongcw:

First of all, you might want to brush up on your terminology so you can write your questions more clearly. (Example: Win32::OLE doesn't parse spreadsheets, $sheet->Shapes et. al. aren't modules, ...)

Anyway, one way to handle this manually is to go to Excel, select the picture and copy it. Then switch over to Word, select a location for the picture, and then paste it. So I suggest reading Using Win32::OLE and Excel - Tips and Tricks and Automating Windows Apps with Win32::OLE. Then, open up your Excel document, and start the macro recorder, and copy the picture to the clipboard. Then view the macro and using your newly-won knowledge of Win32::OLE, you'll be able to get the picture to the clipboard. For the next step, start word, start the macro recorder, and paste a picture from the clipboard into your word document. Then use the clues in your macro to get the picture from the clipboard into your word document.

If you have any trouble, post the code you're having trouble with, and we'll try to help you out a bit further.

...roboticus

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]