RSView32 dbf to an excel report

Marie1982

Member
Join Date
Feb 2007
Location
House
Posts
121
Hi,

I would like to create a vba code to format the dbf files that RSView32 creates to make readable reports. For example, if I datalog a tag, I want Excel to get all the values in the dbf file, calculate the mean value and put it in a report.

I don't know much about vba programming, all I did in the past was to modify an existing vba code.

Any help would be appreciated.

Thanks,

Marie
 
If a datalog file is saving in wide format each tag is in its own column, and can be opened and manipulated in Excel.
If datalog .dbf file is in narrow format it can also be opened in Excel, then by setting up a pivitol table, you can seperate each tag into its own seperate column, which can then be manipulated with Excel.
I am usure of how you get Vba to work with a datalog .dbf file, as I have never tried.
 
You need tu use the rigth connection string. There are websites regarding all the connection strings for different database files.
Method for vb to read a dbf file.
With this method the software steps through the dbase file, line for line.
Assume the dbase file name is: abc.dbf and located in c:\temp
Assume the data is stored in two columns and are integers.
Use MS ADODB components V2.6 or higher.

Dim cDBConn AsNew ADODB.Connection
Dim rDBRcrd AsNew ADODB.Recordset
Dim sDBPath As String
Dim sConnStr As String
Dim i(2) As Integer

sDBPath = "c:\temp\abc.dbf"
sConnStr = "Driver={Microsoft dBASE Driver(*.dbf)};DriverID=277;Dbq=" & sDBPath
cDBConn.Open(sConnStr)

rDBRcrd.Open(
"ABC", cDBConn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic, ADODB.CommandTypeEnum.adCmdTable)

DoUntil rDBRcrd.EOF
i(0) = rDBRcrd.Fields(0).Value

i(1) = rDBRcrd.Fields(1).Value
rDBRcrd.MoveNext()
Loop
rDBRcrd.Close()
cDBConn.Close()

Good luck,

 
Last edited:
Marie,

It seems to me that what you really need to do is to write some Excel Macro Commands to manipulate the data (that has been previously stored in Excel-readable CSV files by RSView). See the Excel Help for Macros. Macro Commands are easy to write because if you can do it manually, then you can write a Macro to do it automatically.

It works like this: You start the Excel Macro Recorder by clicking "Tools, Macro, Record New Macro". Give your new macro command a name and a shortcut key. Now every key and instruction that you press will be recorded in a macro file as a Visbual Basic executable command. Continue until the data is massaged into the shape you want, then hit the "Stop Recording" button. Now when you run the macro, your commands will be automatically executed. Once the Macro is written, you may view and edit the VB code manually. You can string several Macros together, and even set up an Auto-Run macro which will automatically run one or more Macros when the spreadsheet is opened.
 
Last edited:
Marie
Lancie is right.
Did many csv (Fluke data logger output file) to Excel conversions with around 5,000 data points.

So what you do is have RS View save data as csv file.
Open Excel and retrieve csv file.
convert it and preferable save to different folder - that way when you screw up the Excel file you still have your raw data in the original file.
Have not done many Excel macros but the few times I tried it I was impressed. The main trick with macros is to figure out what you want the output to be.
Like any learning process it will take some time and you will make mistakes. That is why you NEVER change the raw data.

Dan Bentler
 
Thank you for all your replies.

I thought of the idea of a macro, but can a macro get information from an Excel file to another?

Because RSView32 creates 2 files: one which contains only the tagnames and one with the data. I would like to merge the two so the user doesn't have to have two files opened to consult his data.

Marie
 
That is what I did. Everytime I add another group of tags I just add to the existing macro. I also wnet so far to insert the tag names at the top of the Excel file.

Good Luck
 
Marie1982 said:
Thank you for all your replies.


Because RSView32 creates 2 files: one which contains only the tagnames and one with the data. I would like to merge the two so the user doesn't have to have two files opened to consult his data.

Marie

When using RSView Datalog narrow format, There are, tag name files and the tag float data files created, you can put these through a pivitol table in excel to seperate each tag into its own column.
( Pivitol tables are mentioned in your RSView user manual ).

I think this one file, is what you are trying to create, so you are then able to do the next step you mentioned in your first post.
 
You will have to insert a row at the top of your Excell file, then insert the tag name of the tag. That was the easiest way I found to have the tag name in the file. Using a macro is the easiest way.
 
Gil47: I didn't find any info on Pivitol tables in the RSView32 User Manual that I downloaded from www.ab.com Can u post the manual that u have?


Jimbeaux: I found how to format my file with a macro, but I still can't copy the tagnames from one file to the other using a macro. Can you give me more details about that?

Thanks

Marie
 
Last edited:
Marie1982 said:
Gil47: I didn't find any info on Pivitol tables in the RSView32 User Manual that I downloaded from www.ab.com Can u post the manual that u have?
That's a typo. Look for Pivot tables. The Excel help files will have more info.

My favourite method for creating reports from RSView's datalog files is to use Crystal Reports.
 
Marie,

Yes, you can combine two or more CSV files using Excel. I use an Excel spreadsheet to keep up with stocks, and when I open the spreadsheet it runs a Macro to import stock data from several sources.

In Excel, go to the Excel Help menu, then search for "Import Data", or go to this link for Microsoft Excel Online Help with the Data Connection Wizzard:

http://office.microsoft.com/training/training.aspx?AssetID=RC011831161033

Set up a spreadsheet that has a defined area of rows and columns for the first set of data, and another area for the second data, then write a Macro to import data to each area. Now use the remainder of the sheet to manipulate the data so that it tells what you want to show.

You could even import the two sets of data into separate Worksheets in the same Excel Project, then use a third Worksheet to automatically manipulate and massage the data from the first two sheets.
 
Last edited:
Marie,

I should have mentioned the easy way to create automatic links between spreadsheets, or a way to have a cell in one spreadsheet go get data from a completely different spreadsheet or database.

1. Open the first sheet, (call it "Data To")
2. Open the second sheet (call it "Data From")
3. In the Data To sheet, place your mouse pointer on the upper left corner of the area whre you want the data link.
4. Type in "=", then move to the first upper left cell in Data From, click on that cell, then hit the Enter key.

Now you have a cell that has a formula that automatically links Data To to Data From and the cell in Data To will change when Data From changes. To create a group of links, first check the formula to be sure that the Row and Column references are "relative" (not preceded by a $ sign). If there are $ signs, remove them, then simply copy the formula in Data From to as many cells as you need to get all the relevant data.

You can use this quick easy link method to link as many different spreadsheets as you need.
 
Last edited:
Marie1982 said:
Gil47: I didn't find any info on Pivitol tables in the RSView32 User Manual that I downloaded from www.ab.com Can u post the manual that u have?


Jimbeaux: I found how to format my file with a macro, but I still can't copy the tagnames from one file to the other using a macro. Can you give me more details about that?

Thanks

Marie

With your version of RSView 32 in the help files there is a User guide in the online books.
Go to chapter 16 called "Sharing data with other Windows applications" in the last half of that chapter are several pages about linking tables from different files and also pivot tables in fact the whole chapter may be of interest to you.

If you open Excel on the menu bar under Data, Pivot File are on of the drop down list.

Jimbeaux: Gerry: and Lancie: There info also appears to be useful for your cause
 
Last edited:

Similar Topics

Hi, I have same system connected to 2 PC's running RSView32 SE (old)and another with Factorytalk SE (new). I next few months old one will be...
Replies
2
Views
1,478
Greetings all,I have an existing RSVIEW32 project that is running on a computer which is connected to our "production network". The production...
Replies
7
Views
3,327
I have to produce charts for a month's worth of these dbf files. There is one file for each 6 hour cycle. Anyone have any ideas? I could...
Replies
4
Views
1,914
I'm importing an RSView32 project into FTView SE. I'm using Legacy Tag Database Conversion on a virtual machine with Windows XP, I did the first...
Replies
0
Views
349
Hi everybody, I have a rsview32 application, when I try to run it it loads upto 80% system settings and the crashes saying Rsview32 Project...
Replies
3
Views
1,892
Back
Top Bottom