The XL2GIF macro on this page was graciously supplied by Harald Staff
The subroutine to invoke is GIF_Snapshot to convert a range of cells to a .GIF file.Option Explicit 'Harold Staff -- see http://www.mvps.org/dmcritchie/excel/xl2gif.htm 'XL2GIF_module -- GIF_Snapshot Dim container As Chart Dim containerbok As Workbook Dim Obnavn As String Dim Sourcebok As Workbook Function SelectArea() As String Dim Internrange As Range On Error GoTo Brutt Set Internrange = Application.InputBox("Select " _ & "range to be photographed:", "Picture Selection", _ Selection.AddressLocal, Type:=8) SelectArea = Internrange.Address Exit Function Brutt: SelectArea = "A1" End Function Function sShortname(ByVal Orrginal As String) As String Dim iii As Long sShortname = "" For iii = 1 To Len(Orrginal) If Mid(Orrginal, iii, 1) <> " " Then _ sShortname = sShortname & Mid(Orrginal, iii, 1) Next End Function Private Sub ImageContainer_init() Workbooks.Add (1) ActiveSheet.Name = "GIFcontainer" Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Worksheets(1).Range("A1") ActiveChart.Location Where:=xlLocationAsObject, _ Name:="GIFcontainer" ActiveChart.ChartArea.ClearContents Set containerbok = ActiveWorkbook Set container = ActiveChart End Sub Sub MakeAndSizeChart(ih As Long, iv As Long) Dim Hincrease As Single Dim Vincrease As Single Obnavn = Mid(ActiveChart.Name, Len(ActiveSheet.Name) + 1) Hincrease = ih / ActiveChart.ChartArea.Height ActiveSheet.Shapes(Obnavn).ScaleHeight Hincrease, _ msoFalse, msoScaleFromTopLeft Vincrease = iv / ActiveChart.ChartArea.Width ActiveSheet.Shapes(Obnavn).ScaleWidth Vincrease, _ msoFalse, msoScaleFromTopLeft End Sub Public Sub GIF_Snapshot() Dim varReturn As Variant Dim MyAddress As String Dim SaveName As Variant Dim MySuggest As String Dim Hi As Long Dim Wi As Long Dim Suffiks As Long Set Sourcebok = ActiveWorkbook MySuggest = sShortname(ActiveSheet.Name) ImageContainer_init Sourcebok.Activate MyAddress = SelectArea If MyAddress <> "A1" Then SaveName = Application.GetSaveAsFilename( _ initialfilename: =MySuggest _ & ".gif", fileFilter:="Gif Files (*.gif), *.gif") Range(MyAddress).Select Selection.CopyPicture Appearance:=xlScreen, _ Format:=xlBitmap If SaveName = False Then GoTo Avbryt End If If InStr(SaveName, ".") Then SaveName _ = Left(SaveName, InStr(SaveName, ".") - 1) Selection.CopyPicture Appearance:=xlScreen, _ Format:=xlBitmap Hi = Selection.Height + 4 'adjustment for gridlines Wi = Selection.Width + 6 'adjustment for gridlines containerbok.Activate ActiveSheet.ChartObjects(1).Activate MakeAndSizeChart ih:=Hi, iv:=Wi ActiveChart.Paste ActiveChart.Export Filename:=LCase(SaveName) & _ ".gif", FilterName:="GIF" ActiveChart.Pictures(1).Delete Sourcebok.Activate End If Avbryt: On Error Resume Next Application.StatusBar = False containerbok.Saved = True containerbok.Close End Sub
Harold Staff's code appears above, my implementation notes and comments follow.
<img src="xl2gif3c.gif" border="0">
<img src="xl2gif3c.gif" border="3">
The example on the left is a GIF file created by XL2GIF by Harald Staff and is described on this page. The file is 1471 bytes (197 x 53 pixels). Related articles can be found on my Home page in the HTML area, and the can be found here. (The macro is xl2GIF)
The first example does not include adding some pixels in the VBA code to account for cell boundaries. Left this one in to show the difference, since you are working with both the VBA and the HTML. The missing or faint portion is caused by the VBA code not providing sufficient dimensions, not to be confused with Border="0" in HTML coding for image (IMG).
The example in color is 201 x 63 pixels, Harold's code was improved a little to provide for the extra pixels used by cell boundaries in the VBA code.
The last example includes a cell boundary in the HTML image source.
Some Additional Code ExamplesYou can change the filter to whatever you want. I notice that if you convert a Word document to HTML you will get both .gif and .pngSub expchart() ' Efstratios Malasiotis,("Stratos"), 2000-06-19 programming ActiveChart.Export FileName:="D:\MyChart.gif", FilterName:="GIF" ActiveChart.Export FileName:="D:\MyChart.jpg", FilterName:="JPEG" ActiveChart.Export FileName:="D:\MyChart.png", FilterName:="PNG" 'To export a picture, first paste it in an empty chartobject ' and then export the chart. End Sub
A B C 1 A1 B1 C1 2 A2 B2 C2 3 A3 B3 C3
A B C 1 A1 B1 C1 2 A2 B2 C2 3 A3 B3 C3 The example on the left was created by an earlier version of XL2HTMLx and is 384 bytes, and includes the column and row headers, the example below it is a more current vesion that includes color (473 bytes). The XL2HTML macro will produce the table without the Excel row and column headings.
Adjustment spacing can be forced by including char(160) or (non-breaking space) characters, or by fiddling with cell boundaries and spacing in the generated code. The second appears more compact because the macro now generates: cellspacing="0" cellpadding="0"
For what it is worth, if you were to select the area A1:C3, copy it, hold shift and create a picture, then paste that picture into an HTML Word Document your document would be 5,737 bytes. So you can instantly realize the overhead involved with conversions with so-called web features. frankly I do not have 5K bytes to spare, but it greatly increases download time and storage space. Also while it may reproduce the original document more correctly it usually produces a very bad HTML document overridding features that HTML does so much better. The complete document you are reading here as of 2003-09-10 is 14.8Kb for 15 images + 18.7 KB of HTML code, and I do now have some free space on my hard drive.
Paste Picture You can copy a chart, then use Edit, copy picture and then simply paste into another Office application (ctrl+v). Office application.
You can create a picture from a group of selected cells as follows:
select contiguous cells, copy (ctrl+c), use Shift+Edit. You can copy picture, then using normal paste (ctrl+v), or even create a GIF file as described below. These two taken together can substitute for Harold's code.
GIF files from
Public Sub ChartToGIF() 'David McRitchie 2000-04-17 Dim TPath As String, SName As String, Istr As String TPath = "c:\temp\" SName = "chart01" ActiveChart.Export filename:=TPath & SName & ".gif", _ filtername:="GIF" Istr = "<img href=""" & TPath & SName & _ ".gif"" Alt=" & SName & """>" MsgBox ActiveChart.Name & " image can be included " _ & "in HTML by pasting this code" & _ Chr(10) & Istr End SubYou can choose your filter in the above as GIF, JPEG, TIF, or PNG.
To store in same path as Excel file use ThisWorkbook.Path
If you prefer you can use an inputbox. using a default to supply filename.
- Filter must be available: (see help file) [ref]
You need to have the GIF filter installed to export a single chart to a GIF file using VBA. To see which graphics filters are installed, check the following registry key,
HKEY_LOCAL_MACHINE\Software\Microsoft\Shared Tools\Graphics Filters\Export
If no GIF filter is installed, you'll need to use the Office installation program to install it.
More Related Postings (#more_related)The following list was started with the following Google Usenet Achive Search:
gif & save & chart* & sub & ~g *excel*
Harold Staff has many great postings, thought I'd list some here that almost constitute web pages.
- Saving multiple charts as multiple gifs, Tim Williams 1999-09-10.
Save as .GIF files all charts found in all of the worksheets of the active workbook.
- Addin to copy selected sheets, 2001-03-16, Addin to select which sheets from the active workbook should be copied into a fresh new workbook, and in there which sheets should have their formulas removed and replaced with the cell values. This is a text posting and tells how to create the addin with the buttons and everything, from beginning to end, and as Harald said -- It's a very useful -- and I believe him.
- pasting a picture from a URL to Excel at a specific location. Norman Harker, 2001-10-11, programming.
An additional example from Tom Ogilvy 2001-11-14 to obtain picture from local files and place it at and set it to the dimensions of the selected cell.
Alternatives (#alternatives)Someone asked about xl2gif in April 2008, nobody has asked for years because there are so many easier alternatives.
Free with Windows (#free)You may already have software on you machine that you have licensed, there are many pay for, shareware, and free possibilities.
Windows comes with MS Paint, in Vista called Paint. You can simply click on "Print Screen" then bring up Paint (Start, All programs, Accessories, Paint) then paste into Paint, then use the selection tool (dotted rectangular outline) from the tools, select area, then Crop (under Image), then File, Save As
Documentation: Windows Vista Help: Using Paint, which is named in http://en.wikipedia.org/wiki/Paint_(software)
More Alternatives (#more_alternatives)Some more alternatives some free, some licensed, some pay for.
- SnagIT (licensed), screen shots and modifications, I use it mainly as tied in with Print Screen, then select the area, crop and save. $39.95 or 30 day trial.
- SourceForge.net: Greenshot, "Save a screenshot or a part of the screen to a file within a second. Apply text and shapes to the screenshot. Offers capture of window, region or full screenshot"
- FastStone Screen Capture, $19.95
- Mirek's Free Windows Software, free, donationware
Related Web Pages
- Excel to HTML on my site. xl2html and xl2htmlx macros.
- Some Examples on my Toolbars page.
(you can copy these 16 bit x 16 bit buttons)
- Adding links to an image map with links to go with a GIF image. One such site -- NCDesign HTML Design Guide, additional sites might be found with a Google toolbar search: map gif HTML image usemap, which might lead you to How To Make A USEMAP. Then you could actually check out the standards at W3C.org where you might look first at Index of the HTML 4 Elements then at MAP and into Objects, Images, and Applets in HTML documents. You may also want some mapping software which you can probably find something suitable and free at freeware downloads: Image Mappers.
- Commercial solutions such as Adobe PhotoShop®, and SnagIt® can be used, you know where to find their sites or retail stores without me linking to them (I use SnagIt). A viable free packge is Screenshot Captor. All of them allow you to annotate your extracted imges.
- Creating a picture catalog through Mail Merge with external pictures -- Cindy Meister. ScreenRip32 - Progency Software, freeware screen capture utility that let's you capture areas of the screen with a variety of methods, Win95/98/NT compatible. Supports different image formats such as ICO, BMP, JPG, and GIF support (animated or standard). There may be followup in a later posting 2004-04-10, if not at least shows some methods of working with pictures for a web page.
- Copy Picture, Microsoft PowerPoint: ruler question in Powerpoint 2000, to be checked out -- can Excel really be be copying an entire workbook into another application with a straight copy for each chart copied unless you copy as a picture [Shift+Edit, Copy Picture].
- Export GiF and add-in at Rob's Excel VBA Pages. Rob Bruce has VBA utilities on his site for Excel, his main newsgroup is probably Excel-L.
- Graphics Exporter addin «, Andy J. Pope, will allow you to export pictures, shapes, charts and more to image files.
- Harald Staff has also written a document on Pivot Tables, you can compare his to Microsoft's and other documentation. If you have Excel 2000 you can paste Harald's example into your spreadsheet and work along step by step with the descriptions.
- Make a GIF copy from a Clipboard Copy «, Dan Maguire (AC6LA), Antenna Modeling Software and Other Utility Programs. Make a GIF file from any picture or bitmap copied to the clipboard, with optional resizing. This utility requires Excel 97 or later but the clipboard copy can be made with Excel, Word, or any other program that can copy a bitmap or metafile to the clipboard. ListClipboardFormats proc included for debugging provides a list of formats used in current clipboard. [2004-01-30] Note: Tied into supplied spreadsheet, suggest looking at defined names then move and expose defined name DirExportGif from under button so you can specify library to save into ahead of time rather than just within macro. /\
- Mirek's Free Windows Software, MWSnap, Mirek Wójtowicz, for snapping (capturing) images from selected parts of the screen.
- Paste Picture Creates a bitmap file from a chart, a range, or from the clipboard as a bitmap or metafile. Choices are an image as either a bitmap (better for same-size copies) or a metafile (better when zooming/stretching).
- PastePicture The entry point for the routine
- CreatePicture Private function to convert a bitmap or metafile handle to an OLE reference
- fnOLEError Get the error text for an OLE error code Picture Display dependent on a cell value, misc, J.E. McGimpsey, 2003-02-05.
- Webbify Excel, Dermot Balson -- Update websites from Excel, Download files without web queries, An Excel web browser, An excel web server.
You are one of many distinguished visitors who have visited my site here or in a previous location since this page was created on March 4, 2000. Return to TOP.
The primary feature on this page is the XL2GIF macro provided by provided by Harold Staff.
Visit [my Excel home page] [Index page] [Excel Onsite Search] [top of this page]
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved