Tip

Excel Subroutines

These subroutines make it simple to dump whatever data you have out to excel. We use these for a lot of reports. I've even built a dynamic report builder that allows users to select a form, select the fields from the form then enter selection criteria and the results are exported directly to Excel.

Sub NewExcelSheet(ssheet As Variant, wsheet As Variant)
'Creates an instance of MS Excel (msexcel) and a new spreadsheet (ssheet)

Set ssheet = CreateObject("Excel.Application")
Set wsheet = ssheet.Workbooks.Add

End Sub

Sub AddEntry(ssheet As Variant, row As Integer, col As Integer, value As Variant, bold As Integer,fnt$, fsz As Integer )

' fnt$ = "Times New Roman"
ssheet.Cells(row, col).Select
With ssheet.Selection.Font
.Name = fnt$
.Size = fsz
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = False
.ColorIndex = xlAutomatic
End With
ssheet.Selection.Font.Bold = bold
'ssheet.Selection.Font.Underline = True
ssheet.ActiveCell.FormulaR1C1 = value
End Sub

Sub SaveExcel(wsheet As Variant, filename$)
'Saves the Excel document as filename$

wsheet.SaveAs filename$

End Sub

Sub CloseExcel(wsheet As Variant)
'Closes the instance of Word

wsheet.Close

End Sub

Sub AttachExcel(wsheet As Variant, rtitem As NotesRichTextItem, filename$)
'Saves the Ms Excel document as filename$ then attaches it as an object to the rich text item. Useful for attaching the Excel document to a Notes document

Dim object As NotesEmbeddedObject
Call SaveExcel(wsheet, filename$)
Set object = rtitem.EmbedObject( EMBED_ATTACHMENT, "", filename$, "thewordfile" )
Call rtitem.AddNewLine( 1 )

End Sub

Sub AutoFitExcelColumns(ssheet As Variant)
'Autofit the entire spreadsheet

ssheet.Cells.Select
ssheet.Selection.Columns.AutoFit
ssheet.Cells(1, 1).Select

End Sub

 

This was first published in November 2000

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.