Excel Example Programs

We prefer to send data to Excel to let users analyze the data in Excel.  This data can be quality or process data.  Excel is something that most people know how to use and is powerful for most data analysis and charting that people want to do.  It allows the user to take process data and "massage" it to their own satisfaction.  

However, we don't like to run Excel in real-time.  In other words we don't like to run Excel on our process computer.  We like to minimize the risks on process computers.  Running Excel, or any other programs, on our process computer uses more resources and raises the probability of failure.  Since most computers are networked we would prefer to write Excel compatible files to a network drive and have the user open the file to read it.  

Note that we don't typically read data from Excel.  We prefer to read data from Access or SQL Server databases.  Small local data like recipes and configuration data (we don't like the registry or INI files either) we read from Access.  Databases are more structured for data but are not good for analysis and charting.  

Presented here are four ways to read and write data to Excel.  

Example #7

This example writes data to Excel using DDE.  We don't like using DDE (it is too inefficient and unstructured) but we give you an example so that you can compare this method to the others and decide for yourself.  Note that there are more (and probably better) examples of using DDE in both VB for automation books.  Most VB websites have examples of using DDE with VB.  

1. Click here to download the Visual Basic 6 source code for example #7.  
2. Click here to download the VB.NET source code for this example. 
3. Click here to download the C# source code for this example. 

Example #8

This example creates text files with the CSV file type.  Comma Separated Variable (CSV) files will load directly into Excel but only require about 1/10 the file space of a XLS file.  CSV files can also be "zipped" for even more compression.  This is typical of the log files that we create.  In this example we start a new log file every time the program starts.  However, you can alter the code so that only one log file is created per batch, day, shift, week, or whatever time period you desire.  In this case the log file name is the format MMDDYYHHMMSS for month, day, year, hour, minute, second.  We write six fields to the log file: date, time, number 1, number 2, number 3, and number 4.  The drawback to using CSV files is that the format is boring (not "pretty") when loaded into Excel.  

This example uses the Scripting File System Object.  This is the new way that you are suppose to read and write text files.  Text files are also used for interfacing to MES, ERP, and mainframe systems.  

1. Click here to download the Visual Basic 6 source code for example #8.  
2. Click here to download the VB.NET source code for this example.  
3. Click here to download the C# source code for this example. 

Example #9

This example uses the Excel object to write data.  If the user will not accept boring formats (like the CSV files in the previous examples) then we use this approach.  This is how we make "pretty" user reports.  We don't like crystal reports since you have to work with crystal reports and load the data in a database.  Although Word makes very pretty reports, we don't like Word since the data is not in a spreadsheet format that someone can easily copy the data into another spreadsheet and perform whatever analysis they want.  

An excellent resource for writing Visual Basic programs for Microsoft Office is OFF2000: Microsoft Office 2000 Automation Help File Available (Q260410)   We recommend that you download the Auto2000.EXE file, extract the files and go through the Auto2000.CHM compiled help file.  

This is a simple example that creates four random variables every second.  When you click on the "start logging" command button, the code creates a new Excel spreadsheet and starts logging the data every second.  When you click on the "stop logging" command button a file name is created with the current data and time and the Excel spreadsheet is saved and closed.  Note that there are many more fancy things you can do to format each cell in different fonts. 

1. Click here to download the Visual Basic 6 source code for Example #9.  
2. Click here to download the VB.NET source code for this example. 
3. Click here to download the C# source code for this example. 

Example #10

This example uses ADO.  We wanted to save ADO for the next section when we show you how easy it is to read any data source (in this case Excel) using ADO.  The advantage of ADO is that you don't care what format the data is in.  In this example we use ADO to read an Excel file so we included this example on this web page as another way to read & write Excel.  We suggest that you skip this example now and come back to it after reading the next section on ADO.  

Note that during our testing we found a lot of caveats about using ADO to write to Excel.  After a lot of searching for additional information we did create some examples where we could write to small Excel files (3 rows by 3 columns).  However, on the larger files (1000 rows by 12 columns), we consistently get "General Protection Faults" when we load the spreadsheet with new data into Excel.  Our test system uses VB version 6, service pack 5, Excel 2000 service pack 2, MDAC 2.5 service pack 1, and we tried it on both Windows 2000 and Windows 98.  

Reading from Excel using ADO seems to be easy and straight forward.  Writing to Excel using ADO seems rather buggy and we currently do not recommend it.  We would appreciate feedback from other programmers regarding their experience with these problems.  

1. Click here to download the source code for example #10.  
2. Click here to download the VB.NET source code for this example.  
3. Click here to download the C# source code for this example. 

We try to offer a fair and balanced opinion on every page of our website.  We would appreciate more information from other users to express their opinions which we will then incorporate.  If you have questions or comments please post them on our message board (see button in left hand column) so that others can read and benefit.