Saturday, June 20, 2015

Convert all picture in folder to single pdf using Excel VBA

Hi,

Photographer n Artist can now send all their pic in one PDF to client instead of zip file...Following code will help them to import all pictures stored in folder & convert them to handy PDF which can be shared on the go...

Sub Add_PIC_Save_PDF() Dim strPath As String Dim strFileName As String Dim Pic As Picture Dim sh As Worksheet Dim n As Integer Dim cl As Range Application.DisplayAlerts = False Application.ScreenUpdating = False n = ThisWorkbook.Worksheets.Count strPath = "C:\Users\ABC\Desktop\test\" ' change folder to suit strFileName = Dir(strPath & "*.png") ' change file type to suit Set sh = ActiveSheet Do While Len(strFileName) > 0 sh.Select Set Pic = ActiveSheet.Pictures.insert(strPath & strFileName) Range("A1").Select With sh Set cl = sh.Range("A1") With PicSize PicSize.Top = cl.Top PicSize.Left = cl.Left PicSize.Placement = xlMoveAndSize End With With sh.PageSetup .Orientation = xlLandscape .FitToPagesWide = 1 .FitToPagesTall = 1 .Zoom = False .LeftMargin = Application.InchesToPoints(0) .RightMargin = Application.InchesToPoints(0) .TopMargin = Application.InchesToPoints(0) .BottomMargin = Application.InchesToPoints(0) .HeaderMargin = Application.InchesToPoints(0) .FooterMargin = Application.InchesToPoints(0) .PrintQuality = 4000 End With strFileName = Dir End With Set sh = Sheets.Add(After:=Sheets(Sheets.Count)) Loop
sh.Delete
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "\ImageToPdf.pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Do let me know your views..

Cheers!!