[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

How to create and send values to an excel document

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
macob
Asked:
macob
  • 12
  • 6
  • 4
  • +4
1 Solution
 
bobbit31Commented:
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
 
rspahitzCommented:
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
 
Alon_hCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
bobbit31Commented:
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
 
macobAuthor Commented:
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
 
rspahitzCommented:
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
 
macobAuthor Commented:
Do I need to have Excel installed in the PC that runs the program with Excel VBA or just some dll?
0
 
rspahitzCommented:
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
 
macobAuthor Commented:
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
 
Richie_SimonettiIT OperationsCommented:
doesn't end users needs to see that excel file?
0
 
macobAuthor Commented:
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
 
Richie_SimonettiIT OperationsCommented:
Then, you can use bobbit code.
0
 
macobAuthor Commented:
Check my previous post, please
0
 
rspahitzCommented:
I agree--original bobbit31 post.
0
 
macobAuthor Commented:
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
 
rspahitzCommented:
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
 
macobAuthor Commented:
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
 
rspahitzCommented:
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
 
macobAuthor Commented:
Yes my friend
0
 
TimCotteeCommented:
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
 
pierrecampeCommented:
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
 
TimCotteeCommented:
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
 
Richie_SimonettiIT OperationsCommented:
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
 
pierrecampeCommented:
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
 
macobAuthor Commented:
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
 
bobbit31Commented:
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
 
macobAuthor Commented:
Thanks a lot, could you tell me how to resize a col?
0
 
Richie_SimonettiIT OperationsCommented:
Using autofit property of column object?
0
 
macobAuthor Commented:
caould you give me the code? please...
0
 
bobbit31Commented:
after you add the text (ie. myRange = strTextToAdd
 from the above code) do:

myRange.autofit

good luck
0
 
macobAuthor Commented:
I get an error message "Autofit Method or class failed"
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 6
  • 4
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now