Lee Richardson
asked on
Building a MSA database to read an input file, performs a calculation, creates a report & outputs a prn file from selected report data
Our company wants to use a VBA module in Microsoft Access 2016 to do the following:
1) Read in an excel file with data that will be used to perform a calculation
2) Create a report that checks the calculated results for outliers that fall outside of a lower & upper set of boundaries for quality purposes (we would like to build this in as a modifiable setting that can be changed by the user but are willing to make these settings predefined within the VBA code)
3) Output a .prn file that contains the results of the calculation
My idea is to do the following:
1) Create a form within a database with a simple interface for loading the input file (and creating objects/input fields for setting the lower/upper boundaries for the report)
2) Create a report template to meet the specifications needed and to handle populating the additional fields that will be computed
3) Use the values calculated in the report to create a prn output file that will have the required data populated once the report is ran
It would help me a lot to get a general idea of the steps that I should use to complete this task.
I've attached an excel file that contains these sheets:
1) Sample input sheet (Input_File)
2) Sample MSA report (Report)
3) Sample output file (Output_File)
I will award full points to someone who provides useful insight on my question. Thanks in advance!
1) Read in an excel file with data that will be used to perform a calculation
2) Create a report that checks the calculated results for outliers that fall outside of a lower & upper set of boundaries for quality purposes (we would like to build this in as a modifiable setting that can be changed by the user but are willing to make these settings predefined within the VBA code)
3) Output a .prn file that contains the results of the calculation
My idea is to do the following:
1) Create a form within a database with a simple interface for loading the input file (and creating objects/input fields for setting the lower/upper boundaries for the report)
2) Create a report template to meet the specifications needed and to handle populating the additional fields that will be computed
3) Use the values calculated in the report to create a prn output file that will have the required data populated once the report is ran
It would help me a lot to get a general idea of the steps that I should use to complete this task.
I've attached an excel file that contains these sheets:
1) Sample input sheet (Input_File)
2) Sample MSA report (Report)
3) Sample output file (Output_File)
I will award full points to someone who provides useful insight on my question. Thanks in advance!
ASKER
Pat:
It has to be a .prn file because the prn is being read into a VB6 application.
Here's my attached file.
Internal_Index_Calculation.xlsx
It has to be a .prn file because the prn is being read into a VB6 application.
Here's my attached file.
Internal_Index_Calculation.xlsx
You will need to provide the format of the prn since prn is not a standard file type.
I created the calculation query for you. I had to import the spreadsheet because it wasn't clean. If you remove the extra header row and get the columns defined as the correct data type, you can link.
Simply run the query.
Calc.accdb
Simply run the query.
Calc.accdb
ASKER
Excel has taken liberties with "prn". Prn is a printer file. The file that Excel outputs is a fixed width text file. Access can export this file as .txt and then rename it to .prn but I would still need to know the format. i.e. Column order and widths.
I also see that you started a new question. Please do not start multiple questions on the same topic. Close either this thread or the new one.
I also see that you started a new question. Please do not start multiple questions on the same topic. Close either this thread or the new one.
Here are four definitions of prn and as you can see, none is a standard text file. This is the industry definition of prn. If Microsoft decides to co-opt the extension for a different use, that's fine but it is not standard. And I see that you have started yet a third thread. I won't pollute that thread but I see that so far two other experts have told you that the format is not standard.
http://www.frogmorecs.com/arts/what-is-a-prn-file/
http://www.pcmag.com/article2/0,2817,1945478,00.asp
http://www.openthefile.net/extension/prn
http://filext.com/file-extension/PRN
All you need to do is to define the columns and widths and any competent programmer or even a novice can manage to export a file in the desired format but they may have to export it as .txt and then include code to rename it to .prn. Access is very finicky about file extensions for reading and writing external files.
http://www.frogmorecs.com/arts/what-is-a-prn-file/
http://www.pcmag.com/article2/0,2817,1945478,00.asp
http://www.openthefile.net/extension/prn
http://filext.com/file-extension/PRN
All you need to do is to define the columns and widths and any competent programmer or even a novice can manage to export a file in the desired format but they may have to export it as .txt and then include code to rename it to .prn. Access is very finicky about file extensions for reading and writing external files.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You didn't attach the spreadsheet.
The whole process shouldn't take more than an hour or two for anyone with a little bit of Access experience. Very little VBA will be needed. The biggest hurdle could be the Excel file if it is not consistently formatted.
If the Excel file is clean and in table format, you can start by opening Access and linking to the file. There is no need to import the data unless you need to store it for future use.
The calculations can probably all be done in a query or in the report itself so create a query.
Then use the Wizard to create a report based on the query.
The code required is only to open the report or to export it to a PDF or both. Generally you will want to specify where the PDF gets saved and possibly the name of the file. Here's a procedure that is probably far more complicated than you need. It even creates the export folder if necessary. It loops though a recordset and creates a separate PDF for each record.
Open in new window
This is an example of linking to a spreadsheet in code with the paths hard-coded which I don't recommend.DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12Xm