Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Writing Data in Excel format

Posted on 1999-07-22
6
Medium Priority
?
209 Views
Last Modified: 2010-04-30
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?

Thanks
0
Comment
Question by:mikemonnex
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 5

Accepted Solution

by:
mayhew earned 400 total points
ID: 1526659
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.
app.Application.Sheets("Daily").Select

'Select a cell.
app.Application.Range("C11").Select

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

'Save the workbook.
app.Application.Workbooks(1).Save

'Or save as a different workbook.
app.Application.Workbooks(1).SaveAs

'Close the workbook.
app.Application.Workbooks(1).Close

'Close the app.
app.Application.Quit
Set app = Nothing




0
 

Expert Comment

by:vbsmith
ID: 1526660
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:  

http://www.smithvoice.com/vb5expt.htm 

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

-Smith
0
 
LVL 5

Expert Comment

by:mayhew
ID: 1526661
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.
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.

 

Expert Comment

by:vbsmith
ID: 1526662
<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?
 

0
 
LVL 5

Expert Comment

by:mayhew
ID: 1526663
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!  :)
0
 

Expert Comment

by:vbsmith
ID: 1526664
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.addnew
rs![columnname] = "Hello"
rs.update
rs.close
set rs = nothing
db.close
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.
-Smith
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

688 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