Exporting Reports with subreports to excel (1 Viewer)

[Snappy1263]

Hi I am new to the group and I am in need of some expert help.

I have been searching all morning trying to find information on this. I am not an expert on VBA but I have written some. I have a report called BOM EXPORT ODL with 3 sub-reports linked to it. How do I get the main report and the sub-reports to export to excel. I have written a VBA for other reports I have but they don't need the sub-reports. I would appreciate any help on this. I need this for reporting to customs at the border and I need something easy. Ideally I would like to somehow keep formatting so it can be easily be read.

Thank you so much for your help.

 

[GinaWhipp]

Welcome to the Forum!

This is not easy, especially if the main part contains varying lines. Basically you will need a separate query for each report part... one for main and one for each subreport. You then export the main and if needed, count the rows in the recordset so you know where to start the second part and again count the rows in the second recordset and so on...

That said, you could also create a table and start appending records and then just export the table but I never did that way as the data never ended up where I wanted but it can be done.

Either way it's going to take a bit of coding and plenty of coffee. Sorry but there is no *easy* way to do this.

 

[Snappy1263]

Thank you,

I have a separate query for each report already, then I did an append query and now i have tables for each. They are linked in the report is by the ID number. So I exported each table to excel. How do I tell it to start at a certain row for each??? The problem with this is there would be a lot of work in excel. These are Bill of Materials that need to be group together by the ID number then all the material cost added up. I currently have a report that I save to a PDF that has all the calculations but Customs needs it in excel so they can do their job. I was hoping for not so much work on my end and wanted to export each one to excel and have each Bill of Material separate like it is on the report.

thanks,

 

[GinaWhipp]

Okay, will first three tables is not what I had in mind and for a BoM one table would be difficult. So, is the top of the BoM pretty much standard? If so you could set up a template and them export to the template making it slightly easier.

As for calculation, you have a choice you can either set them up in your template (never did that way) or use automation, which you are going to have to use anyway, and do the calculations in the code which will be performed after the data is exported. Oh, you can also perform the calculations in the query itself and export the results and then you just need to code for a column summing.

No matter how you do it, it's not a quick up time... Depending on your coding abilities (and remember, we are here to help) it should take more than a day or two... we have to allow for testing.

 

[Snappy1263]

Thank you,

The Top of the Main is the same it will have 3 fields, ID, Part Number and Part Description. But the data is different...I don't know how to do a template The 3 subreports have 7 fields. I have 4961 part numbers MAIN REPORT. The ID number links them together. My query does have the calculations I would just have to run totals. How would i be able to separate each BOM since the ID is the key to Main and the subreport. What I did last night was export all 4 to excel and then matched the main with the other 3 using the ID number utilizing vlookup.....I have done some codes on my own but I am no means an expert

 

[GinaWhipp]

A Template is just the layout of the report in Excel with no data. That way when exporting you will just tell Access to drop data in those fields.

You would actually click a button, send the BoM to the Excel Template and it would create itself, make a copy leaving the Template to be used for the next one. So part one would be to create an Excel Template.

 

[Snappy1263]

Ok I created the Template. I have attached for you to see.

Thank you,

 

[GinaWhipp]

Umm, that's a .PDF, you did create it in Excel right? I need the Excel spreadsheet so I can assist with the code you're going to need.

 

[Snappy1263]

yes i did in excel but it wouldnt let me upload it. i will try again.

 

[Snappy1263]

ok i had to save it to a lower version. i have 2010.

 

[GinaWhipp]

Okay great... now the SQL of the queries and a screenshot of the Form the button will be placed on.

 

[Snappy1263]

not sure what you mean SQL of the queries sorry...here is the screen shot of the form

thanks

 

[GinaWhipp]

If you go to the queries you are using for generating the report and then go to Design View then select SQL View in the upper left hand corner... that is the SQL to post.

 

[Snappy1263]

ha thanks you i know what i SQL is but i wasn't sure if that is what you wanted... Here they are.

MAIN REPORT
SELECT [BOM PLUS].ID, [BOM PLUS].[COVER PART NUMBER], [BOM PLUS].DESCRIPTION
FROM [BOM PLUS]
WITH OWNERACCESS OPTION;


SUB-REPORT
SELECT [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].ID, [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].[PART NUMBER], [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].[PART DESCRIPTION], [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].SUPPLIER, [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].COO, [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].[COST W FREIGHT], [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].UOM, [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].QUANITY, [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].Expr1
FROM [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT]
ORDER BY [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].ID
WITH OWNERACCESS OPTION;



SUB-REPORT
SELECT [BOM PRICING EXTENDED DETAILS PACKAGING S EXPORT].ID, [BOM PRICING EXTENDED DETAILS PACKAGING S EXPORT].[PART NUMBER], [BOM PRICING EXTENDED DETAILS PACKAGING S EXPORT].[PART DESCRIPTION], [BOM PRICING EXTENDED DETAILS PACKAGING S EXPORT].SUPPLIER, [BOM PRICING EXTENDED DETAILS PACKAGING S EXPORT].COO, [BOM PRICING EXTENDED DETAILS PACKAGING S EXPORT].[COST W FREIGHT], [BOM PRICING EXTENDED DETAILS PACKAGING S EXPORT].UOM, [BOM PRICING EXTENDED DETAILS PACKAGING S EXPORT].QUANITY, [BOM PRICING EXTENDED DETAILS PACKAGING S EXPORT].Expr1
FROM [BOM PRICING EXTENDED DETAILS PACKAGING S EXPORT]
WITH OWNERACCESS OPTION;



SUB-REPORT
SELECT [BOM PRICING EXTENDED DETAILS LABOR S].ID, [BOM PRICING EXTENDED DETAILS LABOR S].[PART NUMBER], [BOM PRICING EXTENDED DETAILS LABOR S].QUANITY, [BOM PRICING EXTENDED DETAILS LABOR S].Expr4
FROM [BOM PRICING EXTENDED DETAILS LABOR S]
WITH OWNERACCESS OPTION;

 

[GinaWhipp]

Hmm, good thing I can to check, never got this...

 

[GinaWhipp]

Haven't seen this in a long time...

WITH OWNERACCESS OPTION

What version of Access?

You made a lot of extra work with all those spaces in field names. That said I now have the fields lined up so I can read them easier. Please let me know where on the Excel spreadsheet they are to land so I can give you some code to get started.

SELECT [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].ID,
[BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].[PART NUMBER],
[BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].[PART DESCRIPTION],
[BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].SUPPLIER,
[BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].COO,
[BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].[COST W FREIGHT],
[BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].UOM,
[BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].QUANITY,
[BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].Expr1
FROM [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT]
ORDER BY [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].ID

 SELECT [BOM PRICING EXTENDED DETAILS PACKAGING S EXPORT].ID,
 [BOM PRICING EXTENDED DETAILS PACKAGING S EXPORT].[PART NUMBER],
 [BOM PRICING EXTENDED DETAILS PACKAGING S EXPORT].[PART DESCRIPTION],
 [BOM PRICING EXTENDED DETAILS PACKAGING S EXPORT].SUPPLIER,
 [BOM PRICING EXTENDED DETAILS PACKAGING S EXPORT].COO,
 [BOM PRICING EXTENDED DETAILS PACKAGING S EXPORT].[COST W FREIGHT],
 [BOM PRICING EXTENDED DETAILS PACKAGING S EXPORT].UOM,
 [BOM PRICING EXTENDED DETAILS PACKAGING S EXPORT].QUANITY,
 [BOM PRICING EXTENDED DETAILS PACKAGING S EXPORT].Expr1
 FROM [BOM PRICING EXTENDED DETAILS PACKAGING S EXPORT]

 SELECT [BOM PRICING EXTENDED DETAILS LABOR S].ID,
 [BOM PRICING EXTENDED DETAILS LABOR S].[PART NUMBER],
 [BOM PRICING EXTENDED DETAILS LABOR S].QUANITY,
 [BOM PRICING EXTENDED DETAILS LABOR S].Expr4
 FROM [BOM PRICING EXTENDED DETAILS LABOR S]

 

[Snappy1263]

So sorry I wondered why I hadnt heard from you thank u for checking. I have 2010 but it was created 2003 version.

 

[Snappy1263]

On the templete I sent u the 1st row of headings is for the 1st 2 codes n the 2nd row of headings would b for the 3rd code. The headings match the names in those codes....Hope this makes sense.

Thank u so much for ur help I owe u.

 

[GinaWhipp]

So, is the database an .ACCDB or .MDB? Because tried sending code from .MDB to .XLXS and not sure it would work.

 

[Snappy1263]

It is a .mdb