Writing Data in Excel format

Hi, I have some data that I'd like to write in an Excel spreadsheet directly from my VB program.

Can somebody give me some sample code to add the text in a cell for example?

Who is Participating?
mayhewConnect With a Mentor Commented:
Dim app As Excel.Application
'Create instance of Excel object.
Set app = New Excel.Application
'Make app visible so you can see what happens.
app.Application.Visible = True

'Open an existing workbook.
app.Workbooks.Open app.Path & "\" & "MyWorkbook.xls"

'Select a sheet.

'Select a cell.

'Enter some data.
app.Application.ActiveCell.Value = "Whatever you want."

'Save the workbook.

'Or save as a different workbook.

'Close the workbook.

'Close the app.
Set app = Nothing

Wow, that's a lot of code AND it requires Excel automation too! (yes, you would need the Excel objects and you can not distribute them so your users had better OWN Excel)

However ...

I don't know if I would have called that the final answer, because actually you can export direct to Excel (or Lotus or xBase or dBase or Text or HTML) and you can convert any of these formats to any other format completely with a single line of DAO or ADO2.1sp2 code.  It will chop a hundreds lines form the accepted answer and bo so much faster you'll be amazed.

MS says you can't but, think about it .. how do they do TransferDatabase?

If you want to see a single line that will do you a lot of good, look at:  


Works with DAO/ADO all  versions of Jet, VB, VC++, Access et all.

Hey that's not a bad tip.  Thanks!  :)

In this question, though, mike asked how to insert data into a specific cell.

How does your tip handle something like that, or other Excel specific functionality?  Not everyone is looking to do a straight data dump.

And as to your comment about the users needing to own Excel in order to use this solution, I wonder why someone would want their output in Excel if they didn't own it.  Is this really an issue?

BTW, you might give a thought to updating your presentation a little.  The "angry, activist programmer out to protect the world from the evil empire" doesn't play well anymore.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

<g> (up front).

Couple of things, real meekly presented...

There are lots of times when a format is required even though that particular format's app may not be on every user's machine.  I've hit it myself with Excel and asked my users why they wanted that format ... they just did.  I had a similar Lotus requirement a while back at a comapny where Excel was on every machine.  Since most good apps do conversions so too should our apps. Have you never been told to export to "dBase", xBase is the common format for data passing but how many users have an Inprise dbms on their computer?

Against the evil empire I don't understand.  Usually I hear evil empire as a reference to Microsoft.  If this was your implication it's not totally applicable.  I live 5 minutes from the Redmond campus, use and love the tools and could be called a pretty fair fan.  But if you mean evil empire as in me against the whole world .. well, I'll have to give that some thought.  I don't think that such an attitude, if not taken too far is a bad thing.  It gets attention.  But I'll try to keep it reigned in and thank you for the advise.

As to exporting to ranges?  Play with the code a little before you give up.  Sorry, have to say it this way: Microsoft has known about this code for a good number of years but every documentation they put out says that it does not exist at all.  Since it does (and it does more than dumps if you'll take a look at it) then logic says that it might be adjusted for even more potential ... like, when used with clauses other than "SELECT"?

I know, I'm sounding loud again.  Forgive me.  Honest, no offense.  I just like being excited by my work, dont you?

Definately!  :)

If I didn't enjoy it, I wouldn't do it.

I'm originally from Seattle myself.  When I was going to school at the UW (studying Comp Sci), all of my friends wanted to work for Bill.  You live in a nice area.

I appreciate your comments and I'm going to play with what you suggested.

Also, if you're open to a tip, you might get a larger audience if you post in open questions rather than tacking comments on to a "Previously Asked Question".

Best wishes and thanks for the chat!  :)
hey, I saw it and thought it would be a nice thing for the guy to know as an alternative ...

I simply forgot to mention that the far easiest way is just to use Excel as an ISAM.  (I always just figure that being able to crerate a new file is the biggest worry for folks).

If Mike checks this, and if you have the excel file already then you can write to it's worksheets as if they were any other table:

Dim db as database
Dim rs as recordset
set db = workspaces(0).opendatabase([pathtofile], ";Excel 8.0)

set rs = db.openrecordset([worksheetname], dbopendynaset)

rs![columnname] = "Hello"
set rs = nothing
set db = nothing

This assumes you want to deal with the worksheet as if it is a table, thinking in field|value terms instead of coordinates.  But it does work and a lot of the standard routines are available.

Same deal for ADOx, code shown in the Office2000 Programmer's Guide.

Mayhew:  thanks for accepting my apology.  If you hit that smithvoice/vbfun.htm sitelett drop me a direct email.  I can be more "casual" and would welcome your comments.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.