Insert image to Excel programmatically
LBT
I wish to insert JPEG files into the Excel document through programming way.
Is VBScript able to help me achieve my objective? Please advice
Thanks a lot!!!
Michael Harris (MVP)
It so much easier to record a temp Excel macro while doing the task
manually. Use the temp macro as a guide to create the VBScript
equivalent...
My vba vs. vbscript guide...
Automating Office applications externally via VBScript (or any other COM
capable language) starts by creating a reference to the specific Office
application's Application object via CreateObject and the appropriate progid
(e.g., "Word.Application" or "Excel.Application").
Your best bet is to then refer to the Office VBA documentation...
Documentation for Office object models is supplied with every copy of
Office. Start the Office application you're interested in. On the menu bar,
select Help/Contents and Index. In the contents, select Microsoft Visual
Basic Reference/Visual Basic Reference. This will bring up the VBA help
file.
When you read the help on the various objects, properties, methods, etc. you
have to keep in mind that it's written in the context of VBA hosted by a
specific VBA-enabled application, not VBScript. VBA is hosted "from the
inside" by the specific application. As the host, it automatically provides
things to the VBA code that aren't automatic when you automate an
application's object model "from the outside" using VBScript hosted by WSH.
The key items to remember:
--- No objects are automatically exposed to VBScript. You generally use an
explicit CreateObject to get an instance of an object to use as the "root",
usually the ".Application" object.
---In VBA that Application object and it's immediate interface members
(properties/methods) are automatically exposed. In VBScript you refer to the
Application object and it's properties/methods through the object variable
reference returned by the CreateObject.
---Named constants specific to the application aren't exposed. You can
either look them up and code them locally in the VBScript code as Const
variables, or you can use the .wsf file format and a <reference> element to
automatically expose them.
---VBA supports named argument syntax (e.g., ArgName:="argvalue") in method
calls. In WSH hosted VBScript, you have to code all arguments as positional
arguments since named argument syntax is not supported.
Once you understand the "VBA from the inside" vs "VBScript from the outside"
issues and the fundamental differences between VBA and VBScript as separate
but similar languages, you should be able to mentally "port" VBA and even
full VB examples to VBScript.
A common trick scripters often use is recording a VBA macro within an Office
application while performing a task that they want to automate and then
porting the VBA macro code to VBScript.
--
Michael Harris
Microsoft MVP Scripting
LBT
I have another question here. If multiple pictures are going to be inserted
to the Excel sheet, I face problem to arrange the pictures properly in
different rows since the size of the pictures is varying.
Anyway I can get the picture to fit into one cell so that the pictures won't
be overlapped? Currently the picture just spread across couple of cells
(vertically and horizontally) in the Excel sheet and I have problem to
determine which is the next cell I should put the second picture and so and
so forth.
Hopefully I make my question clear. Thanks a lot
Miyahn
> I have another question here. If multiple pictures are going to be inserted
> to the Excel sheet, I face problem to arrange the pictures properly in
> different rows since the size of the pictures is varying.
Take a look at this post.
news:edSRYuBo...@TK2MSFTNGP12.phx.gbl
> Anyway I can get the picture to fit into one cell so that the pictures won't
> be overlapped?
Yes you can.
Here is a sample VBA source code for add-in. (not VBScript.)
Attribute VB_Name = "InsertCellPictures"
Option Explicit
' Add-in for Cell-Size Pictures
' Ver. : 1.0
' Translated: 2005/ 8/16
'
Sub Auto_Open()
If Val(Application.Version) < 9 Then
MsgBox "This add-in works on Excel2000 or later."
ThisWorkbook.Close
End If
If Application.CommandBars("Cell").Controls(5).Caption = _
"Insert Cell-Size Pictures" Then
MsgBox "This add-in has already been registered."
ThisWorkbook.Close
End If
If ThisWorkbook.Sheets(1).Names.Count = 0 Then SetNames
With Application.CommandBars("Cell").Controls.Add(Before:=5, _
Temporary:=True)
.Caption = "Set Picture Quality"
.FaceId = 2144
.OnAction = "PictureSetting"
End With
With Application.CommandBars("Cell").Controls.Add(Before:=5, _
Temporary:=True)
.Caption = "Insert Cell-Size Pictures"
.FaceId = 267
.OnAction = "InsertCellSizePicture"
End With
End Sub
Sub SetNames()
With ThisWorkbook.Sheets(1)
.Names.Add "Quality", .Range("$A$1"): .Range("Quality") = 1#
End With
ThisWorkbook.IsAddin = True: ThisWorkbook.Save
ThisWorkbook.Saved = True
End Sub
Sub Auto_Close()
Dim aCtrl As CommandBarControl
For Each aCtrl In Application.CommandBars("Cell").Controls
If aCtrl.Caption = "Insert Cell-Size Pictures" Or _
aCtrl.Caption = "Set Picture Quality" Then aCtrl.Delete
Next aCtrl
End Sub
Sub InsertCellSizePicture()
Dim Mag, aPath
Dim Target As Variant, X As Single, Y As Single, W As Single, H As Single
Dim FName As String, CellRatio As Single
Target = Application.GetOpenFilename( _
"Picture file (*.jpg;*.gif;*.bmp),*.jpg;*.gif;*.bmp", , _
"File(s) Selection", , True)
If TypeName(Target) <> "Variant()" Then Exit Sub
Mag = ThisWorkbook.Sheets(1).Range("Quality")
Application.ScreenUpdating = False
For Each aPath In Target
With ActiveCell
X = .Left: Y = .Top: W = .Width: H = .Height: CellRatio = H / W
End With
ActiveSheet.Pictures.Insert(aPath).Select
With Selection.ShapeRange(1)
.LockAspectRatio = True
If .Height / .Width > CellRatio Then
.Height = H - 4: .Left = X + (W - .Width) / 2: .Top = Y + 2
Else
.Width = W - 4: .Top = Y + (H - .Height) / 2: .Left = X + 2
End If
X = .Left: Y = .Top: H = .Height: W = .Width
.Width = .Width * Mag: .Cut
End With
Select Case LCase(Mid(aPath, InStrRev(aPath, ".") + 1))
Case "jpg", "bmp"
ActiveSheet.PasteSpecial "Picture (JPEG)"
Case "gif"
ActiveSheet.PasteSpecial "Picture (GIF)"
End Select
With Selection.ShapeRange(1)
.Left = X: .Top = Y: .Height = H: .Width = W
End With
ActiveCell.Offset(, 1).Select ' advance to column direction.
Next aPath
Application.ScreenUpdating = True
ActiveCell.Offset(, -1).Select
End Sub
Sub PictureSetting()
Dim Ans, Mag
Mag = Format(ThisWorkbook.Sheets(1).Range("Quality"), "0.0")
Ans = InputBox("Input Picture Quality.(Low 1.0-3.0 High)", "Picture Quality", Mag)
If IsNumeric(Ans) Then
If Ans < 1# Or Ans > 3# Then Ans = 1#
ThisWorkbook.Sheets(1).Range("Quality") = Ans
End If
End Sub
--
Miyahn (Masataka Miyashita) JPN
Microsoft MVP for Microsoft Office - Excel(Jan 2005 - Dec 2005)
HQF0...@nifty.ne.jp