How do I Export to Excel Template and apply formatting

[Snowflake68]

I need help please. I have been trying to find a solution to a problem for months now. I have read many pages on forums and tried lots of different pieces of code but I just can’t find a clean simple solution that works or that is easy enough for me to understand/amend for my requirements.

I really don't know VBA although the systems that I have built do contain VBA code that I have 'borrowed' from lots of places and amended until I got it to work. I can't write my own code from scratch yet but I have booked myself on a VBA course at the end of September plus I am doing MTA & MCSE course as well as working full time so I'm really hoping someone can help me out here as I just don't have the skills or the time right now.

I’ll try to explain what I need;
I have built an Access database system for Sales reps to produce individual quotes for their own customer base. Each Sales Rep has their own local copy of the Access database system. They can currently export the quote to an Excel workbook but then they have to do lots of manual formatting before it is presentable enough to give to the customer.

I need to develop the system that will export the data and then to do all of the formatting to the Excel worksheet for them.

I was hoping that I could create an Excel Template that is placed on each sales reps laptops. The template would contain the company logo and other static details in the header and footer and then get the Access system to export the data and populate a copy of the template which would automatically apply the necessary formats. The excel sheet needs to have the following;

• 
  Company logo and Company contact details. (same for every output as this is my company logo, so would assume it could be positioned on a template (Logo in header and company details in the footer)
  Sales Rep Details (Name, Mobile Number) which would change for each Sales Rep but this would be obtained from the Access database) and positioned in the left-hand side of the Footer
  Column Widths- Auto-fit to data
  Columns Headings - Wrap Text to fit column width
  Format Data - I need to format some of the columns to currency and percentage
  Group by Type: The Excel layout needs to group by product type and then sort by Net Price. (I've done this is an Access Report but don't know how to do it in an Access Query or Excel worksheet.
  
  Example of grouped data by product type (this is just made up data plus the export has about 30 columns of information)
  
  
  Item Number Item Name Net Price​
  JEANS
  
  A12345 Black Jeans £5.50
  A78943 Blue Jeans £6.50
  A34562 Red Jeans £7.50
  A34562 Red Jeans £12.50​
  TROUSERS
  
  B45670 Black Trousers £3.45
  B44362 Blue Trousers £4.40
  B45142 Red Trousers £10.75​
  SHOES
  
  C77915 Blue Shoes £23.45
  C78900 Green Shoes £32.75
  C78923 Black Shoes £44.45
  ​
  Some other points to remember are; I currently have all of the customers information and Sales rep data in the same export query that produces the quote information so it is currently repeated on every row of each product. I only want the customer and sales rep info to be in the header and footer (not against each line of the products) so I may need to have the report pull from two different queries. Really unsure here.
  
  Sounds simply enough I thought, but everything I've tried hasn't worked. I have tried using a macro to export the query to a template but couldnt get it to work. I would have thought that Microsoft would have built something into Access to do this more easily by now. :banghead:
  
  I am now under a lot of pressure now from the Sales Director plus all the Sales Reps are on my case (you know how pushy Sales reps can be; love them guys really ).
  

 

[Ranman256]

1. What about exporting the excel data
then in XL, run a macro to does all the formatting?
Because even if you use a preformatted template, access could overwrite formats.

2. you could build an export table. This table has all the formats in it, then when you export, it will have most of the formatting done.

3. (just like 1) access exports the data, then uses the macro to format the excel sheet.
in 1., the user runs the macro, but here in 3, access runs the macro.
Either way the macro must be built but in access , it requires a LOT of programming.
(hence ver 1 seems better)

 

[spikepl]

If your Excel formatting is really fancy, then another option is to have an Excel workbook and import data from Access via a query you call from Excel. That could wind up in a table while, AFAIK, retaining the formatting. Alternatively, the table could be on separate worksheet and from there you could reference the cells as required in your presentation worksheet.

 

[Snowflake68]

1. What about exporting the excel data
then in XL, run a macro to does all the formatting?
Because even if you use a preformatted template, access could overwrite formats.

2. you could build an export table. This table has all the formats in it, then when you export, it will have most of the formatting done.

3. (just like 1) access exports the data, then uses the macro to format the excel sheet.
in 1., the user runs the macro, but here in 3, access runs the macro.
Either way the macro must be built but in access , it requires a LOT of programming.
(hence ver 1 seems better)

Thanks for your reply.
1. This is what I have been trying to do but don't know how. However I would still need to export the data to my template but I haven't even succeeded with that one yet :banghead:

2. An export table wont work either as it doesn't deal with the issue of the header and footer that i need in the Excel file.

3. If I knew how to do that then i would do it but I don't.

My skills are very limited with all of this but thanks for trying to help anyway.

 

[Snowflake68]

If your Excel formatting is really fancy, then another option is to have an Excel workbook and import data from Access via a query you call from Excel. That could wind up in a table while, AFAIK, retaining the formatting. Alternatively, the table could be on separate worksheet and from there you could reference the cells as required in your presentation worksheet.

Thanks for your reply, I did try that option but they would need to have the Access database system open with the quote still in the table that the export query pulls from and I dont think they would understand that. It needs to be kept simple and all in one system really. Also I couldnt get a MS Query to work in the header and footer.

Ill keep looking and trying different things and hopefully ill find a solution that suits everyone.

 

[Ranman256]

docmd.transferspreadsheet..... filename
call shell(excel.exe, filename)
then run macro in xl to do formatting.

1,2,3