How Access makes complex reports--using Excel to output complex reports

Access|excel
How to make complex reports--using Excel to output complex reports



How to make complex reports--using Excel to output complex reports

Before reading this article, first of all, to remind you that Access itself report also has a strong practical and powerful features, only when you discover the full functionality of its own but still can not meet your special requirements for the report, please use Excel output report. Obviously, the first weakness of using Excel to output a report is that you have to open both Access and excel at the same time (of course, you have to install Access and Excel on one machine at the same time), and of course if you and I have the CPU and 256M memory on the 966MHz and the large capacity Hard drive, you don't have to think about it, go ahead.
First locate the VBA programming interface and find the Tools menu and click the Reference ... menu item, find the "Existing references" list in the pop-up references form, and tick the box in front of the item named "Microsoft Excel x.0 Object Library."

Second, you should do a blank Excel spreadsheet, set the font, border, print paper, and so on and save it. (The table in this example is named "Temp.xls") of course, it's OK to use a blank Excel file directly, but you have to programmatically generate some content, such as table borders, background colors, cell formats, and so on, which involve Excel programming, which I will later describe in Excel columns. There's not much to say here.

Then you can make your own print preview form in Access, set up 2 command buttons in the form, and enter the following code in the Click event of the button named preview:

Private Sub Excelpreview_click ()
Dim xlapp as Excel.Application
Dim xlbook as workbook, xlsheet as Worksheet


Set xlapp = CreateObject ("Excel.Application")
Set xlapp = New Excel.Application
Xlapp.visible = True
Set xlbook = XlApp.Workbooks.Open ("C:\reprot\temp.xls")
Set xlsheet = xlbook.worksheets (1)
Xlsheet.cells (3, 1) = "Tabulation Date:" + "12" + "Month"
' The above changes only the data in one cell, and can be added to the number of times as needed
Xlbook.save
Xlsheet.printpreview ' If you want to print, just change the PrintPreview to PrintOut
Xlbook.close
xlApp.Quit
End Sub


OK, and then you can click the button to see how the resulting table is.