Populating a report sheet based on imported values on another sheet

**th3spankst3r** · 04-27-2010, 02:58 PM

I have sort of a unique problem here, and I'm hoping there's a genius out there who can help me. I've written a database in MS Access, and part of the database exports information to Excel. I have split my workload with a coworker because he is a genius when it comes to Excel, so I left the Excel formatting and programming up to him. However, due to his increased workload, he no longer has time to complete the project, and I know nothing about programming Excel without VBA. His goal was to make the workbook without using any VBA or macros so that we didn't need to buy a certificate to sign the project. I'm not too worried about this, since I'd like to finish this project soon. If there's a way to continue without programming, it would be nice though. Anyway, I've uploaded the workbook so that ya'll can try to understand my problem a little better.

If you look at the workbook, the ERO worksheet is an electronic variation of what Marines call an ERO (pronounced arrow) or Equipment Repair Order. It tracks the maintenance of a piece of gear. If you look at the workbook, the Import worksheet is where my database dumps all of the information, somewhat methodically. The ERO is split into 3 basic sections- the header, the descriptions of work, and the close-out section. My database exports all information pertaining to the header (which should never change after the opening of an ERO) to row 2 with a "header" in row 1 for use by the ERO programmer. All of the information pertinent to the CURRENT description of work is exported to row 4 with a "header" in row 3. All the information pertinent to the close-out section is exported to row 6 with a header in row 5.

Currently, the header section of the ERO works perfectly. He was able to complete all of that. The problem starts with the descriptions of work. When handwritten, an ERO entry would look like this:

Please Login or Register  to view this content.

or simply

Please Login or Register  to view this content.

The point is that at the beginning of every entry, the technician writes that day's julian date (generated by my database and exported to the Import worksheet), enclosed in parenthesis, and at the end of the entry, hyphens are added to reach to the defect code, to prevent someone from writing anything unwanted in there, and then at the end of the hyphens, justified to the right of the box, would be the new defect code enclosed in parenthesis.

This presents a couple of problems that are well beyond my intelligence. First of all, every time a new entry is made from my database, it overwrites the previous entry in the Import worksheet. I could probably work that out on the db side, if it cannot be solved here.

The next problem, and the biggest one, is the formatting. I've thought of splitting the "Description of work" boxes into three sections: one for julian date, one for description of work, and one for defect. I think this would be my best bet, however, there's still some issues with that. I would want the defect to be placed at the END of the entry, IE if the entry is a paragraph long, I want the defect to be placed to the right of the last line of that paragraph. This seems easy enough if they all remain one cell, however that also causes issues. I could either increase the size of the cell, which would ruin the form itself, or I could shrink the size of the text to fit the cell, which would cause it to be illegible when it is of great length. This is why I think splitting the contents of the cell into as many as needed would be a better idea. But then how do I make sure the defect is aligned with the last cell for the description?

Finally, a problem I will be facing in the future, is the scenario where an ERO becomes more than one page. In practice, on paper EROs, the way we would do it is that some of the same information would be filled out on a second ERO and the description of work would continue on to the second page and then the close-out section would only be filled out when maintenance is done and the gear is ready to be given back to the owner, and would only be filled out on the first page of the ERO.

The way I forsee fixing this problem is that when the description of work is filled, Excel would copy the worksheet and rename the original one to ERO1 or such, and name the new worksheet ERO, so that all of the formatting would be identical, and then the description of work and close-out sections would lose their formulas. I'm not sure.

I can make simple changes to my db, but due to its complex nature and large size, I'd prefer not to rethink the whole task. If someone can help me out with what I have, I'd GREATLY appreciate it. If you have trouble understanding what it is I'm asking, please ask questions.

**broro183** · 04-27-2010, 05:04 PM

hi,

Originally Posted by th3spankst3r

I have split my workload with a coworker because he is a genius when it comes to Excel, so I left the Excel formatting and programming up to him. However, due to his increased workload, he no longer has time to complete the project, and I know nothing about programming Excel without VBA. His goal was to make the workbook without using any VBA or macros so that we didn't need to buy a certificate to sign the project.
...
The way I forsee fixing this problem is that when the description of work is filled, Excel would copy the worksheet and rename the original one to ERO1 or such, and name the new worksheet ERO, so that all of the formatting would be identical, and then the description of work and close-out sections would lose their formulas. I'm not sure.

I'm not sure either!

When you retitle your thread as RoyUk has requested, can you please make it clear if you still want to do this "without using any vba or macros"?
If that's the case, can you please ask a Moderator (or RoyUK) to move this thread out of the Programming Forum?
Note macros/vba will be needed for "Excel to copy the worksheet and rename the original one...".

A possible thread title (as I understand it) could be along the lines of "how can I populate (& format) a report sheet based on an Imported range on another sheet?" - I'm sure you can come up with a more concise title :-)

Rob

**th3spankst3r** · 04-27-2010, 09:13 PM

Originally Posted by broro183

hi,

I'm not sure either!

When you retitle your thread as RoyUk has requested, can you please make it clear if you still want to do this "without using any vba or macros"?
If that's the case, can you please ask a Moderator (or RoyUK) to move this thread out of the Programming Forum?
Note macros/vba will be needed for "Excel to copy the worksheet and rename the original one...".

A possible thread title (as I understand it) could be along the lines of "how can I populate (& format) a report sheet based on an Imported range on another sheet?" - I'm sure you can come up with a more concise title :-)

Rob

will a moderator move this post to an appropriate forum, please? I'm not sure where it would be placed if solving this problem without programming is possible.

**broro183** · 05-01-2010, 10:21 AM

hi th3spankst3r,

When re-developing a process, it is often a good idea to challenge the validity necessity/design of the existing process. For example:
- does it need to be processed via Excel at all?
- did you know you can Create Reports in Access?
(this may provide a valid alternative)

I don't want to apprear rude, but I have waited for someone else to help you out with this, because, put simply, to a more experienced eye the spreadsheet looks painfully messy ie it is not designed as well as it could be*. My main suggestion for improving the ERO spreadsheet would be to remove the merged cells and to use the Format - Cells - Alignment - Horizontal: "centre across selection" (& tick "Wrap Text") as necessary. A second suggestion is to change your database export so, that when any info is imported into Excel, each header is presented in a separate column & the related data is presented in the rows below.

*The following link(s) may be quite useful for your coworker (& you) to help with some better practices in spreadsheet design: http://www.eusprig.org/smbp.pdf which can be sourced from http://www.sysmod.com/sslinks.htm

Originally Posted by th3spankst3r

I can make simple changes to my db, but due to its complex nature and large size, I'd prefer not to rethink the whole task. If someone can help me out with what I have, I'd GREATLY appreciate it. If you have trouble understanding what it is I'm asking, please ask questions.

I've only started learning Access in the last 6 weeks & am lucky enough to be working alongside some helpful & knowledgeable people (so my learning is being accelerated) but I don't yet recognise all the possible complexities. However, in your case, I think you will get a better & more robust solution if you change the process as close to the "source" as possible. To help me (us) understand more clearly...
- Would Access generated "Reports" work for you?
- does anybody enter data directly into the db?
(could a form be created for entry into the db - perhaps into a temporary table for review)
- do people enter comments directly into the Excel sheet or is it initially manually written on a printed page & then transferred to Excel?
- how is the data "imported" into Excel?
- Once (if) you change the format to one row for headers (with more columns across the worksheet), can you use an "Append Table" query in Access to add updates to new rows, instead of overwriting the existing information?

hth
Rob

**th3spankst3r** · 05-04-2010, 03:20 PM

Originally Posted by broro183

- Would Access generated "Reports" work for you?

Probably not, because Access has no way of loading/editing "saved reports", and in fact there's only a few ways that I know of to "save" a report without buying software. The most viable of these would be to save them as a PDF. However, you still would not be able to load and edit the PDF from within the database. This is needed to add entries to an ERO.

Originally Posted by broro183

- does anybody enter data directly into the db?

Yes, in fact, all of the data is input directly into the database forms and manipulated by forms as well. Any additions or changes made to the data in my db is done through forms. The idea is that instead of filling out a paper ERO, a user can click on the "Open ERO" button to open the form for filling out an ERO, which provides a little better description for new Marines who don't know much about EROs in the first place. They input the information, click the "Open" button, and it essentially fills out the ERO for them (by exporting to Excel, and then the functions on the ERO worksheet would pull the data over into the correct format). Then they could simply print the ERO when the work is done as a receipt.

Editing EROs works the same way, they would specify an ERO number type in the work that they did to the gear, click "Annotate" and it would add an entry to the specified ERO.

Originally Posted by broro183

- do people enter comments directly into the Excel sheet or is it initially manually written on a printed page & then transferred to Excel?

I think the above quote's response should answer this question, hopefully. Everything will be input into the database. When the repairs are done, that's the only time paper should ever be needed. In the Marine Corps, every piece of gear we have has a record jacket that goes with it, just to provide proof of maintenance history, ownership history, etc. The receipt printed after work is done will go in this record jacket. That, ideally, will be the only paper involved in the maintenance process once my database is implemented.

Originally Posted by broro183

- how is the data "imported" into Excel?

I have written a function within my database that will export a predetermined set of data to a predetermined set of cells in a predetermined worksheet in a predetermined workbook. So when someone is starting an ERO, the db generates the next available ERO number using another function, and then the ExportToExcel function uses that ERO number to open up the blank ERO workbook, switches to the "Import" worksheet, and then pastes the contents of the recordset (created by the Open ERO form) into the predetermined set of cells (which, for opening a new ERO is row 2) and then adds a header (in row 1). After exporting, it switches to the ERO worksheet and then saves the workbook as the ERO number into a specified folder.

When someone is editing an ERO, the predetermined workbook that is loaded is the ERO number designated by the user. It loads the workbook, switches to the "Import" worksheet, pastes the recordset (created by the "Annotate" form) into the specified set of cells (for annotations, this is row 4) with a header (row 3). It then switches to the ERO worksheet and saves the workbook.

When closing an ERO, the same process is followed, except that it prints the ERO worksheet after all exporting and formatting is done.

Originally Posted by broro183

- Once (if) you change the format to one row for headers (with more columns across the worksheet), can you use an "Append Table" query in Access to add updates to new rows, instead of overwriting the existing information?

I don't believe I'll have a problem with overwriting the existing information. I have to make a small adjustment to function ExportToExcel, so that it checks the population of the row that it is trying to export to, and if it is populated, check the row+2, if not, go ahead and export. This way, new entries are added down below the last entry, and not replacing it. I don't know much about "Append Table" queries, but there's already a query being used to create the recordsets. That query isn't populated until after the user fills out the form and clicks the button, so I'm not sure running another query would be a better way to do it.

After I make that adjustment to function ExportToExcel, I believe the only problem I will have is how to format the descriptions of work. Essentially I want the function in all of the "Description of Work" cells to say something like "if I am the first cell NOT populated, populate me with the contents of the row that is 2 rows down from that which is populated above me." for instance let's say that J18 is filled, but J19 is not. J18 is using row 4 to populate itself. I want J19 to use row 6, but ONLY if it is the first empty cell in the "Description of Work" range. So J20 would remain empty.

This sounds confusing when I re-read it, but knowing what I want, it's easy for me to picture it in my mind. Sorry I can't be more descriptive.

**broro183** · 05-05-2010, 06:19 PM

hi,

I'm sorry, I don't think it is worth the effort of developing complex formulae in your existing spreadsheet design...

Mods/others,
I'm warping the direction of this thread away from Excel Worksheet Functions sub Forum towards the Access Forum & I apologise for that. If the OP is willing, this thread may need to be moved again!

Originally Posted by th3spankst3r

...
This sounds confusing when I re-read it, but knowing what I want, it's easy for me to picture it in my mind. Sorry I can't be more descriptive.

It sounds like you have already got the solution in mind. I think you may need to step back & look at this from the start by identifying what the problem is, my understanding is:

1 You have a database that users update with the aim of minimising printouts.
2 Interim printed reports are currently needed for initial manual updating.
3 Final reports are needed for attaching to dispatches.
4 An electronic record of these final reports needs (?) to be kept.

Currently...
a) Some updates occur in the data base.
b) Info is exported to specific cells in Excel & these specific cells are referenced to try & put the info in the desired format*.
c) I think (?) a copy of the Excel file is kept as an electronic record.

My suggestions are made with no regard to your current db setup, but more for ongoing usability:
*If Excel is to remain in the process, I strongly recommend redesigning your spreadsheet with NO merged cells (see earlier post). To remove complexity I suggest you modify your ExportToExcel function to write directly to the correct cells in the redesigned Excel Template. This shouldn't be too much of a change, as you are already writing to specific cells, and it removes the necessity for some complicated formulae to "align this with that...".
However, because users are already interacting with the db, I will go a step further & suggest completely removing Excel from the process:

With that in mind, I think that you can achieve "points 1-4" with Access Reports because:
- All the information is already within your existing database.
- I assume, additions to an ERO via your db are stored within (appended to) an existing Access table which can be used as the source for the reports. See here for an explanation of Append queries (this concept is probably hiding within the forms you use).
- Yes pdf'd Reports can easily be created when necessary. In the mean time, data sits patiently in the Table.
- With this data already existing, the specific report can be created at any time the push of a button (see below).
- Unfortunately I'm not experienced enough to provide a solution esp without seeing your existing db design. However, you may be able to add the flexibility to your db yourself by using the ideas in the below links:

http://www.grbps.com/access4.pdf
http://www.eggheadcafe.com/software/...ecific-re.aspx
http://allenbrowne.com/ser-50.html
http://www.brighthub.com/computing/w...les/42372.aspx
http://office.microsoft.com/en-us/ac...963181033.aspx

Try creating a test Access Report using Create - Report Wizard...
This will give you a starting point & you can then move the controls around in Design or Layout View to achieve the layout you want to see.

If you need any specific help, can you please upload a zipped version of a stripped out sample database?
(I think this is legal file type for uploading...?)
This will allow any of the knowledgable Access guys to whip up a mock form/report without needing to start from scratch, so 1) the solution is more likely to be offered and 2) it will be more specific.

Rob