CODEWITHSUNDEEP
excelvbaexportcopy-paste
Zeus
Zeus

Reputation: 1600

Save a range as a picture file/pdf on one page

I am trying to save a range as a picture file. The code below (scraped from another post) works, but I don't want to create a chart page which I then need to delete. Any suggestions?

Sub savedeal()

Dim sSheetName As String
Dim oRangeToCopy As Range
Dim oCht As Chart
Dim myFileName As String, myPath As String

myFileName = Format(Now(), "dd-mmm-yy") & "-" & "DEAL.PNG"
myPath = "D:\Hughs Files\Google Drive\Work Folder\Polaris\Blog\"


Worksheets("BOOK").Range("B15:M45").CopyPicture xlScreen, xlBitmap

Set oCht = Charts.Add

With oCht
    .Export Filename:=myPath & "\" & myFileName, Filtername:="PNG"
End With
End Sub

Upvotes: 1

Views: 1226

Answers (1)

Davesexcel
Davesexcel

Reputation: 6982

This has been discussed for years, if you want it saved as an image you will have to add a chart, even add-ins use a chart.

One thing you can do though is save the desired range as a PDF for example.

Sub RngToPDF()
    Dim sh As Worksheet, rng As Range, Fnm As String

    Set sh = Sheets("Book")
    Set rng = sh.Range("B15:M45")
    Fnm = "C:\Users\Dave\Downloads\TestMe.pdf"

    With sh.PageSetup
        .PrintArea = rng.Address
        .PrintGridlines = True
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With

    sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Fnm


End Sub

Upvotes: 1

Related Questions