p2p.wrox.com Forums

Need to download code?

View our list of code downloads.

  Return to Index  

crystal_reports thread: Re: Sending Report to Excel From VB Application

Message #1 by "Justin" <justin.jones@a...> on Wed, 3 Apr 2002 22:10:30
I used the following code.  This will open the Excel application and then 
insert the data from an MS Access DB into the spreadsheet.

TEMP_DB_PATH is the path to the Access table.  Not sure how to link 
directly to SQL Server.  Still researching that one.  

sql_string = ""
sql_string = "SELECT * FROM tblInspectorOperatorDefects"

'Define the database object for the data export
Dim dbExport As DAO.Database
Set dbExport = DAO.DBEngine(0).OpenDatabase(TEMP_DB_PATH, dbOpenDynaset)

'Create and define the recordset object
Dim rstExport As DAO.Recordset
Set rstExport = dbExport.OpenRecordset(sql_string, dbOpenSnapshot)

'Define the MS Excel object
Set objExcel = New Excel.Application

objExcel.Visible = True

'Add a new sheet to the Excel application
Set objBook = objExcel.Workbooks.Add

Set objSheet = objBook.Worksheets(1)
objSheet.Name = "Inspection Data"

'Setting up and formatting the column headers for the export to Excel
With objSheet
    .Cells(1, 1).Formula = rstExport.Fields(0).Name
    .Cells(1, 1).Font.Bold = True
    .Cells(1, 2).Formula = rstExport.Fields(1).Name
    .Cells(1, 2).Font.Bold = True
    .Cells(1, 3).Formula = rstExport.Fields(2).Name
    .Cells(1, 3).Font.Bold = True
    .Cells(1, 4).Formula = rstExport.Fields(3).Name
    .Cells(1, 4).Font.Bold = True
    .Cells(1, 5).Formula = rstExport.Fields(4).Name
    .Cells(1, 5).Font.Bold = True
    .Cells(1, 6).Formula = rstExport.Fields(5).Name
    .Cells(1, 6).Font.Bold = True
    .Cells(1, 7).Formula = rstExport.Fields(6).Name
    .Cells(1, 7).Font.Bold = True
    .Cells(1, 8).Formula = rstExport.Fields(7).Name
    .Cells(1, 8).Font.Bold = True
    .Cells(1, 9).Formula = rstExport.Fields(8).Name
    .Cells(1, 9).Font.Bold = True
    .Cells(1, 10).Formula = rstExport.Fields(9).Name
    .Cells(1, 10).Font.Bold = True
End With

'Actual function of copying the data from the Access database to the Excel 
objSheet.Range("A2").CopyFromRecordset rstExport

'Closing the link to the database
Set dbExport = Nothing

> I need to export a report to an Excel file without user interaction.  I 
k> now the Report object has several EXCEL Properties but I'm not sure 
o> nes to set.  Any help is appreciated.

  Return to Index