If you do go this way, have a look at the .CopyFromRecordset method of the
Range object--it's supposed to be faster than walking the cells.

But if you can manage to keep all your data in one place (e.g., either
Access or Excel), I'd reccomdend that first.  It's nice to eliminate
opportunities for duplicated info to fall out of synch...

HTH,

-Roy

-----Original Message-----
From: coral@j... [mailto:coral@j...]
Sent: Saturday, May 19, 2001 6:55 PM
To: professional vb
Subject: [pro_vb] Re: Populating an Excel Spreadsheet with data Using a
VB program


I find that writing a spreadsheet from VB is not too hard.  The way that I 
do it is to create the spreadsheet in Excel first with some sample data 
with all the headings, formatting and formulas created, then delete the 
sample data.

Then in VB, read your 'template', fill in the data and save with a new 
name.

Private Sub ExportToExcel()

Dim oXLApp As Excel.Application
Dim oXLWBook1 As Excel.Workbook
Dim oXLWSheet1 As Excel.Worksheet
Dim ExcelRunning As Boolean
Dim intHrsRowNo As Integer
Dim i As Integer

   
   'setup Excel spreadsheet
   ExcelRunning = IsExcelRunning()
   If ExcelRunning Then
       Set oXLApp = GetObject(, "Excel.Application")
   Else
       Set oXLApp = CreateObject("Excel.Application")
   End If
   Set oXLWBook1 = oXLApp.Workbooks.Open(App.Path & "\Wages Hours 
Blank.xls")
   Set oXLWSheet1 = oXLWBook1.Worksheets("Draft")
   
   oXLWSheet1.Range("D6").Value = Format(Date, "dd/mm/yy")
   
   intHrsRowNo = 12
   For i = 1 to 10
      oXLWSheet1.Cells(intHrsRowNo, i).Value = i
   next i

   'save spreadsheet and close excel
   oXLWBook1.SaveAs App.Path & "\Wages Hours " & Format(Now, "ddmmyyhhnn") 
& ".xls"
   oXLWBook1.Close SaveChanges:=True
   oXLApp.Quit
   
   Set oXLWSheet1 = Nothing
   Set oXLWBook1 = Nothing
   
   If Not ExcelRunning Then oXLApp.Quit
   Set oXLApp = Nothing
     
End Sub

Function IsExcelRunning() As Boolean
Dim xlApp As Excel.Application

   On Error Resume Next
   
   Set xlApp = GetObject(, "Excel.Application")
   IsExcelRunning = (Err.Number = 0)
   Set xlApp = Nothing
   Err.Clear
   
End Function

Coral

> I want to create a VB program where the data that is retrieved from the 
> user will first be added to a database using Access.  This part I have 
> done and would like to take the information inside the database and 
> populate a spreadsheet in Excel.  Any suggestions on where to begin my 
> research on this and how to go about designing and implementing this 
idea?
> 
> Or, do you suggest another route all together (meaning dumping the 
> spreadsheet idea and just creating reports from within Access)?
> 
> Any suggestions would be helpful.
> 
> Thanks,