Hi,

We have an access 2003 report that is run weekly and sent out to one of our customers. They have now requested that we supply them the data from this report in excel format.

The report has four grouping levels (Agent Code, Customer, customer branch and partcode) along with totals for Customer branch, customer, and Agent Code.

As expected the exported excel spreadsheet is a complete mess and is unusable.

I have tried several different methods of getting the data out of the report (eg exporting as txt or csv) but the fields do not line up properly.

I was wondering if anyone could offer any advice as to the best method of getting the data from the report into excel. I can't really export directly from the query as a lot of the calculations are done in the report.

Any advice would be greatly appreciated as I am pretty stuck here.

Thanks,

James

This is achievable but not without some knowledge of vba. I would be tempted to create queries that match the groupings in the report and export the queries. An alternative is to create a template in Excel and open up the Excel spreadhseet and populate it with the raw data and let Excel do the calculations. This is the more complex route. Can you provide a sample of what the report looks like?

David

Thanks for the advice.

I have attached screen captures of the report in snapshot and design view.

I'm definitely not a VBA expert but I have some experience, I think I should be able to get the calculations done in queries rather than in the report.

Thanks,

James

Now this is only a suggestion and not the only solution. From what I can see it looks like a repeating set of data consisting of 4 rows and a sub total row.

If you set up an outer loop in vba and filter and group recordsets by each product code with the necessary columns (looks like a cross tab to me) this should get you the detail. Copy the recordset to the worksheet using the CopyFromRecordset method. Then write the subtotal formulas under the current row.

All the tiem keeping count of what you are on so than when you do the next loop it knows which row to copy the next block of data to. Do this until you reach the end of the file. From the pages count on the snap shot it looks like it is going to be a lengthy spreadsheet.

If you can get it to work with say a couple of sub sets then expanding the scope will be simple.

What you need to do first is to create a template workbook that has all the necessary column headings and formatting in place. Then using automation copy the template to a working xls, so as not to damage the template, and populate that.

It may sound a bit scary at first, but to me it seams the most effective way.

You could actually try to take out all the niceties from a report and try and export that to see if it is any better.

David

That does sound quite complex, but I will give it a try.

I have asked our sales rep for the company requesting the spreadsheet if they would be happy for us to change the layout of the report.

We do have the following function which we use to export another query to excel:

Sub ExportToExcel()
Function OUTPUT_company_YEAR_WH()
Dim strQueryName As String, strFilePath As String
Dim rst As ADODB.Recordset
Dim objExcel As Object
Dim objWB As Object
Dim objWS As Object
Dim n As Long
' query name
strQueryName = "[PR-REPORT-AGENT_WH_YEAR_XLS]"
' path to workbook template
strFilePath = "\\server\Ac_Output\Output_Templates\PR-TEMPLATE_comapany_YEAR_WH.xls (file://server/Ac_Output/Output_Templates/PR-TEMPLATE_comapany_YEAR_WH.xls)"
' create recordset
Set rst = New ADODB.Recordset
' open query
rst.Open strQueryName, CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic, adCmdStoredProc

' start Excel
Set objExcel = CreateObject("Excel.Application")
' get workbook
Set objWB = objExcel.workbooks.Open(strFilePath)
' use first worksheet
Set objWS = objWB.worksheets(1)
' add field names
For n = 1 To rst.Fields.Count
objWS.cells(1, n).Value = rst.Fields(n - 1).Name
Next n
' output data
objWS.cells(2, 1).copyfromrecordset rst
'close recordset
rst.Close
' save workbook with date stamp
'objWB.SaveAs objWB.Path & "\\server\Ac_Output\Reports_eop\Company Year WH" & Format(Date, "yyyy-mm-dd") & ".xls"
objWB.SaveAs "\\server\Ac_Output\Reports_eop\company (file://server/Ac_Output/Reports_eop/company) Year WH.xls"

' close workbook
objWB.Close False
' quit excel
objExcel.Quit

'clean up
Set objWS = Nothing
Set objWB = Nothing
Set objExcel = Nothing
'End Sub
End Function


I didn't write this myself but do you think I could (heavily) modify it to export the query to a spreadsheet once I have changed the query so that all calcs are done there rather being done in the report?

Thanks for the advice.

Regards,

James

Your example if fine, however, it only exports 1 query into the worksheet it does not give sub totals or grand totals. If this is fine then go with it. If not you would have to revise it to get the layout you want.

The key to that is the line

objWS.cells(2, 1).copyfromrecordset rst

The rst is the query and it is hooking row 1 col 1 to cell A2

It is not performing any formatting either.

David