How to/Can I use Access to modify and print pre-existing forms made in Excel.

[codyj]

I am brand-spanking new to Access, and I am not even sure that this is the best program to use for what I would like to do. I will do my best to explain what I am looking to do.



I would like to create something that will make printing forms for my job much easier. My job is to assess the maintenance program onboard US Navy ships. We have six different Excel spreadsheets that we print out and use in order to perform this job. Each spreadsheet has an information block on the top where ship, group, and other pertinent information is pre-filled. Some of these spreadsheets we require multiple copies for just one group. We print these out and write in the score numbers for each attribute, and make any necessary notes.

Right now, I am preforming this task by opening an Excel spreadsheet for Form 1, and there are tabs on the bottom for each Group. I have to click on each tab and hit print, 30-50 tabs. Then I do the same for Form 2, and so on.

Is there a way to use Access to see all of the groups, and each form for each group and easily print all of the forms I need? In my head I see each group as a row, and each form as a column where I can enter the number of copies I want, then hit the print button and I will get all 300 sheets of paper I need. Is this possible?? Am I using the right program?? Thanks for the help!!

[June7]

Yes, VBA code can automate this process but the code would likely be quite complicated. Personally, I would convert all to Access and eliminate the Excel. I think in the long run it would be faster and simpler than figuring out code to manipulate spreadsheets. I have some experience with approach to programmatically generate dynamic documents from Excel and prefer Access.

Use Access report objects to design and print your forms.

Suggest you spend a week studying an introductory tutorial book for Access to learn the basics of relational database principles and Access functionality. Most tutorials include an introduction to programming concepts. Here is a primer on Access programming tools https://support.office.com/en-us/art...0-DAB7C75CBE0C.

Are you saving these score numbers and notes into spreadsheet?

[ranman256]

dont.
its best to change forms by hand.

[June7]

ranman, I don't think OP is asking how to modify design of Excel but how to dynamically change data and print. However, converting the Excel structure to Access would be a manual design and build exercise.

[ssanfu]

First, thank you for your service. Much appreciated.
And welcome to the forum.

If you want to stay in Excel, you can create a user form or another worksheet to do what you want. I am not exactly sure how many workbooks or how many worksheets are involved, but it is not hard to add a button to execute code to print from a worksheet.
Here is a simple example I just created:

Sub PrintGroups()

    ' select printer to print to - can be commented out to use the default printer
    Application.Dialogs(xlDialogPrinterSetup).Show
    '
    Sheets("Form1").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=50, preview:=False, Collate:=True, IgnorePrintAreas:=False

    Sheets("Form2").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=20, preview:=False, Collate:=True, IgnorePrintAreas:=False

End Sub

Form1 is printed 50 times and Form2 is printed 20 times.

You can add a button to a worksheet or create a userform to do more......

Is there a way to use Access to see all of the groups, and each form for each group and easily print all of the forms I need? In my head I see each group as a row, and each form as a column where I can enter the number of copies I want, then hit the print button and I will get all 300 sheets of paper I need. Is this possible?? Am I using the right program??

Add a worksheet, set it up like you envision, add a button and add the code.


It has been over 5 years since I did any Excel programming (I'm a little rusty), but, with a little work, you should be able to figure out how to have code initiate your printing.

[codyj]

June7, Thanks for the info and link. I will dig in to the info and see what I can do with it.

Currently, we do enter our data into the spreadsheets. I intend to explore that option in the future because I think it will make things easier overall, but there is a stupid issue. My office does not own the spreadsheets and they are password protected. We are not allowed to use unprotected sheets.......because people are paid money to create and update them. Its stupid. If I can come up with a better solution then what they have then I will. Baby steps!!

ssanfu, I dont mind using Excel if I have to. But I am lazy and want to move my finger as little as possible!! No problem for serving, it is a fun time and makes for great stories!!

[ssanfu]

Enjoy learning Access....

You could still create your own Workbook with the print code that opens the other workbooks that are protected from edits.
Could be done in Access, but requires a little to a lot more code.

Good luck...