Exporting range to images using VBA

[atavane]

I have multiple ranges ( with range names assigned) in a workbook. I would like to export all the range data into multiple jpeg files to my local drive using VBA. I know how to do it one at a time. It is very tedious and time consuming since I have 100 of those in the workbook. I would like to automate it using VBA so that I can repeat the process if the data changes in those ranges over time. Any help would be appreciated.Thanks

 

[Hui]

Atavane


If I understand you right you have a lot of Named Areas that you want to regularly export


I would encourage you to name the areas according to some pattern like:

Test1

Test2

Test3

etc


Then you can use the following code to Extract all the areas named Test* as pictures

They will be saved in c: and called Test1-2013-3-8.jpg etc


Code:

-----

Sub Export_Pictures()
    Dim Nm As Name
    Dim rgExp As Range

'   Loop through each named formula
    For Each Nm In Names
        If Nm.Name Like "Test*" Then 'Change template to match your naming convention

'          Copy range as picture onto Clipboard
           Range(Nm).CopyPicture Appearance:=xlScreen, Format:=xlBitmap

'          Create an empty chart with exact size of range copied
           With ActiveSheet.ChartObjects.Add(Left:=Range(Nm).Left, Top:=Range(Nm).Top, Width:=Range(Nm).Width, Height:=Range(Nm).Height)
                .Name = "TempArea"
                .Activate
           End With

'          Extract the date for renaming purposes
           myDate = Trim(Str(Right(Date, 4))) + "-" + _
           Trim(Str(Mid(Date, InStr(1, Date, "/") + 1, 2))) + "-" + _
           Trim(Str(Left(Date, InStr(1, Date, "/") - 1)))

'          Paste into chart area, export to file, delete chart.
           ActiveChart.Paste
           ActiveSheet.ChartObjects("TempArea").Chart.Export "C:" + Nm.Name + "-" + myDate + ".jpg"
           ActiveSheet.ChartObjects("TempArea").Delete
        End If
    Next
End Sub

 

[Fred Esquivel]

Hello Hui,
I really like your code, it runs fast, and it is simple to use when you have a lot of documents with ranges to export. However, I have a weird problem. The code runs great if I run it manually, but when I call it or run it directly on workbook open, it exports the right size image according to the range, but it is completely blank, just a white background with the right dimensions.

I modified the code a bit to suit my needs:

Sub Export_Ranges()
Dim Nm As Name
Dim rgExp As Range
For Each Nm In Names
If Nm.Name Like "mctele*" Then
Range(Nm).CopyPicture Appearance:=xlScreen, Format:=xlBitmap
With ActiveSheet.ChartObjects.Add(Left:=Range(Nm).Left, Top:=Range(Nm).Top, Width:=Range(Nm).Width, Height:=Range(Nm).Height)
.Name = "TempArea"
.Activate
End With
ActiveChart.Paste
ActiveSheet.ChartObjects("TempArea").Chart.Export "\Dashboard\Telephony\" + Nm.Name + ".png"
ActiveSheet.ChartObjects("TempArea").Delete
End If
Next
End Sub

I have a couple of macros that run before this on, one that refreshes data from a DB, and the other filters the right time and date on the pivots. Any idea why it runs fine manually and not being called (or ran directly) from ThisWorkbook? is it not meant to run on a Private Sub?

Fred.

 

[Fred Esquivel]

Bump

 

[SirJB7]

Hi, Fred Esquivel!

Are you very hurried? If so please take a break and dedicate 5 minutes to what follows. Posting without adding any useful information just for bumping up a topic doesn't guarantee neither a faster assistance nor getting the interest of people who might be reading that post, but rather the opposite effect of discouraging them.

If you'd have read the 1st forum at the main page...
http://chandoo.org/forum/forums/new-users-please-start-here.14/
...you should have noticed this points (and if you did it seems as if you should do it again):

"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."

"If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."

"Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out."

Regards!

 

[Pratik Parmar]

Hi, Fred Esquivel!

Are you very hurried? If so please take a break and dedicate 5 minutes to what follows. Posting without adding any useful information just for bumping up a topic doesn't guarantee neither a faster assistance nor getting the interest of people who might be reading that post, but rather the opposite effect of discouraging them.

If you'd have read the 1st forum at the main page...
http://chandoo.org/forum/forums/new-users-please-start-here.14/
...you should have noticed this points (and if you did it seems as if you should do it again):

"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."

"If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."

"Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out."

Regards!

Absolutely True "Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here."