Solved

How to create and send values to an excel document

Posted on 2001-07-18
31
200 Views
Last Modified: 2008-02-07
I want to create an excel document, and send some values to its cells. How can I do this? I need to know the fastest and better way...

Thanks in advance
0
Comment
Question by:macob
  • 12
  • 6
  • 4
  • +4
31 Comments
 
LVL 18

Expert Comment

by:bobbit31
ID: 6295629
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
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6295703
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.)
0
 

Expert Comment

by:Alon_h
ID: 6295739
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
0
 
LVL 18

Expert Comment

by:bobbit31
ID: 6295757
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?
0
 

Author Comment

by:macob
ID: 6296154
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?
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6296163
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.
0
 

Author Comment

by:macob
ID: 6296192
Do I need to have Excel installed in the PC that runs the program with Excel VBA or just some dll?
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6296221
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
0
 

Author Comment

by:macob
ID: 6296237
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
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6296249
doesn't end users needs to see that excel file?
0
 

Author Comment

by:macob
ID: 6296255
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.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6296287
Then, you can use bobbit code.
0
 

Author Comment

by:macob
ID: 6296304
Check my previous post, please
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6296341
I agree--original bobbit31 post.
0
 

Author Comment

by:macob
ID: 6296362
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.

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 22

Expert Comment

by:rspahitz
ID: 6296401
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?
0
 

Author Comment

by:macob
ID: 6296412
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.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6296442
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.
0
 

Author Comment

by:macob
ID: 6296467
Yes my friend
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 6297637
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.
0
 
LVL 6

Expert Comment

by:pierrecampe
ID: 6297991
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
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 6298109
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.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6298128
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.
0
 
LVL 6

Expert Comment

by:pierrecampe
ID: 6299316
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
0
 

Author Comment

by:macob
ID: 6299819
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.
0
 
LVL 18

Accepted Solution

by:
bobbit31 earned 250 total points
ID: 6299894
this should get you well on your way:
not completely tested and i tried to answer most of your questions.  I didn't notice that you wanted to write in different sheets until i was finished, but if you look at the code i'm sure you can figure out how to do it.

Private Sub Command1_click()
    Dim myXL As New Excel.Application
    Dim myWkBk As Excel.Workbook

    '' open/create new workbook
    Set myWkBk = openExcel(myXL, "C:\my documents\test.xls", True)
   
    '' create a new worksheet
    createWorkSheet myWkBk, "testSheet"
   
    '' get a cell value
    MsgBox checkCellValue(myWkBk, 1, 1)
   
    '' change cell value
    addCellValue myWkBk, "NEW TEXT", 1, 1
   
    '' save changes
    myWkBk.SaveAs "C:\my documents\test.xls"
    myWkBk.Close
    Set myWkBk = Nothing
   
    '' close xl
    myXL.Quit
    Set myXL = Nothing
   
End Sub

'' function to open up a work book
Private Function openExcel(myXL As Excel.Application, strPath As String, docExists As Boolean) As Excel.Workbook

    If docExists Then
        Set openExcel = myXL.Workbooks.Open(strPath)
    Else
        Set openExcel = myXL.Workbooks.Add
    End If
       
End Function

Private Function createWorkSheet(myWkBk As Excel.Workbook, strName As String)
   
    Dim myWkSht As Excel.Worksheet
   
    Set myWkSht = myWkBk.Worksheets.Add
    myWkSht.Name = "strName"

End Function

Private Function checkCellValue(myWkBk As Excel.Workbook, cellRow As Integer, cellCol As Integer) As String
   
    checkCellValue = myWkBk.Worksheets("sheet1").Cells(cellRow, cellCol)

End Function

Private Sub addCellValue(myWkBk As Excel.Workbook, strTextToAdd As String, cellRow As Integer, cellCol As Integer)

    Dim myRange As Excel.Range
   
    Set myRange = myWkBk.Worksheets("sheet1").Cells(cellRow, cellCol)
   
    '' you can do formatting here
    myRange.Font.Bold = True
    myRange.Font.Italic = True
   
    myRange = strTextToAdd

End Sub
0
 

Author Comment

by:macob
ID: 6300445
Thanks a lot, could you tell me how to resize a col?
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6300884
Using autofit property of column object?
0
 

Author Comment

by:macob
ID: 6301375
caould you give me the code? please...
0
 
LVL 18

Expert Comment

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

myRange.autofit

good luck
0
 

Author Comment

by:macob
ID: 6303436
I get an error message "Autofit Method or class failed"
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now