Formatting and modifying cells in Excel from Access

[robojeff]

I have a query which I am exporting to excel using the TransferSpreadSheet command and I am wondering if there is also a way to format the data in Excel.

What I would like to do is after transfering my query, is to replace the first row (which contains my query Field Names) with a title with some names that are more descriptive and specific to the data in each column.

Also, I would like to size the columns to auto fit and center the text in all columns.

My code for exporting the query is as follows:
DoCmd.TransferSpreadsheet acExport, cSpreadsheetTypeExcel9,
strTemp, "C:\" & strFileName & ".xls"

Is there a painless way to do the formatting that I mentioned above?

Thanks

 

[HarleyQuinn]

This is simple using excel automation.

Record a macro of yourself doing what you want to do with the spreadsheet (naming, resizing etc.) and then you can use that pretty much directly in your code using an Excel application object with a reference to Excel X.0 Object Library.

That should be able to help you get on your way.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PHV]

replace the first row (which contains my query Field Names)
Why not simply use column aliases in your exported query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robojeff]

Thanks for the tips HarleyQuinn & PHV-

I will read up on how to do those..

 

[robojeff]

I just tried harley Quinn's suggestion and recorded a macro in excel which I would like to port over to my access code so that I can format each worksheet that I export with my transferspreadsheet method....

But how do I set this macro up in access to modify the worksheets in excel?

Thanks again

 

[robojeff]

I meant to include the following excel macro in my last post but forgot to add it:

--------------------------
Sub cleanup()
'
' cleanup Macro
' This macro bolds the heading, autofits columns and centers text
'
' Keyboard Shortcut: Ctrl+z
'
Rows("1:1").Select
Selection.Font.Bold = True
Cells.Select
Selection.Columns.AutoFit
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
--------------------------

How do I run this from access so that this formatting occurs with every worksheet that I create with my transferspreadsheet function?

thanks

 

[jsteph]

robo,
Here is a snippet from something I'd done, (names have been changed to protect the innocent), and all variable dims aren't there, and there's other changes, but this should give you an idea.

The idea about running a macro is excellent, but just remember in your code that you MUST precede the objects in the macro with the Exl. object as in my example, ie, in a macro it'll say Selection.Range...blah; you'll say Exl.Selection.Range...blah.

On Error Resume Next
dim Exl as Excel.Application,oBook as Excel.Workbook
dim oSheet as Excel.Worksheet
Kill strExcelPath & strFname
On Error GoTo errD

'create the excel app object
Set Exl = New Excel.Application
Exl.Visible = False 'set true to watch things happen, ebug, etc.
Exl.DisplayAlerts = False
Exl.ReferenceStyle = xlA1 'as opposed to xlR1C1

Set oBook = Exl.Workbooks.Add 'here I'm creating NEW file, but in your example you use the .Open
On Error Resume Next 
oBook.SaveAs Filename:=strExcelPath & strFname
If Err.Number <> 0 Then
'tell user something bad happened--file exists or whatever
End If
On Error GoTo errD 'back to your handler
Set oSheet = Exl.ActiveSheet 'you can choose any sheet
strRng = "A1:Q250" 'this is arbitrary for example purposes
oSheet.Range(strRNG).Select
Exl.Selection.NumberFormat = "#,##0.00"