Output a function result to Excel

  • Thread starter Thread starter Philippe
  • Start date Start date
P

Philippe

Hi,

I have a function that opens many recordset and count the
values of those recordset. For now the function outputs
the value to
Debug.print
or to a text file with:

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("c:\testfile.txt", True)

This is nice, but I want more :) I want to output the
result to an Excel file and maybe format it. Btw, I'm
using DAO. Any ideas?

:P hilippe
 
Philippe said:
Hi,

I have a function that opens many recordset and count the
values of those recordset. For now the function outputs
the value to
Debug.print
or to a text file with:

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("c:\testfile.txt", True)

This is nice, but I want more :) I want to output the
result to an Excel file and maybe format it. Btw, I'm
using DAO. Any ideas?
Click to expand...

1) Create a temp table
2) stuff the data into it.
3) Run an export on the table to excel.
4) Clean up the temp table
5) done!
 
I have found a solution in the MS help files:

'Creation du rapport Excel
Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")

' Make Excel visible through the Application object.
ExcelSheet.Application.Visible = True
' Place some text in the first cell of the sheet.
ExcelSheet.Application.Cells(i, j).Value = "Report"

' Save the sheet to C:\test.xls directory.
ExcelSheet.SaveAs "C:\TEST.XLS"
' Close Excel with the Quit method on the Application
object.
ExcelSheet.Application.Quit
' Release the object variable.
Set ExcelSheet = Nothing
 
Back
Top