These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Wednesday, April 14, 2010

Text file manipulation with VBA

http://files.gove.net/shares/files/10w/bnd4/PMH_done_claims_Jan_2010_example.txt
http://files.gove.net/shares/files/10w/bnd4/baselinefortextmanipulation.xlsm
This program is designed to take a text file and upload it into excel in a way that will allow it to act more like a database. The main sheet of the excel file contains a button called "Select Data File" that goes and finds the filename and file path of the text file you want brought in. The used is then able to choose the fields they want brought in by clicking on the field names on the left of the sheet. A checked box indicates a field that will be brought in.

The button "Build workbook" then goes in and builds the workbook from the original text file bringing in only the fields that are specified in a list format that can be sorted and screened easily.

The button "Build Query" launches a user form that would allow the user to build queries that would then populate another sheet in the new workbook created. Some of the features of this user form include listboxes that populate with unique values from the data in ascending order when the used choses a field they want to screen by.

Although the code for the resulting query has been started inside the VB editor, the author decided (with the advice of the professor) that everything that might be queried could be done more easily and with more flexibility with pivot tables instead. That said, the query side of this project was discarded, though the user form was left because it contained several useful and complex features.

Also included in the code is a smaller program that fixes the formatting of one of the cells to be the correct size and positioning the decimal place in the right place.

No comments:

Post a Comment

Blog Archive