Friday, February 24, 2012

Insert Image to Word, Resize Image, Insert Borders using VBA Excel

The program inserts an image to a word file and resizes the images and inserts a border.

Explanation

This VBA program is developed to extract an image and insert it to word file resize the image according the settings in the worksheet and surround the image with a border. The image can be re-sized using this code but the image will not change in terms of size in kilobytes. To compress an image using VBA is not possible this has to be done manually.

In order to make the program work the reference “Microsoft Word XX.X Object Library” needs to be enabled.Example file of the VBA code is available for downloading at the bottom of this web page, enjoy! Or just copy and paste the code directly from this page.

Code

Public Sub Insert_Image_to_Word_Resize_Image_Insert_Borders_using_VBA_Excel()

Dim Insert_Image_to_Word_Resize_Image_Insert_Borders_using_VBA_Excel_APP As Word.Application
Dim Insert_Image_to_Word_Resize_Image_Insert_Borders_using_VBA_Excel_DOC As Word.Document
Set Insert_Image_to_Word_Resize_Image_Insert_Borders_using_VBA_Excel_APP = CreateObject("Word.Application")

Dim PlaceOfWordFile As String
Dim NameOfWordFile As String

PlaceOfWordFile = Range("B4").Value
NameOfWordFile = Range("B5").Value

PlaceOfImageFile = Range("B6").Value
NameOfImageFile = Range("B7").Value

NamePlaceImage = PlaceOfImageFile + "\" + NameOfImageFile
NamePlace = PlaceOfWordFile + "\" + NameOfWordFile

Insert_Image_to_Word_Resize_Image_Insert_Borders_using_VBA_Excel_APP.Visible = True

Set Insert_Image_to_Word_Resize_Image_Insert_Borders_using_VBA_Excel_DOC = Insert_Image_to_Word_Resize_Image_Insert_Borders_using_VBA_Excel_APP.Documents.Open(NamePlace, ReadOnly:=False)

Set WORD_Image = Insert_Image_to_Word_Resize_Image_Insert_Borders_using_VBA_Excel_APP.Selection.InlineShapes.AddPicture(NamePlaceImage, False, True)
   
HeightOfImage = Range("D5").Value
   
With WORD_Image
    H = .Height
    B = .Width
    Ratio = H / B
    .Height = HeightOfImage
    .Width = HeightOfImage / Ratio
End With

WORD_Image.Borders.OutsideLineStyle = wdLineStyleSingle

Insert_Image_to_Word_Resize_Image_Insert_Borders_using_VBA_Excel_DOC.Save
Insert_Image_to_Word_Resize_Image_Insert_Borders_using_VBA_Excel_APP.Quit

Set Insert_Image_to_Word_Resize_Image_Insert_Borders_using_VBA_Excel_DOC = Nothing
Set Insert_Image_to_Word_Resize_Image_Insert_Borders_using_VBA_Excel_APP = Nothing

End Sub

No comments:

Post a Comment