Using an Excel spreadsheet from VB

[anonymous]

[Originally posted by Chopper]

Can anyone tell me how to use an Excel spreadsheet that has already been created, a template so to speak. I need to insert data into certain cells of a spreadsheet. What is the syntax for something like that.

[anonymous]

[Originally posted by Greg DeBacker]

You add a Reference to the Excel Object Library to your application (Project/References menu. Then select "Microsoft Excel 8.0 Object Library")

You then create an Excel object variable and use it to manipulate Excel.

Option Explicit
'object variable
Dim ApExcel As Object

Private Sub Form_Click()
ÿ ÿ 'Create the object
ÿ ÿ Set ApExcel = CreateObject("Excel.application")
ÿ ÿ
ÿ ÿ ' Make Excel visible
ÿ ÿ ApExcel.Visible = True
ÿ ÿ
ÿ ÿ 'Use this to add a new workbook
ÿ ÿ 'ApExcel.Workbooks.Add
ÿ ÿ
ÿ ÿ 'use this to open an existing workbook
ÿ ÿ ApExcel.Workbooks.Open ("c:\my documents\mybook.xls")
ÿ ÿ
ÿ ÿ 'Add Text to a Cell
ÿ ÿ ApExcel.Cells(1, 1).Formula = "HELLO"
ÿ ÿ
ÿ ÿ 'change the borders.
ÿ ÿ ApExcel.Range("A1:Z1").Borders.Color = RGB(0, 0, 0)
ÿ ÿ
ÿ ÿ 'adjust the column's width.
ÿ ÿ ApExcel.Columns("A:AY").EntireColumn.AutoFit
ÿ ÿ
ÿ ÿ 'select a range
ÿ ÿ ApExcel.Range("A:Z").Select
ÿ ÿ
ÿ ÿ 'change format
ÿ ÿ ApExcel.Selection.NumberFormat = "0"
ÿ ÿ
End Sub

Grex

[anonymous]

[Originally posted by Chopper]

Thanks you for you help! I may need some more later on. Thanks again!!

[anonymous]

[Originally posted by Anonymous]

Exactly what I was looking for!!!ÿ I knew there was a way to specify formats.ÿ Here is how I specified the format of a column so as not to lose leading zeroes (in this instance, column A is the SSN).ÿ This can also be used to format numeric fields/rows/columns, and probably date field formats (I haven't tried it, yet). Thanks, again!

ÿ ÿ 'select a range
ÿ ÿ ApExcel.Range("A:A").Select
ÿ ÿ 'change format
ÿ ÿ ApExcel.Selection.NumberFormat = "000000000"