Excel Template to Access Report

[brownmary]

Hi.

My company uses a one-page Excel spreadsheet for calculation of annual salaries by department. A paper copy (!) of the spreadsheet is then submitted (that's a different problem).

My data (for multiple departments)is in an Access database. In a nutshell, how do I get my data to look like the required Excel report?

I tried redrawing the spreadsheet as an Access Report. It was too cumbersome in terms of the design.

I've looked into OLE but don't need to work with the Excel application. I just want my final Access report to look like the required Excel report.

Thks for your help.

Mary Brown

 

[Celeste025]

This probably isnt the answer that you're looking for, but it is possible to export access data to excel spreadsheets. If designing a report in Access is too cumbersome, you might find it easier to just export the data and put it into the Excel spreadsheet. In Excel, Id just link the cells of the template to the cells of the export to keep from having to copy/paste every time, the data should always export in the same way.

 

[bobjackson]

All my standard datasheets are in excel, It may not be the best way round it but I group the cells in excel and copy them. I then go into the report and do a paste special as a picture. It then put the format that I had set up in excel in the Access report. I then just position my fields in the right place for the template. Never smile at work, they may think you're enjoying yourself too much and cut your pay.

 

[brownmary]

Bob --

I'm not sure I follow you --

1. Your data is in Excel (is some of it in Access?)
2. You group the data in Excel and copy them.
3. Then you paste special the data as a picture into the Excel (???) report.
4. Then bring the Excel report format into the Access report (how do you do this?)
5. Put (Access?? Excel??) fields in the right place for the report template.

If I have this correct, I'm wondering why you copy it into Access.

Thks.

M

 

[bobjackson]

All my data is in access. But the standard documents that the data has to appear on were produced in excel.
Therefore I group the cells in excel that make up the standard document and do a Ctrl + C. In the access report I then do a paste special and place the excel spreadsheet as a picture into the access report. I then place the text boxes in the relevant positions to line up with the background.



Never smile at work, they may think you're enjoying yourself too much and cut your pay.

 

[brownmary]

In other words, you do everything in Access (sorting the data, listing it, totaling, etc) with the exception that you bring the Excel "picture" in as the background for the Access report.

I hope it is that easy.

M

 

[bobjackson]

Yes. That's all I've done. Access is not very friendly when producing complicated report layouts and unless you buy another program like Crystal to create your forms and reports. It's the easiest way I've found upto now. It may mean your database ends up a bit bigger but I have not had any problems. Never smile at work, they may think you're enjoying yourself too much and cut your pay.

 

[EdFleeson]

Why don't you just set up your form the way you want it in Excel (including any calculations you want to do) then use the "Get External Data" function in Excel to pull the data from Access into your excel table. You can even set up a macro in Excel to run all the updates for you.

 

[brownmary]

Ed and Celeste --

I've decided your approach is best -- to use "Import External Data" to pull my data from Access into Excel. My problem now is that I keep pulling in the results of the entire query when all I want is specific fields in specific cells.

For example, I want Cell A1 to show the Account Number and Cell F1 to show the Account Description. Then I want Cells A30, B30, C30 (with rows continuing however many subaccts) to show the Subacct Description, SubAcct #, and Budget Amount, respectively.

I'd appreciate your help or referral to a site that can help me.

Thks.

Mary

 

[Celeste025]

brownmary,

Personally Id go into Access, to your query and then export it as an Excel file. Then Id open the salary budget report that you need and link the corresponding fields. Each time you do the export, it should set up the file in the exact same way, unless you're changing the setup of the query so that you should only have to do the linking once (as long as you first save the document as a file name and always do that). Each time you need to update the report, export a new Excel sheet and then update the links in the salary budget report.

To make a cell in one workbook equal a cell in another workbook, simply open both workbooks to the right sheets, then use the sum function in the cell that you want to have the data in, then go to the workbook that the data is in and click in the corresponding cell, it will automatically fix the workbook and worksheet names.

Good luck, let us know how it goes.

 

[brownmary]

I've given up on importing external data and am retrying bobjackson's copying the Excel report as a background picture. Am having trouble sizing the picture.

The original Excel spreadsheet is landscape and needs to be reduced to 64% to print on 11 x 8-1/2 paper. When I paste special,I am not getting the picture of the full spreadsheet. Assuming, I get the full picture, I will then need to reduce it. I tried adjusting the PictureSizeMode property and that didn't seem to do anything.

Please help. I am running out of time to compete this project. Thks.

Mary

 

[powella]

I believe PictureSizeMode = 1 is the zoom mode. I should expand or shrink to fill the picture box, or page as a background image.

 

[powella]

oops, I was wrong. Here is an MSDN document on it:

http://msdn.microsoft.com/library/e...reSizeModeProperty1_HV03077469.asp?frame=true

0 Crops any part of the picture that is larger than the form or page (default).
1 Stretches the picture to fill the form or page. This setting distorts the picture in either the horizontal or vertical direction.
3 Enlarges the picture, but does not distort the picture in either the horizontal or vertical direction.

I wonder what happened to 2. mmm I got an error using 2