create worksheet,input data to excel worksheet cell and save it?

I had retrive data from database and I require to do more with the data. right now I need to put those data input a worksheet.
This is what I have down
private sub datatoexcel()
Dim excelo as object
Dim I as integer
set excelo = createobject("Excel.Sheet")

the problem is now I don't know how to input data into cell??do I use excelo.cell(x,y) = variable to input data or there is a eary way??and how do I save the the object into a xls file after I finish everything?

Thanks
c4marAsked:
Who is Participating?
 
avitalkConnect With a Mentor Commented:
try looking into MSDN - Office Developmeant, they have some examples there.

you need to use objects from a library called :" Microsoft Excel 8.0 Object Library"
which you should add to the references of the project.
that would allow you to access excel objects.

inserting data into excel cells and saving the file would look like this ( I'm inserting random data ):

Private Sub DoExcel()
Dim AppExcel As Excel.Application
Dim Wb As Excel.Workbook
Dim WS As Excel.Worksheet
Dim I As Long, J As Long

Set AppExcel = CreateObject("Excel.Application")
Set Wb = AppExcel.Workbooks.Add
Set WS = Wb.ActiveSheet

With WS.Range("A1")
    For I = 1 To 10
        For J = 1 To 10
           .Offset(I - 1, J - 1) = I * J
            DoEvents
        Next
    Next
End With
Wb.SaveAs App.Path & "\multiple.xls"
AppExcel.Visible = True
Set WS = Nothing
Set Wb = Nothing
Set AppExcel = Nothing
End Sub

I hope this helps.
Avital
0
 
c4marAuthor Commented:
avitalk have been a great help on the application that I am doing, however I still not able to find helpful information on the MSDN. There still have a little problem to about save excel workbook, does anyone know how to do it???
Wb.SaveAs App.Path & "\multiple.xls" (seem like doesn't working, I have try .savetofile, .savefile, but all of them fail to achieve saving excel workbook)
0
 
avitalkCommented:
Hi again,

Wb.SaveAs App.Path & "\multiple.xls"
worked fine in my computer.

the method Wb.SaveAs as additional optional parameters.  try playing with them.

also, try setting an error handler to see if any error happens.

info Excel objects in MSDN can be found in the following URL:
http://msdn.microsoft.com/library/officedev/office97/ORKhtml/FULLTOC.htm

or just search about Excel Object.

0
All Courses

From novice to tech pro — start learning today.