Options for Access/Excel report printng - Import Excel as a report or push Db data and print Excel sheet? (1 Viewer)

lAMRITH

Registered User.
Local time
Today, 08:57
Joined
Dec 4, 2019
Messages
17
I work for a small repair facility. Prior to me they have used all Excel sheets and physical log books for tracking jobs. Over the last 2 years, I have developed (complete untrained novice, so it is not properly designed or structured, but I am slowly working on that so be gentle) an Access Db now that we use for tracking once jobs are in the building.

Prior to being entered in the dB each job is hand written up on physical job cards (Job cards are required for our business and documenting work thru the building). These job cards were made in excel and are empty and printed on cardstock and stacked in the shipping area. The salespeople and mechanics then fill them out when they bring work in. The format/design has been tweaked over the years to what works for the shop so I cannot alter the layout.

My goal is to take bringing the jobs in and writing them up directly into the Db. Rather than the sales people hand writing info, and then office staff enters that information into the Db later. We will put a computer terminal out at the incoming area and want to have them enter data directly into the tracking Db and then print out the job card. I am already making the separate forms/queries dB for them to do the data entry without having the back office info there where it can be mistakenly altered.

However now I need to figure out how to print the data onto a report that is identical to the excel sheet we have, or maybe it is easier to export the data to the excel spreadsheet and have it print? We do not need to save the Excel sheet with data, it is just literally a form template that we normally print out blank. I need to make this seamless to the users, literally they fill out the fields and hit print, no having to open or manipulate Excel and print. There are roughly 11 fields of data that will be entered and printed on the job card.

Trying to manually recreate the form in Access would be an excersize in frustration, and probably take more hours than we could save in probably years of streamlining this change will make. It would also have to be done 3 times as each Dept uses a very different form. Is it possible to import the excel sheet in somehow and convert it to a report? Or is there a way for push the data to excel and have it automatically print? Would printing the Excel template to a PDF allow easier use with Access and report printing?

I attach a PDF print of one of the forms here. The info to be printed would be the top 3 rows for customer and unit information and then the shipping type at the bottom. ( I may convert that area to be blank rather than checkbox for simplicity as that is for us in the office and we can adapt)

Looking for any ideas you all may have.
 

Attachments

  • Cylinder Work Order.pdf
    47.8 KB · Views: 624

theDBguy

I’m here to help
Staff member
Local time
Today, 08:57
Joined
Oct 29, 2018
Messages
21,360
Hi. Without being familiar with your business process, that form doesn't look to me like a complicated one that you cannot reproduce in Access. I would recommend creating a Report in Access to mimic the Excel form/report/card and just print it from Access.
 

lAMRITH

Registered User.
Local time
Today, 08:57
Joined
Dec 4, 2019
Messages
17
I don't have hours/days to recreate that form is the problem, it is beyond my ability or experience to even start trying to recreate visually in access. I have only ever used report wizard and tweaked from there.
True it is not a complicated form, but it is quite a large number of cells to have to grid out and size as well as text and the box sizes need to be spot on match to the existing form. Then I have to do it 2 more times for the other 2 dept that use different forms. That is why I am hoping to leverage excel or even a pdf of what we have, I hoped some basic coding would take much less time than recreating from scratch.
 

lAMRITH

Registered User.
Local time
Today, 08:57
Joined
Dec 4, 2019
Messages
17
I should have probably clarified more in OP. I am not an IT guy, not hired to run the system, no training or anything I am just a former mechanic turned office/admin guy doing what I can to try and streamline our process and save us all time while also providing clearer job tracking. I am computer savy decent with Excel, but going beyond basic/wizard stuff on access gets over my head very quickly. :)

I wonder if doing something like this would be a good option?
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:57
Joined
Feb 19, 2002
Messages
42,989
Using a pdf as the background isn't going to help much. You still need to get all the data in the correct spots. If the data is normalized, you will probably have a main report with the header information from the top block. Below that you will have a subreport for the Barrel through bearings section. Then another subreport for the Chrome through total section. The stuff to the right of that list will probably come from the main form.

To make all this line up, you will need to futz with the two subreports to get them to align precisely

To use excel as the presentation method, you would need to poke the cells individually which will be a fair amount of code and will also require that you learn OLE automation.

Try the report. Be patient..
 

lAMRITH

Registered User.
Local time
Today, 08:57
Joined
Dec 4, 2019
Messages
17
I do not think I ever mentioned I am only talking about printing the top 2 rows of information on the printed report. It will not be used to print full information on the entire form. Just the customer and unit information in the top.

So far so good. The linked solution above appears to be working, I converted pdf to png image and imported it as background for the report.
Then drag and place report fields over the image. I have done a test print and things look good.
It was a couple steps to accomplish and did require a free piece of software (or use Photoshop if you have it) to convert.
I used Gimp graphics program to convert from PDF to png.

Excel file ->print to PDF
Open PDF in Gimp -> Export as .png file.
Open Access report ->Select report properties
Go to Format tab in properties, click the ... for picture field.
Navigate to where you saved the png file and select it.
I set the tiling to No and the alignment to center.
Then go in and place your text fields and report output over the image. (see attached)

View attachment 90323
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:57
Joined
Feb 19, 2002
Messages
42,989
I do not think I ever mentioned I am only talking about printing the top 2 rows of information on the printed report.
As you can tell, my answer was completely useless because the spec was erroneous. GIGO:)
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:57
Joined
Sep 12, 2006
Messages
15,614
If you have an excel template, and an access process, I would have thought a relatively easy? way might be to just (in access) copy the excel template to a new file, open the new file, and load the data into the appropriate excel cells.

That way you retain all the formatting in excel, and just manipulate the data in access.
 

DanielSanders

New member
Local time
Today, 16:57
Joined
Feb 23, 2020
Messages
8
You can use excel as report and enter the data in Access. You can name a range or A Cell in excell and write the data straight from Access to this named range or cell. It’s nit much code with vba. I can make it for you. You can also read the data from the excel sheet into Access. I can make it for you.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 15:57
Joined
Jul 9, 2003
Messages
16,245
maybe it is easier to export the data to the excel spreadsheet and have it print?

If you decide to go this route, you may find this code:-


From Bob Larson (BTAB Developments) useful. I demonstrate how you can use it on my blog here:-

Show/Hide & Export to Excel​



Another example of Bob's code in Use:-

Excel Sheets From Access Table​



Before I found Bob's excellent code,
I relied on the two links from Microsoft which you can find on my blog here:-

Update Excel from MS Access​

 
Last edited:

Users who are viewing this thread

Top Bottom