Exporting Reports with subreports to excel

[snappy1263]

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.

[ItsMe]

I think you will have a lot of difficulty exporting a Report Object to Excel. Automation of Excel or, at least, use an Excel file as a template and export queries.

Have you tried using the OutputTo method for exporting reports?
https://msdn.microsoft.com/en-us/lib.../ff192065.aspx

DoCmd.OutputTo acOutputReport, "ReportName", acFormatXLS, "\\ServerName\Folder\FileName.xls"

[snappy1263]

Yes I use this code all the time for my reports but for this particular one I need the sub-reports also to export with the main report. How would I do it with queries and make sure the information matches the the main information with the sub information. I have the Main report and the Sub-report linked by ID number.

[ItsMe]

The only way I know of is to use lots of code and define the ranges in the spreadsheet(s).

[snappy1263]

Yes I found some while I was researching and it was very confusing to me. I was hoping for a simple solution, I was reading where people are doing it but not any information on it.

[ItsMe]

I was reading where people are doing it but not any information on it.

The only other thing I can think of would be to use XML and a custom XML schema. Again, lot's of code.

[snappy1263]

That is what I was trying to avoid. These are Bill of Materials with subreports listing all the materials. The main form has all other information. I need to get this all in excel for customs so they can get it in their system. I was doing PDF's but that was more work for them.

[ItsMe]

Maybe you can hire a consultant to create a module for you. I honestly do not know of a simple way. The Export Wizard is what it is. If your organisation is domestic to the USA, I might be able to help by creating a custom module.

[snappy1263]

Yes I am in the USA....My company exports are finished product from Mexico. I will have to check on that. Thank you for your help.

[ItsMe]

Feel free to send me a PM if you would like my contact info. It may be most convenient if you can find somebody local to you. However, I would not guess that it would be difficult to manage an entire project like this remotely.