XL2GIF routine

Location:   http://www.mvps.org/dmcritchie/excel/xl2gif.htm
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

The XL2GIF macro on this page was graciously supplied by Harald Staff

I thought I might add a little item to your "Save as HTML" stuff.  This is a little Excel8/9 routine that prompts for a worksheet range selection and saves this as a GIF image.  We use an extended version of this for web-publishing some weekly reports.  Limitations are the size of a bitmap image copied in Excel, I guess it's a graphics limitation like "maximum possible screen resolution" or something like that.
-- Harald Staff -- mailto:Harald.Staff@nrk.no       
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 Integer
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 Integer, iv As Integer)
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 Integer
Dim Wi As Integer
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="xl2gif.gif">

<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 Examples

You 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 .png 
Sub 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

 ABC
1 A1 B1 C1
2 A2 B2 C2
3 A3 B3 C3
384 bytes

 ABC
1A1B1C1
2A2 B2C2
3A3B3 C3
473 bytes

  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.

Related articles can be found on my Home page in the HTML area, Excel to HTML -- information & downloads and the coding can be found here.  (The macro is xl2HTML)

Adjustment spacing can be forced by including char(160) or &nbsp; (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 is 7KB + 2 KB for the GIF file. for the image included.

 

Paste Picture

  You can copy a chart, then use Edit, copy picture and then simply paste into another Office application (ctrl+v). Office application.

Create a
Picture		 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
figures		  
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 Sub
You 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.

More Related Postings

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. Pasting Pictures

Related Web Pages

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  mailto:DMcRitchie@msn.com.