How to create and send values to an excel document

reference the excel object library (project/references):

    Dim myXL As New Excel.Application
    Dim myWkBk As Excel.Workbook
    Dim myWkSht As Excel.Worksheet
   
    Set myWkBk = myXL.Workbooks.Add
    Set myWkSht = myWkBk.Worksheets("Sheet1")
   
    myWkSht.Cells(1, 1) = "THIS IS A TEST"
    myXL.ActiveWorkbook.SaveAs ("<path to save to>")

    myXL.quit

If you want the fastest, you may want to use the VBA built into Excel so the environment is already loaded when you go to run the procedure.  (The only problem I find is that the development seems to be more difficult even though the language is the same.)

Hi macob.
try this.

reference the excel object library (project/references):

after that, put command like command1, and this code...



Private Sub Command1_Click()

' Decleare Application,Workbook and Worksheet
Dim app As New Excel.Application
Dim wbk As Excel.Workbook
Dim wsh As Excel.Sheets

     
    ' Set Workbook and worksheet
    Set wbk = app.Workbooks(0)
    Set wsh = wbkBook.Sheets
       
    ' Visible Excel
    wbkBook.Parent.Visible = True
   
    ' Add sheet
    wshSheet.Add
       
    ' Put in sheet1, in cells(1,1) the value "Test"
    wshSheet(1).Range("A1") = "Test"
   
    ' Close the application
    app.Quit
   
    ' Free the memory of wbk.
    Set wbk = Nothing
   
End Sub

Alon_h:
Comments
Comments are intended to be used as a collaboration tool. Many Experts choose to post their solutions as comments only.

Answers
An answer is a specific solution to a question and should be submitted if it will solve the questioner's problem and doesn't duplicate a previous comment.

Comment Vs. Answer
If you are unsure of your solution, post it as a comment. Members can accept comments as solutions and award you Expert Points for them.

your proposed answer is almost the same as my previous comment.  Why not comment rather than post as answer?

rspahitz, how can I use the VBA built into Excel?

Alon_h, how can I access a document that already exist, and then edit it?

To use Excel VBA, go to the menu bar under Tools | Macro, then pick either "Record Macro" to make a VBA procedure, "Macros" to run/view one, or "Visual Basic Editor" to figure it all out on your own.

Do I need to have Excel installed in the PC that runs the program with Excel VBA or just some dll?

VBA for Excel depends on Excel as its host, so yes.

Others have described how VB hosts Excel, in which case all you need is the Excel object library but not Excel.

VBA cannot be compiled, and can be changed by the users as needed (potentially good or bad if they don't know what they're doing.)

VB can be compiled and therefore only changed by your programmers (potentially good or bad if they're on vacation or leave the company.)

Faster: VBA for Excel
Better (more control): VB with Excel library

mmm thats a problem rspahitz . Because some of the users wont have excel.


To all of you:

At the previous code I get how to create but I really need to edit a file, so first change the name of the Sheet and add somothings there and later rename the second sheet and add something there also. Also I need to add some format such as bold and font size.

Hope you can help me.

Thanks

doesn't end users needs to see that excel file?

What I want is just to create teh file and I dont want to care if they have Excel or not. Because they must send this file to other users.

Then, you can use bobbit code.

Check my previous post, please

I agree--original bobbit31 post.

Check my previous post, please I need some other things also:

At the previous code I get how to create but I really need to edit a file, so first change the name
of the Sheet and add somothings there and later rename the second sheet and add something there also.
Also I need to add some format such as bold and font size.

Hope you can help me.

So what you want is an Excel-like interface that these people can use so that they can create something that will create an Excel spreadsheet for someone else?

Wouldn't it be easier for them to just use Excel?

rspahitz , I thinks you didnt understand me.
What I need is to create an excel document, based in some information that the user enters in the program, but I need this information to have some formatting like bold, font size and font name, in some cells (like subjects).

I hope now you understand me.

OK...You need an interface that accepts certain pieces of information.

The information is manipulated in such a way that it is placed into an Excel document (with formatting) that the user never sees but will be distributed to someone else.

So the input is used to build an Excel file as its output.

Yes my friend

No can do without excel installed on the machine. The excel library described is does NOT allow excel files to be manipulated unless the application is installed. All that this library does is expose the Excel object model to VB so that it can be used. The example given are fine but you will not get it to work on a machine that does not have excel installed. There is no way to encapsulate the excel application itself within VB even if you attempt to embed it in the OLE control as all this does is store the appropriate Class IDs so that it can be instantiated on the target machine. Without having Excel installed this will fail and you will be unsuccessful. The only solution to this is to use something like the Tidestone Formula1 control which allows you to create a spreadsheet and save it in Excel format without having excel itself on the machine. This unfortunately is not free but is perhaps a cheaper alternative to licensing and installing Excel on every machine that will run this application.

You can create excel files without having excel installed by using DAO and the excel installable isam
however you wont be able to do any formatting
but if i remember well the format of an excel file is described in the excel developers manuel
so that will enable you to do anything you want
but please dont ask me how i dont have that manual and i have never done it

Here is a link to the Excel 2.1 file format. This can be read by later versions so you could use this if you wanted.

http://www.wotsit.org/download.asp?f=xls

Pierrecampe you are right about DAO, you can also do it in ADO if you want but as you rightly say there is no way with these methods to do any formatting.

I agree with Tim and Pierre.
But with Pierre commnent you could need to know some intrinsic "byte" of Excel sheet and the need of code to write is wide.

i just thought of something
you can use the office spreadsheet component in a vb program
maybe that will enable you to do what you want
but again i have no idea how to do it

Guys, so if it's so complicated to make this program without excel installed, then give me an example (the fastest and that can do formatting) having excel installed in that machine.

This is what I need:
- To write to an existing excel document
- Also write to a new excel document
- Check values from some cells
- Add values to some cells (with formatting : bold, font size and font type)
- Add a formula in a cell
- Create Sheets and rename them
- Write in diferent sheets

I'm going to increase the point to 250, I hope you cna help me with all this.

Thanks a lot, could you tell me how to resize a col?

Using autofit property of column object?

caould you give me the code? please...

after you add the text (ie. myRange = strTextToAdd
 from the above code) do:

myRange.autofit

good luck

I get an error message "Autofit Method or class failed"