PDA

View Full Version : EXCEL-macro to auto convert bris files


JJMartin
12-20-2012, 05:22 PM
Currently testing some VBA code that opens a drf file, converts it to columns, pastes to main workbook and closes the drf file. If anyone interested, I will post it and provide more details.

rosenowsr
12-20-2012, 07:53 PM
Sounds interesting to me.

raybo
12-21-2012, 03:59 AM
Currently testing some VBA code that opens a drf file, converts it to columns, pastes to main workbook and closes the drf file. If anyone interested, I will post it and provide more details.

How many columns are you talking, in Excel? Assume you're using Excel 2007 or 2010. Pre2007 versions only have 256 columns.

2007 and 2010 should be able to open the file themselves, having 16,000 columns.

For pre2007 versions, I played around with some VBA that opens the 1435 field Bris single file, on to 6 rows per horse, using standard Windows Explorer type file selections, just click a button, the window pops up to the folder you have your files in, select the file and it opens in the active workbook.

JJMartin
12-21-2012, 10:42 AM
Raybo I use Excel 2010, so the entire 1435 columns are displayed and converted. I am currently working on a 2nd stage to allow the user to choose which columns to keep and delete unwanted columns. By the way I can appreciate your extensive work with Excel, I have hundreds of files that have mutated and evolved through the years myself.

JJMartin
12-21-2012, 07:38 PM
Excel file in this post:
http://www.paceadvantage.com/forum/showthread.php?t=99911

raybo
12-25-2012, 10:33 PM
JJ,

Here's something I've been messing around with, off and on, for some time. It's still a "work in progress", but thought you might find some code you can utilize in your worksheet.

This one uses Windows Explorer for file selection and is geared towards pre-2007 versions of Excel. It puts all the Bris .drf or .mcp or JCapper/HDW card data on 6 rows in Excel, for each horse on the card (actually I think it will open any file extension including the old TSN single files). You can change the number of blank rows between horse data in the macro:"Insert9RowsEvery6Rows". The data is curently set up for 15 rows of data for each horse (6 data rows and 9 blank rows), because my basic PPs views are setup that way, and this makes referencing and coping/pasting much easier, as Excel increments rows as you paste downward.

Worksheet "List Files" is the beginning of a batch processing method, but it's far from complete. I haven't begun the listing of results files yet, maybe someday.

The worksheet "Card Data" is where the "Import Card" macro button is located, and that is the macro that actually opens Windows Explorer for file selection. Set your folder path by opening the macro "Main" code window, then below that code in Function: "GetDRFFile", the 3rd line of code "SetCurDir" is where the folder path is, just change it to whatever folder your files are in. Once the file is selected in the Windows Explorer window, that card file will open in the "Card Data" worksheet.

The data mapping for the Bris files are on the "Race Data" worksheet.

raybo
12-25-2012, 10:45 PM
Raybo I use Excel 2010, so the entire 1435 columns are displayed and converted. I am currently working on a 2nd stage to allow the user to choose which columns to keep and delete unwanted columns. By the way I can appreciate your extensive work with Excel, I have hundreds of files that have mutated and evolved through the years myself.

Yeah, my computer and my external drive are full of Excel workbooks, data files, notes, code, VBA articles, etc., maybe 200-300 workbooks alone? I've got stuff from the early 90s still saved somewhere. Been a long, long ride!

JJMartin
12-25-2012, 11:07 PM
Thanks, I'll take a look. I'm working on an update to the file with some more features, should be ready soon.

raybo
12-25-2012, 11:16 PM
Thanks, I'll take a look. I'm working on an update to the file with some more features, should be ready soon.

Anytime!