Range to Image

lawvictor

New Member
Joined
Jul 29, 2017
Messages
27
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.
 
can i define the location of the paste within the chart area. is that possible?
I know of no way to do that :confused:

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

Code:
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
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
I know of no way to do that :confused:

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

Code:
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top