export to excel and format excel rows?

  • Thread starter Thread starter pebsanne
  • Start date Start date
P

pebsanne

Hi, I am trying to export to excel file and format the cells so that the
column names is underlined and bolded, and maybe even freeze the top row.
I can export the query using transferspreadsheet. Any ideas after that?
thanks
 
Hi, I am trying to export to excel file and format the cells so that the
column names is underlined and bolded, and maybe even freeze the top row.
I can export the query using transferspreadsheet. Any ideas after that?
thanks
Click to expand...

First, I would create a template in Excel so that you can format
everything just the way you like it. Then if you need them, create
macros to insert anything that will not go in a specific cell or range
of cells. Then you can use the code at Access Web to transfer the
data to your template using CopyFromRecordset.

http://www.mvps.org/access/modules/mdl0035.htm
 
Or check out this code and change it to suit:

EXPORT A FORM'S RECORDSET TO EXCEL

'---------------------------------------------------------------------------------------
' Procedure : Send2Excel
' Author : Bob Larson
' Date : 5/25/2008
' Purpose : Send any single recordset form to Excel. This will not work
with
' subforms.
' Use : You may freely use this code as long as the author information
in
' this header remains intact
'---------------------------------------------------------------------------------------
'
Public Function Send2Excel(frm As Form, Optional strSheetName As String)
' frm is the name of the form you want to send to Excel
' strSheetName is the name of the sheet you want to name it to



Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim intCount As Integer
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107

On Error GoTo err_handler

Set rst = frm.RecordsetClone

Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
ApXL.Visible = True

Set xlWSh = xlWBk.Worksheets("Sheet1")
If Len(strSheetName) > 0 Then
xlWSh.Name = Left(strSheetName, 34)
End If
xlWSh.Range("A1").Select
Do Until intCount = rst.Fields.Count
ApXL.ActiveCell = rst.Fields(intCount).Name
ApXL.ActiveCell.Offset(0, 1).Select
intCount = intCount + 1
Loop

rst.MoveFirst
xlWSh.Range("A2").CopyFromRecordset rst
xlWSh.Range("1:1").Select
' This is included to show some of what you can do about formatting.
You can comment out or delete
' any of this that you don't want to use in your own export.
With ApXL.Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
End With
ApXL.Selection.Font.Bold = True
With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
' selects the first cell to unselect all cells
xlWSh.Range("A1").Select

rst.Close
Set rst = Nothing

Exit Function
err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Exit Function

End Function



--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________
 
I wsa wondering if this function would also work if using the results of a
select query instead of a form? If so, what would need to change? I've
tried modifying the code the way that I thought it would work but no luck.

Thanks,
Michael
 
When using a template it can be done more simple.


Public Function Export_2_Excel()
Dim OutFile As String
Dim Template As String
Set Fs = CreateObject("Scripting.FileSystemObject")

On Error GoTo FileNotFound

OutFile = "c:\temp\" & Environ("username") & "-export.xls"
Template = "path 2 Template-export.xls"

'Now copy the template to the output location

Fs.CopyFile Template, OutFile, True

'Transfer table or query data to output file
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "query", OutFile

'Finally open the generated excel file
R = Shell("Excel" & " " & OutFile, vbMaximizedFocus)
Exit Function

FileNotFound:
R = MsgBox("File NOT exported. File does not exist of may be in use." &
Chr(13) & "Check if file is not already opened in Excel.", vbExclamation,
"File not found or in use")

End Function

With this you can export multiple tables/query's to different sheets to one
xls file by simply adding extra lines like this.

....
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "query1", OutFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "query2", OutFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "table1", OutFile
....
 
Back
Top