Excel with VB Script change into VB.NET app

[burpootus]

Please excuse me for being such a newbie and for this being so long. I've taken a MS approved intro course to VB.NET but I still don't know exactly what is possible. At work we have an Excel sheet that uses a lot of VB script and a report template. I want to try to turn this into a VB.NET app as my first "project". Background: We have a machine that measures sound in a production environment. This machine needs to be calibrated periodically. To do this we take a calibrated instrument and measure those same sounds. We do this in 5 locations. This generates 5 files in ASCII format. Each file contains some header information and then 1024 measurements. Each measurement is a different frequency and there is an associated db level. What Excel does is this: Queries the user with an open file dialog to select the 5 measurement files. Uses a couple of values in the header of the first file and puts 5 x 1024 measurements in a scratch sheet. It then does some averaging on those values, goes out on the network to the machine in the location just measured and pulls in the last calibration, and displays a line graph comparing the new measurements with the old calibration. If this information is satisfactory, you click a button to "use" the new measurements as a calibration and a data file is written over the network to the machine as it's new cal file and the report template fires up to generate a 2 page statistical report and graph of the new data.

The part I'm lost at is the Excel scratch sheets. How do I open up these 5 files and manipulate them in VB.NET in a stand alone .exe (with no Access DB or SQL Server dependency)?

Thanks for bearing with me through this.

 

[newguy]

Google "oledb excel" and you find plenty of stuff. And no you don't need access or SQL, but you will learn some SQL like statements - enjoy.

PS I would send you some links but I haven't figured out how to copy url on my iTouch

 

[burpootus]

Thanks, but what I want to do is completely replace the Excel above with a stand alone executable that doesn't have dependencies on other apps. Whereas the Excel version uses scratch sheets to store and manipulate the 5 arrays of data, I don't know how to accomplish the data manipulation in VB.NET. The reading I've done on streams hasn't helped.

 

[burpootus]

bump

Is what I am proposing to do within the scope of VB.NET? Or am I totally off base?

 

[burpootus]

bump

Anybody?

 

[Tom]

I dont know what you mean by "scratch sheets". A VB app will allow you to control the data that you want to save to your hard drive but you eventually have to choose what format you want to use to save that data; whether thats some type of file or something with stronger processing capabilities such as a database.

As suggested in post #2 you can use OleDb funcitonality in your VB app to read excel formatted files; this is not dependent on actually needing Excel, Access or anything other then the excel file to read/write.

Again in the end your data must be saved to some type of file or database.

 

[burpootus]

Thanks for your reply. The "scratch sheet" are the way the guy did it in excel. The 5 files are opened at once and the scratch sheet is populated with their data. At the end, these sheets are purged. The data is not saved. I mention the way this is currently being done only because I understand it.

I don't want to include excel in my VB app. I don't want to save the data. I have it in the original files. I want to manipulate and do the math on the data in those 5 files at once in memory and produce a new file (.dat) for the calibration. That is where I am lost. It is very possible that I'm totally off base and going about this the wrong way. Thanks for your help.

 

[Tom]

Again the method mentioned in Post #2 is a means of being able to read/write to Excel files without the need of acutually using Excel.

You say you dont want to save data but in the next sentence say you want to produce a dat file... (note that would be saving some type of data). Of course you can save info to whatever type of file you want to use, including a dat file. (note a dat file is still just some sort of regular delimited based text file)

Without knowing to much about the details within your 5 input files or what structure/data you need to output, you may benefit from something like an XML file where a single file can contain multiple tables of information.

 

[burpootus]

I didn't realize that newguy's post concerned using an excel file without actually calling up excel and opening it through the app I'm trying to build. So that might be an option after all.

Sorry for the confusing comments about saving data, but I am simplemindedly looking at this where if I can figure out how to open the files up and manipulate them, writing the dat file and creating a report would be a piece of cake.

Each of the 5 files are in ASCII format. The top 30 or so lines contain general header information such as the name of the instrument used during the measurements, date, time, integration time, etc.. Then there are about 1000 lines that contain two pieces of data, a frequency and corresponding decibel level. These are the lines I am most concerned with manipulating.

Then after this data there is appx. 10 lines with some statistical information on the data.

That's basically the structure.

I don't have any XML experience at all. I am currently trying to learn HTML, CSS, Javascript, and PHP so I don't know if my little brain can handle any more.

Thanks for helping me out.

 

[Tom]

Each of the 5 files are in ASCII format. The top 30 or so lines contain general header information such as the name of the instrument used during the measurements, date, time, integration time, etc.. Then there are about 1000 lines that contain two pieces of data, a frequency and corresponding decibel level. These are the lines I am most concerned with manipulating.

Then after this data there is appx. 10 lines with some statistical information on the data.

If all the data (each line in your files) are not formatted exactly the same as the other lines in the file then NO the previous methods mentioned would not work. Now it sounds more like you do have to read everything on a line by line basis and split it up accoridingly depending on what type of line data there is...

You would need to provide more details about the files and line data for me to elabarate further.

 

[burpootus]

Here's a much shortened version, there is more header data up top and more statistics at the end, but this gives you the format:


<File Title>
Numer1.xxx
<Date Time>
2/3/2009 10:12:14 AM
<Type>
Sound
<Designation>
02-03_0990740.3hrs
<Calibration File>
C:\Program Files\xxx\20071218.cal
<Unit Time>
Unit Time Used
<Number Of Scans Averaged>
20
<Data>
374.12, 98.25
374.83, 98.23
375.55, 98.28
376.27, 98.30
<EndData>
Peak Wavelength = 400hz
Max Intensity= 99.75