how to call excel com to insert a image to a excel file ? Topic is solved

[woshichuanqilz72]

As the title said, I found a post before, which said PasteSpecial can do the job.

And I try the code like this: error occur : cant get the PasteSpecial property. So any other way to make it?

Code: Select all

FileSelectFile, Path
Xl := ComObjCreate("Excel.Application")
Xl.Workbooks.Open(Path) ;open an existing file
Xl.Visible := True

Xl.Range("A:A").PasteSpecial(-4163)

--------------------------------------------

here is my final code, don't the unit in the excel of height and width of cell and shape is not pixel. The ratio should be 431/1607 and 269/137, not very precise, cuz I measure it by manual but works fine on me.
Any one who know the better way to calculate the ratio can amend my code and post your code ~

Code: Select all

SetTitleMatchMode, regex
;~ FileSelectFile, Path
Path := "C:\Users\HG_LiZhe\Desktop\test.xlsx"
xlApp := ComObjCreate("Excel.Application")
xlApp.Workbooks.Open(Path) ;open an existing file
xlApp.Visible := True
WinWaitActive, - Excel
WinMinimize
FileName := A_Desktop "\1.png"
xlRng := xlApp.Range("C2")
xlApp.ScreenUpdating := false
xlShape := xlApp.ActiveSheet.Shapes.AddPicture(FileName, false, true, xlRng.Left, xlRng.Top, -1, -1)
xlShape.LockAspectRatio := true
xlShape.Width := 100      ; 431
xlShape.Height := 200   ; 269
xlRng.ColumnWidth  := 200 * 431/1607 ; 1607
xlRng.RowHeight  := 100 * 269/137 ; 137
xlApp.ScreenUpdating := true
ExitApp

[TLM]

It works for me like this Xl.Range("A:A").PasteSpecial() without the constant -4163

FWIW

Range Class:
Function PasteSpecial([Paste As XlPasteType = xlPasteAll], [Operation As XlPasteSpecialOperation = xlPasteSpecialOperationNone], [SkipBlanks], [Transpose])
Member of Excel.Range

Worksheet Class:
Sub PasteSpecial([Format], [Link], [DisplayAsIcon], [IconFileName], [IconIndex], [IconLabel], [NoHTMLFormatting])
Member of Excel.Worksheet

[FanaticGuru]

As the title said, I found a post before, which said PasteSpecial can do the job.

And I try the code like this: error occur : cant get the PasteSpecial property. So any other way to make it?

Code: Select all

FileSelectFile, Path
Xl := ComObjCreate("Excel.Application")
Xl.Workbooks.Open(Path) ;open an existing file
Xl.Visible := True

Xl.Range("A:A").PasteSpecial(-4163)

You can also just insert an image without the clipboard.

Code: Select all

xlApp := ComObjActive("Excel.Application")
FileName := A_Desktop "\009.jpg"
xlRng := xlApp.Range("C2")
xlShape := xlApp.ActiveSheet.Shapes.AddPicture(FileName, false, true, xlRng.Left, xlRng.Top, -1, -1)

This will insert an image named 009.jpg stored on the desktop to the C2 cell of the active Excel sheet. It is inserted at full size.

If you want to size it and make things look a little neater, you can do:

Code: Select all

; ...Shapes. AddPicture( Filename , LinkToFile , SaveWithDocument , Left , Top , Width , Height )
; Left Top, Width, Height is points, -1 is auto default, can ust Range.{Left|Top|Width|RowHeight} to get positons based on range
xlApp := ComObjActive("Excel.Application")
FileName := A_Desktop "\009.jpg"
xlRng := xlApp.Range("C2")
xlApp.ScreenUpdating := false
xlShape := xlApp.ActiveSheet.Shapes.AddPicture(FileName, false, true, xlRng.Left, xlRng.Top, -1, -1)
xlShape.LockAspectRatio := true
xlShape.Width := 100
xlApp.ScreenUpdating := true

You can control many other properties of a Shape.

FG

[woshichuanqilz72]

As the title said, I found a post before, which said PasteSpecial can do the job.

And I try the code like this: error occur : cant get the PasteSpecial property. So any other way to make it?

Code: Select all

FileSelectFile, Path
Xl := ComObjCreate("Excel.Application")
Xl.Workbooks.Open(Path) ;open an existing file
Xl.Visible := True

Xl.Range("A:A").PasteSpecial(-4163)

You can also just insert an image without the clipboard.

Code: Select all

xlApp := ComObjActive("Excel.Application")
FileName := A_Desktop "\009.jpg"
xlRng := xlApp.Range("C2")
xlShape := xlApp.ActiveSheet.Shapes.AddPicture(FileName, false, true, xlRng.Left, xlRng.Top, -1, -1)

This will insert an image named 009.jpg stored on the desktop to the C2 cell of the active Excel sheet. It is inserted at full size.

If you want to size it and make things look a little neater, you can do:

Code: Select all

; ...Shapes. AddPicture( Filename , LinkToFile , SaveWithDocument , Left , Top , Width , Height )
; Left Top, Width, Height is points, -1 is auto default, can ust Range.{Left|Top|Width|RowHeight} to get positons based on range
xlApp := ComObjActive("Excel.Application")
FileName := A_Desktop "\009.jpg"
xlRng := xlApp.Range("C2")
xlApp.ScreenUpdating := false
xlShape := xlApp.ActiveSheet.Shapes.AddPicture(FileName, false, true, xlRng.Left, xlRng.Top, -1, -1)
xlShape.LockAspectRatio := true
xlShape.Width := 100
xlApp.ScreenUpdating := true

You can control many other properties of a Shape.

FG

Hi bro,

tnx for your code, works for me. The image can insert well. One more question where can learn the com api, so I can make the code by myself in the future.

Tnx again bro ~

[FanaticGuru]

Hi bro,

tnx for your code, works for me. The image can insert well. One more question where can learn the com api, so I can make the code by myself in the future.

Tnx again bro ~

MSDN Microsoft is a good start for reference.

For example this link shows the Shape object.
https://msdn.microsoft.com/en-us/vba/ex ... ject-excel

This shows all the properties of the Shape object that can be read or set. Stuff like xlShape.Width := 100.

Also you can google things and refer to VBA. Stuff like "vba add picture". VBA code is similar to AHK COM. Not exact but it often gives a good idea of how to do things. They are both calling the exact same underlining Windows code, just with different syntax. You have to understand a little about VBA to translate to AHK.

Also you can use the macro recorder built into Excel which will generate VBA code that will often help show the objects, methods, and properties that you need to use for COM.

Also on the forum are several tutorials, also webcast, videos, etc. Several users on the forum have put in a lot of time explaining various aspects of using COM with AHK. I know it pretty well but I still like browsing all the instructional stuff others have put together. Several users have great personal websites about AHK.

FG

[woshichuanqilz72]

Hi bro,

tnx for your code, works for me. The image can insert well. One more question where can learn the com api, so I can make the code by myself in the future.

Tnx again bro ~

MSDN Microsoft is a good start for reference.

For example this link shows the Shape object.
https://msdn.microsoft.com/en-us/vba/ex ... ject-excel

This shows all the properties of the Shape object that can be read or set. Stuff like xlShape.Width := 100.

Also you can google things and refer to VBA. Stuff like "vba add picture". VBA code is similar to AHK COM. Not exact but it often gives a good idea of how to do things. They are both calling the exact same underlining Windows code, just with different syntax. You have to understand a little about VBA to translate to AHK.

Also you can use the macro recorder built into Excel which will generate VBA code that will often help show the objects, methods, and properties that you need to use for COM.

Also on the forum are several tutorials, also webcast, videos, etc. Several users on the forum have put in a lot of time explaining various aspects of using COM with AHK. I know it pretty well but I still like browsing all the instructional stuff others have put together. Several users have great personal websites about AHK.

FG

Can't thanks you more, best wishes to you~