SyntaX TerroR,
Let's assume that you have a table of values that you want in the signature. For the example below,
the first column of the table must contain a unique string for the export filename. Then the next
columns need to each contain what should go on separate rows of the signature: name in one, address,
etc. in each of the next columns....
Set up a single cell (in this example, H10) tall and wide enough to contain all the signature data
and your logo file. Also, select all the cells and turn the cell borders to white, so that they
don't show up in the gif file. I inserted the logo to the left of cell H1, and right aligned the
text. Then select the data table (all columns, but not the header row), and run the macro
Export_SignatureFiles. It will take a while, since GIF export is slow in Excel. You can use
multiple cells, whatever, but the basic technique works fine.
HTH,
Bernie
MS Excel MVP
Option Explicit
'Based on Harold Staff's code
'see
http://www.mvps.org/dmcritchie/excel/xl2gif.htm
Dim Container As Chart
Dim ContainerBook As Workbook
Dim Obnavn As String
Dim SourceBook As Workbook
Sub Export_SignatureFiles()
Dim ExportAddress As String
Dim SaveName As Variant
Dim MySuggest As String
Dim Hi As Long
Dim Wi As Long
Dim Suffix As Long
Dim myCell As Range
Dim i As Integer
ExportAddress = "$H$10"
Set SourceBook = ActiveWorkbook
For Each myCell In Selection.Columns(1).Cells
MySuggest = myCell.Value
ImageContainer_init
SourceBook.Activate
SaveName = MySuggest & ".gif"
Range(ExportAddress).Value = myCell.Offset(0, 1).Value
For i = 3 To Selection.Columns.Count
Range(ExportAddress).Value = Range(ExportAddress).Value & Chr(10) & myCell.Offset(0, i - 1).Value
Next i
Range(ExportAddress).CopyPicture Appearance:=xlScreen, _
Format:=xlBitmap
Hi = Range(ExportAddress).Height 'adjustment for gridlines
Wi = Range(ExportAddress).Width 'adjustment for gridlines
ContainerBook.Activate
ActiveSheet.ChartObjects(1).Activate
MakeAndSizeChart ih:=Hi, iv:=Wi
ActiveChart.Paste
ActiveChart.Export Filename:=LCase(SaveName), FilterName:="GIF"
ActiveChart.Pictures(1).Delete
SourceBook.Activate
ErrHandler:
On Error Resume Next
Application.StatusBar = False
ContainerBook.Saved = True
ContainerBook.Close
Next myCell
End Sub
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 ContainerBook = 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
