Quick Search for:  in language:    
BIFF,JET,20000,32768,32767,Writes,Excel,sprea
   Code/Articles » |  Newest/Best » |  Community » |  Jobs » |  Other » |  Goto » | 
CategoriesSearch Newest CodeCoding ContestCode of the DayAsk A ProJobsUpload
Visual Basic Stats

 Code: 3,011,557. lines
 Jobs: 117. postings

 How to support the site

 
Sponsored by:

 

You are in:

 
Login



Latest Code Ticker for Visual Basic.
Files Comperator (the right way)
By Jarry Claessen on 6/30


Simple UDP example
By Mick Walton on 6/30


CAPS Trigger
By Trevor Burley on 6/30


Auto clip picture
By Kenneth. Jakobsen on 6/30


Click here to see a screenshot of this code!Game of life clone (cool math)
By Johannes B on 6/30

(Screen Shot)

String to CHR()
By Nikhil Raj on 6/30


Encryption Decryption Demo
By Nikhil Raj on 6/30


Click here to see a screenshot of this code!Serial Registration
By Christian (eXonite Team) on 6/30

(Screen Shot)

AniViewer
By Jerrame Hertz on 6/30


Click here to put this ticker on your site!


Add this ticker to your desktop!


Daily Code Email
To join the 'Code of the Day' Mailing List click here!





Affiliate Sites



 
 
   

Excel Class - write to an XLS file without DLL's or Excel automation

Print
Email
 
winzip icon
Submitted on: 10/4/2000 3:29:37 PM
By: Paul Squires 
Level: Intermediate
User Rating: By 43 Users
Compatibility:VB 4.0 (32-bit), VB 5.0, VB 6.0

Users have accessed this code 33907 times.
 
 
     Writes an Excel BIFF 2.1 spreadsheet file DIRECTLY. No external DLL's or Excel automation needed. Great for exporting data from your application to an Excel spreadsheet without the overhead of JET or ADO. This is a small update that includes the ability to create Horizontal Page Breaks and to specify default formats for numbers and dates (for example, the number 20000 could be given a format of #,### in the file - thanks to Dieter Hauk). Updated June 20/2001: Fixed lost row 32768 if more than 32767 rows are output to the BIFF file. Thanks to Sid Eaton for noticing this problem. Updated November 10/2001: Added support for default row height and the ability to set the row height of individual rows. Example on how to save dates to the file.

 
winzip iconDownload code

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. Afterdownloading it, you will need a program like Winzipto decompress it.

Virus note:All files are scanned once-a-day by Planet Source Code for viruses,but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:
1)Re-scan downloaded files using your personal virus checker before using it.
2)NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.
3)Scan the source code with Minnow's Project Scanner

If you don't have a virus scanner, you can get one at many places on the net including:McAfee.com

 
Terms of Agreement:   
By using this code, you agree to the following terms...   
1) You may use this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.   
2) You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.   
3) You may link to this code from another website, but ONLY if it is not wrapped in a frame. 
4) You will abide by any additional copyright restrictions which the author may have placed in the code or code's description.
 
Report Bad Submission
Use this form to notify us if this entry should be deleted (i.e contains no code, is a virus, etc.).
Reason:
 
Your Vote!

What do you think of this code(in the Intermediate category)?
(The code with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor See Voting Log
 
Other User Comments
10/5/2000 3:01:02 AM:Robert Croll
Thats EXCELent.  Very nice, well 
written code.  If you continue 
developing this module please repost.  
Ran it on NT4 and error 52 occured, Bad 
file name or number, at "Put 
#FileNumber, , END_FILE_MARKER" in the 
Close File Function.  I skipped the 
line and if saved the file no worries.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
10/5/2000 5:05:06 AM:jan van herp
Very fast and usefull. Thank you. 
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
10/5/2000 8:42:25 AM:gjones
Good coding, lots of comments!
Fast 
way of making XLS files.
BTW I have NT 
4 SP5 and had no problems running the 
sample
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
10/5/2000 1:09:21 PM:Chris
Excellent!  You get 5 globes from me. 
:)  Keep up the good work.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
10/5/2000 1:16:50 PM:Mohit Vij
Great information and very nicely 
presented. 5 Star.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
10/5/2000 2:00:49 PM:Tom de Lange - Ariel Systems
Excellent! Well commented. 5 Star work! 
Runs perfectly on Win98SE, Excel97
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
10/5/2000 3:36:10 PM:carl.tice@vacsew.com
deffinately would like to see some 
formulas. Does anyone know how to 
import data into VB from an Excel 
spreedsheet?
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
10/5/2000 4:08:58 PM:eric650
Great code, really useful for 
generating charts from a database app.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
10/5/2000 4:26:02 PM:Euskaldun bat
On Win.95, I got the 52 error, 
the
same as  Robert Croll, anyway 
good
code, my vote "excellent" for 
you.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
10/11/2000 11:01:23 PM:Kunal Johar
Gee, this code seems awful familiar... 
It is available on www.vb-helper.com 
and it has been for about 2 years
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
10/12/2000 4:02:38 PM:Paul Squires
Kunal,
You obviously haven't looked 
at the vb-helper code very closely. If 
you did you would see that this class 
is vastly different. I have spoken to 
Dan Gardner about his class and he was 
extremely pleased with my class because 
it does 99% more than his. For example, 
Dan's class does not do fonts, bolds, 
strikeouts, underlines, borders, 
alignment, locked and hidden cells, 
column widths, and margins. Also, his 
class was limited to only 256 
rows!
Before you accuse me of 
copying code you should have all the 
facts. The BIFF file specification is 
not a secret. I'm sure many people have 
created code for it.
By the way, I 
spoke with Rod Stephens (of vb-helper) 
two weeks ago and he has posted this 
Excel class in his advanced code 
section.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
10/12/2000 6:52:49 PM:Kunal Johar
I solemly apologize for my comment.  
You are 100% right, I did not look at 
the code carefully and I was wrong.  I 
hope you'll accept my apology, next 
time I will not be quick to judge 
without knowing the facts.  Once again 
I am sorry.  Also I took a look at your 
code and it is worth an excellent, even 
more so after the trouble I have 
caused.   Sorry again, Kunal johar
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
10/12/2000 9:02:02 PM:Paul Squires
Kunal,
No problem and no need for 
apologies.
: )
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
11/16/2000 5:26:32 PM:zpadawer@net2phone.com
This code rates A1! I've tried various 
different ways of exporting to excel 
and so far this is the fastest and 
easiest. It also allows me to have 
multiple instances of the program 
running at the same time as it doesn't 
open and close excel. 
thank you 
very much.
PS: Does anyone know how 
to autoformat the column 
widths?
Creating Charts would be great 
as well. Any idea anyone?
thanks!
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
11/20/2000 3:39:04 AM:xiyuan
This code is very good. But I can not 
write some Chinese words into it. Do 
you know how to add Chinese font 
supporting? 
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
11/20/2000 3:40:35 AM:xiyuan
Nice code. But I can not write even one 
Chinese word into the excel file. Do 
you know how to add Chinese font 
surporting in this project? 
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
11/26/2000 1:11:12 PM:antiduh
whoa. perfect. i'm writing a 
comprehensive gui version of netstat 
(that does tons more than give tcp and 
udp connections, such as icmp, tcp, ip, 
udp connection stats, traceroute, 
ping...) and i wanted a way to export 
some of the tables (such as the 
traceroute output) to a xls, and didnt 
want to use the object library (yuck, 
eww eww eww. *gag*) so this is quite 
handy. it also exports to mdb, but 
thats not quite a popular, and i AM 
using the object library for that, but 
that is more popular. perhaps xls to 
mdb w/o object libray usage would be 
another cool project for you *wink* 
*wink*. thanks
-antiduh.
ps, i plan 
on releaseing core components of my 
source code on psc, when im happy with 
it.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
11/30/2000 4:39:29 AM:N Kruse
Bravo! Very well done, I can think of 
many place use this code.  '5' IT IS.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
11/30/2000 9:26:23 AM:Carlos
Nice code, but can I write to an 
existing Office 2000 xls.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
11/30/2000 11:21:12 AM:Brad McDonald
Excellent!!!!
What is the trick to 
do formulas?
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
1/9/2001 2:55:14 PM:Toby
This Rocks! I've been searching 
everywhere for BIFF export code. No 
more CSV files for me!!! Thanks Paul.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
2/19/2001 2:05:11 PM:Markito
Do you know how to write an Excel File 
with more than one sheet?
Tks!
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
2/19/2001 9:02:11 PM:Paul Squires
Unfortunately the BIFF 2.1 
specification does not allow multiple 
worksheets as far as I know.
Sorry.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
3/27/2001 9:46:05 PM:Emil Nadinic
Great code Paul, been looking for 
something like this for a long time. I 
ended up compiling it into an ActiveX 
control for use within an ASP page, and 
it works like a charm.
Great Work, 
again.
Emil.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
4/1/2001 10:25:53 PM:QQQ
excellent! 5 stars for u
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
4/22/2001 10:39:21 PM:Paul Squires
Thanks to everyone who has posted 
comments on this site and who have 
emailed me personally. I never in my 
wildest dreams thought that this code 
would have been so well received by 
everybody. Now if only I could figure 
out formulas...... :)
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
6/22/2001 4:33:41 AM:Jun Santiago
regarding your updates in the missing 
row 32768 you fixed it by skipping to 
the next row and you did'nt write 
anyting on row 32768, is there a way to 
write a value inside row 32768?
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
7/27/2001 9:52:10 PM:Houston McClung III
Wonderful Code! Top of the LINE!  You 
don't know it, but this code 
practically saved my hide.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
8/10/2001 6:54:26 AM:R C Sharma
Nice. How do I use it in my project 
where I want to save data on a grid to 
Excel file??
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
1/14/2002 5:17:48 PM:Rob Gerwing
You wouldn't happen to now where to 
find BIFF 7 or 8, or is the BIFF 2.1 
compatible with all Office 97,2000, and 
XP? Can this utilize charting?
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
2/25/2002 1:03:53 PM:wileecoy
How about reading an Excel file without 
the Excel Object reference?
Anything 
on that?
btw - code is excellent.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
2/25/2002 3:15:46 PM:Paul Squires
wileecoy,
You lost me. The code 
doesn't use any Excel Object reference. 
The whole purpose of the code is to 
avoid that. :-) If you don't have the 
latest code then maybe the code at my 
site is more up to date. 
http://www.planetsquires.com
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
5/13/2002 2:33:33 AM:kookai
Fast solution of Excel for me 
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
5/23/2002 4:39:36 PM:Javier
Hello, 
I Didn't Test the class yet, 
but the idea is great!!
I'll tell you 
when I test it.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
6/25/2002 12:15:24 PM:Steve
If you dim lrow and lcol as variants 
instead of longs you can pass variable 
values to the write value function.  
Makes it easier to work with loops and 
record sets.  Public Function 
WriteValue(ValueType As ValueTypes, 
CellFontUsed As CellFont, Alignment As 
CellAlignment, HiddenLocked As 
CellHiddenLocked, lrow As Variant, lcol 
As Variant, value As Variant, Optional 
CellFormat As Long = 0) As Integer
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
7/2/2002 9:25:21 PM:Ryan
The Code is very useful. But how can i 
export Chinese words to Excel because i 
had tried but failed. Can you help me?
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
7/4/2002 1:08:20 AM:andreas
nice code 
i havent test it yet but 
with so many comment 
i can't wait to 
try it
^_^
oh yeah 
anyone now how 
to read from the excel?
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
8/7/2002 10:58:23 AM:Sergio
Hi, this code is great, but I have a 
problem, when I try to export a long 
text string like 264, 400 or longer, I 
get an overflow error in the textlength 
property, I dont know how to solve 
this, please, if the author can help 
me, that would be great, my email is 
sgil@ceticostacna.com.pe.  Thank you
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
8/24/2002 2:17:56 AM:webJose
Sergio:  I haven't tested it, but for 
what I have read about the BIFF 2.1 
specification for worksheets, you 
cannot write string values of more than 
256 bytes.  That is just the way it is. 
:(  But please, if you prove me wrong, 
I'd appreciate if you let me know.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
10/23/2002 9:33:05 PM:
Fantastic idea, saves on office 
licences thats for sure
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
11/4/2002 9:27:51 AM:
Paul,
Code is very impressive.  I do 
have one question.  I create an excel 
file using this class and when I open 
it with excel I get a memory error. 
(The instruction at 
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
11/6/2002 9:12:08 AM:
Hi, great piece of code, thanks.
I 
discovered that manipulating a row's 
height will hide this row, when opening 
the file in Excel 2000, after saving 
this file in Excel it will be displayed 
again. Maybe you can do something about 
this.
Rob
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
11/12/2002 8:33:37 PM:
Great Code. But I'm having
some 
difficult to make it work on vb .NET
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
11/27/2002 10:41:43 PM:
Because of your code my wife has had a 
better time at work
counting records. 
I wrote a program that counts the 
items
she has to count at the end of 
every month for work.
Of course I 
could have used the ‘CountIf’ function 
in excel
but it was more fun to write 
a stand alone app that exported
to 
excel. Thanks for the code man I got a 
little extra this month if you 
know
what I mean.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
1/6/2003 10:42:43 AM:
Hi, 
great code,  
PS: Does anyone 
know how 
to put color in the cell
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
1/13/2003 6:02:30 AM:The Saint
HI, REALY GREAT CODE!!!
DO YOU HAVE 
ANY IDEA HOW TO SUPPORT GREEK 
CHARACTERS?
THANKS
tsitsirigos_fotis
@yahoo.gr
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
1/24/2003 6:28:46 AM:
Russian support chars solved 
!!
tjn.web@mail.ee
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
1/30/2003 11:58:10 AM:
Hi,
This code is exellent. I have a 
requirement where I have to insert a 
image(jpg) and some statistical data 
into excel. Pls let me know if this is 
possible. 
My email : 
ycmahesh@hotmail.com
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
3/26/2003 4:17:01 AM:
Hi,
This code is very excellent 
!
I'm just lookin how to display a 
number with 3 or more decimal and how 
to use the function 
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
4/8/2003 2:52:05 PM:
Impressive testimonials!! I was 
searching for reading from excel file 
without the need to open excel 
object....Is that possible?
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
4/21/2003 7:24:04 PM:
Could you please let me know a work 
around to write text into the .xls 
having more than 256 characters. 
Appreciate your help in this.
Thank 
you.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
4/22/2003 3:00:13 AM:
I changed some lines to support 
Chinese. Do you need my version?
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
4/22/2003 9:29:27 PM:
That will be great. 
Please do send 
it.
Thank you.
My email 
anilemail17@yahoo.com
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
4/25/2003 12:45:43 PM:
To support Chinese: 
      Case 
ValueTypes.xlsText
         Dim b As 
Byte
         Dim barray() As Byte
         st$ = CStr(value)
     barray = StrConv(st$, 
vbFromUnicode)
        l% = 
UBound(barray) + 1
        Dim 
TEXT_RECORD As tText
        With 
TEXT_RECORD
          .opcode = 4
      .length = 10
          'Length 
of the text portion of the record
     .TextLength = l%
   'Total length of the record
  .length = 8 + l
.Row = Row%
          .col = col%
          .rgbAttr1 = 
CByte(HiddenLocked)
.rgbAttr2 = CByte(CellFontUsed + 
CellFormat)
          .rgbAttr3 = 
CByte(Alignment)
'Put record header
          Put 
#FileNumber, , TEXT_RECORD
        'Then the actual string data
          For a = 0 To 
UBound(barray)
                Put 
#FileNumber, , barray(a)
Next
        End With
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
Add Your Feedback!
Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.

NOTICE: The author of this code has been kind enough to share it with you.  If you have a criticism, please state it politely or it will be deleted.

For feedback not related to this particular code, please click here.
 
Name:
Comment:

 

Categories | Articles and Tutorials | Advanced Search | Recommended Reading | Upload | Newest Code | Code of the Month | Code of the Day | All Time Hall of Fame | Coding Contest | Search for a job | Post a Job | Ask a Pro Discussion Forum | Live Chat | Feedback | Customize | Visual Basic Home | Site Home | Other Sites | About the Site | Feedback | Link to the Site | Awards | Advertising | Privacy

Copyright© 1997 by Exhedra Solutions, Inc. All Rights Reserved.  By using this site you agree to its Terms and Conditions.  Planet Source Code (tm) and the phrase "Dream It. Code It" (tm) are trademarks of Exhedra Solutions, Inc.