Export Access table to Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to export a table from Access to excel and add Bold, Centering
and wrap text in the excel spreadsheet
 
Kathy,

I don't believe there is a way to export an Access report to Excel while
maintaining all of the formatting from the report. I've found that you can,
however, export data to Excel and then, using Access, format the data to
look like your report. Keep in mind that I am no expert, nor do I pretend
to be. One of the Access MVP's may, and probably will, have a better
solution.

Here is an example that will change the font to Arial, the font size to 10
and will Center, Highlight, Wrap Text, and Bold your header row:

Dim xlApp As Object
Dim strPath As String

strPath = "Path to export data.xls"

DoCmd.TransferSpreadsheet acExport, 8, [Query or Table name], strPath
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

With xlApp
.workbooks.Open FileName:=strPath
.Cells.select
With .Selection.Font
.Name = "Arial"
.Size = 10
End With

.Range("A1:K1").select
With .Selection
.HorizontalAlignment = -4108
.VerticalAlignment = -4108
.WrapText = True
.Interior.ColorIndex = 36
.Font.Bold = True
End With
End With


formating that is in the report such as Bold, Centering
 
Back
Top