Range to Image

[lawvictor]

Hi All,
I ma writing a script to covert a given range into image, i found a code from which was working fine as long as the range was smaller width but the moment i increased the width of the column it is not working, the whole image is not creating. the below is the steps how the code works,
1) selects the range, find it height and width,
2) inserts a chart and changes the height and width to the range height and width,
3) paste the copied range into the chart
4) export the chart as image.

i find that the chart gets modified to the size of the range however the image pasted in it is no the full image,

any help would be useful.

 

[Yongle]

can i define the location of the paste within the chart area. is that possible?

I know of no way to do that

Something else to try (works for me)
- amend the filepath (end path with path separator)
- then run from the sheet that contains the range to copy
- and look at file Yongle12345.pdf

Note - You may need a different adjustment to get the images to fit neatly together 0.75 worked for me

Sub CreatePDF()
    Const fPath = "C:[SIZE=3][COLOR=#ff0000]\[/COLOR][/SIZE]Test[SIZE=3][COLOR=#ff0000]\[/COLOR][/SIZE]subfolder[SIZE=3][COLOR=#ff0000]\[/COLOR][/SIZE]"      
    Dim a As Long, Temp As Worksheet, Ws As Worksheet, Pic As Shape, L As Double, T As Double, W As Double

    Set Ws = ActiveSheet: Set Temp = Sheets.Add(after:=Sheets(Sheets.Count))
    L = 0
    For a = 0 To 124 Step 4
        Ws.Range("A1:D30").Offset(, a).Copy
        Temp.Cells(1, 1).Select
        Temp.Pictures.Paste.Select
        Set Pic = Temp.Shapes(Temp.Shapes.Count)
        With Pic
            .Left = L: .Top = T
            W = .Width:
            L = L + W - [COLOR=#006400]0.75[/COLOR]
        End With
    Next a
   
    With Temp.PageSetup
        .Zoom = False
        .FitToPagesTall = 1
        .FitToPagesWide = 1
    End With
    
    Temp.ExportAsFixedFormat 0, fPath & "Yongle " & Round(Timer, 0)
    
End Sub

 

[lawvictor]

I know of no way to do that

Something else to try (works for me)
- amend the filepath (end path with path separator)
- then run from the sheet that contains the range to copy
- and look at file Yongle12345.pdf

Note - You may need a different adjustment to get the images to fit neatly together 0.75 worked for me

Sub CreatePDF()
    Const fPath = "C:[SIZE=3][COLOR=#ff0000]\[/COLOR][/SIZE]Test[SIZE=3][COLOR=#ff0000]\[/COLOR][/SIZE]subfolder[SIZE=3][COLOR=#ff0000]\[/COLOR][/SIZE]"      
    Dim a As Long, Temp As Worksheet, Ws As Worksheet, Pic As Shape, L As Double, T As Double, W As Double

    Set Ws = ActiveSheet: Set Temp = Sheets.Add(after:=Sheets(Sheets.Count))
    L = 0
    For a = 0 To 124 Step 4
        Ws.Range("A1:D30").Offset(, a).Copy
        Temp.Cells(1, 1).Select
        Temp.Pictures.Paste.Select
        Set Pic = Temp.Shapes(Temp.Shapes.Count)
        With Pic
            .Left = L: .Top = T
            W = .Width:
            L = L + W - [COLOR=#006400]0.75[/COLOR]
        End With
    Next a
   
    With Temp.PageSetup
        .Zoom = False
        .FitToPagesTall = 1
        .FitToPagesWide = 1
    End With
    
    Temp.ExportAsFixedFormat 0, fPath & "Yongle " & Round(Timer, 0)
    
End Sub

yes this is something similar what i meant, if i can places it in the chart object in an orderly manner side by side all the ranges then exporting it would give me a one single image file. Yongle in the above code in get the range in 5 sheets, i guess its due to the printer driver which allows certain paper size as maximum it can be used to export it to pdf.

 

[Yongle]

Try changing the printer that you are printing to
I select printer MicrosoftPrintToPDF as my printer and then it works for me
Printer Microsoft XPS Document Writer also works

 

[lawvictor]

thanks,
I had to settle with saving the range as single file html, when saved as pdf the images in the range is loosing its resolution.
publishing it to single page html looks good for time though it doesn't serves the purpose.
Untill i find a resolution to convert the image to a good jpeg or some image format i may have to go with html file.

thanks to ken, jaffar, & Yongle for all the valuable tips & suggestions.

 

[Yongle]

thanks for the feedback
good luck

 

[Kenneth Hobson]

Right, if you can not get your Print Preview to display as you want manually, a macro won't be able to either. With 70 width columns for that range, it results in 6 pages for me. I guess compression to fit columns on one page only goes so far.

It is a common practice to convert a range to html for the htmlBody of an Outlook object. Ron de Bruin has a routine that does that easily. His returns a string but can be easily modified to just create the file and not return the string in the file.