VB Forum / General 2 / November 2006

Following is a VERY simple example of using the application and
workbook objects.  Everyone has a different way of doing it, so you'll
probably get quite a number of responses, but here's mine.

If you're using multiple worksheets, then you'll need to modify this
example to use them.

There is a lot of useful information in the CHM file on how to use the
application and workbook/worksheet objects - depending on what version
of excel you are using.  VBAXL10.CHM, VBAXL9.CHM, etc.

A couple useful links:
http://www.mrexcel.com/archive/General/

Dim objExcel          As Object
Dim objWorkbook   As Object

Dim i    As Long
Dim J   As Integer

'//instantiate excel object - better to late bind if
'//you don't know what version of excel is being used.
'//otherwise you can early bind it in project references

Set objExcel = CreateObject("Excel.Application")

If Err.Number <> 0 Then
    MsgBox "Unable to open Excel, export canceled"
    Me.sbrStatus.Panels(2) = "Can't open Excel, export canceled"
    '//do some error handling
End If

'//instantiate new workbook within excel
Set objWorkbook = objExcel.Workbooks.Add
objExcel.windows(1).Caption = " "

objWorkbook.sheets(1).Activate

'//dump a flexgrid to excel
With flexgrid
   For i = 0 To .Rows - 1
    For J = 1 To .Cols - 1
       objWorkbook.Activesheet.Cells(i + 1, J).Value = .TextMatrix(i, J)
    Next
   Next
End With

'//make column headings bold
objWorkbook.Activesheet.Rows("1:1").Select
objExcel.Selection.Font.FontStyle = "Bold"

With objExcel
   .Activesheet.usedrange.Select   '//select everything that is
populated
   .Selection.Columns.AutoFit  '//resize columns for best fit
   .Selection.Rows.HorizontalAlignment = 2 '//align left
   .Selection.Rows.VerticalAlignment = 3   '//align center
End With

objWorkbook.Activesheet.Cells(1).Select

objExcel.Visible = True

'//destroy the objects
Set objWorkbook = Nothing
Set objExcel = Nothing

'For Win2000 and Excel 2000 you need the following library reference,
VERY IMPORTANT:
  "Microsoft Excel 9.0 Object Library"  from your Project Reference
Menu.

An Excel/VB Relationship is very picky.  You must do everything right
or you'll lock up your Excel session which is a big pain..
The following should get you started in a Write = True environment.
For read only, you'll need more tweaking. Here's hoping I haven't
left anything out!!
Good Luck!

******Declarations Section***************

Dim objWorkbook As Object
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook

******FormLoad***********************
Set xlApp = New Excel.Application
xlApp.Workbooks.Add
xlApp.DisplayAlerts = False            'Shuts off user prompts
Application.Interactive = False            'ditto

Set xlBook = xlApp.Workbooks.Open(FileName:="C:\MyWorksheet.xls", _
   updatelinks:=0, _
   ReadOnly:=False, _        'So you can write to worksheet
   Format:=2)

*******Do Stuff***************************

xlBook.Sheets(1).Cells(24, 92).Value = MyVariable  '(or whatever....)

   '(24 = Row, 92 = Column on sheet)

*****Clean up before exiting program*******

xlApp.DisplayAlerts = False                'Turns on user prompts
Application.Interactive = True            'Turns on prompts
xlApp.ActiveWorkbook.Close True        'Needed to Save changes to s/s.
Set xlApp = Nothing
Set xlBook = Nothing
Application.Quit                                  'Very Important!

'end of code

dave
dsm, ia usa