How to create a Spreadsheet from MS ACCESS 2003

[javimen2800]

I am trying to find out how I can make a report by transferring the data from an Access Table to MS Excel spreadsheet showing dollar values for each customer.

 

[Rabbie]

It's quite simple. Just write a VBA routine that opens a recordset with the data ou want to transfer and then write it to an Excel Apllication that you can open in Access VBA. Finally save it in a file of your choice.

 

[James Dudden]

Here's some code that I have used to get you started:

Private Sub ReportByPlot_Click()
On Error GoTo Err_ReportByPlot_Click

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "YOUR QUERY HERE", "YOUR PATH HERE" & ".xls"

Dim x As Object
Set x = CreateObject("Excel.application")
x.Workbooks.Open FileName:=Path
x.Visible = True
x.ActiveSheet.Name = "Report by Plot"

With x.Rows(1)
.WrapText = True
.VerticalAlignment = xlBottom
.HorizontalAlignment = xlCenter
.RowHeight = .RowHeight * 2
End With
x.Cells.Font.Name = "Arial"
x.ActiveWindow.Zoom = 75
x.Columns("A:H").AutoFit
x.Rows("2:1000").VerticalAlignment = xlTop

With x.ActiveSheet.PageSetup
.PrintGridlines = True
.PrintTitleRows = "$1:$1"
.LeftMargin = 0.9 / 0.035
.RightMargin = 0.9 / 0.035
.TopMargin = 1 / 0.035
.BottomMargin = 1 / 0.035
.HeaderMargin = 0.5 / 0.035
.FooterMargin = 0.5 / 0.035
.LeftHeader = "Report by Plot"
.CenterHeader = ""
.RightHeader = ""
.Orientation = xlPortrait
.PaperSize = xlPaperA4
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With

Exit_ReportByPlot_Click:
x.Visible = True
Exit Sub
Err_ReportByPlot_Click:
MsgBox Err.Description
Resume Exit_ReportByPlot_Click

End Sub