Format an excel spreadsheet through access vba 1

[Tadynn]

Hi All,

Access 2k
Excel 2k
Excel Spreadsheet: x:\spreadsheets\DBAreapicks.xls

I've got the following module that output data from a table into an excel spreadsheet.

However, what I also want to do, is to give the spreadsheet a bit of a format through code.


What I want to do is format the headings in column "A" to bold and make the fill colour white instead of gray as is MS access default output format.
All the data in row "1" starting from column "B", I want to change the font colour to red.
Then lastly, I want to autofit the whole lot.

Can anyone tell me how I can go about automatically formatting this excel spreadsheet by modifying or adding to the existing code that I have?


Private Sub Report_Click()
On Error GoTo Err_Report_Click

'DoCmd.SetWarnings False

Dim exApp As Excel.Application, newSheet As Worksheet
Dim FileName As String
Dim Rng As Excel.Range
Set exApp = CreateObject("Excel.Application&quot

FileName = "x:\Spreadsheets\DBAreaPicks.xls"

Select Case Me.output.Value

Case "0"

Dim stDocName As String

stDocName = "rpt_Area01"
DoCmd.Minimize
DoCmd.OpenReport stDocName, acPreview


Case "-1"

DoCmd.RunSQL "SELECT dbo_AreaPicks.Date, dbo_AreaPicks.MODULE_LOW AS " & _
"[Module Low], dbo_AreaPicks.MODULE_LOW_PERCENT As [Module Low %], dbo_AreaPicks.MODULE_HIGH AS " & _
"[Module High], dbo_AreaPicks.MODULE_HIGH_PERCENT AS [Module High %], " & _
"dbo_AreaPicks.SHELVING AS Shelving, dbo_AreaPicks.SHELVING_PERCENT AS [Shelving %], " & _
"dbo_AreaPicks.RACK_LOW AS [Rack Low], dbo_AreaPicks.RACK_LOW_PERCENT AS [Rack Low %], " & _
"dbo_AreaPicks.RACK_HIGH AS [Rack High], dbo_AreaPicks.RACK_HIGH_PERCENT AS " & _
"[Rack High %], dbo_AreaPicks.BULKSTACK AS Bulk, dbo_AreaPicks.BULKSTACK_PERCENT AS " & _
"[Bulk %], dbo_AreaPicks.OTHER AS Other, dbo_AreaPicks.OTHER_PERCENT AS [Other %], " & _
"dbo_AreaPicks.Total_Hits AS [Total Hits] INTO [AreaPickResults]" & _
"FROM dbo_AreaPicks " & _
"WHERE (((dbo_AreaPicks.Date) Between [AreaFDate] " & _
"And [AreaTDate]))", 0

DoCmd.OutputTo acOutputTable, "AreaPickResults", acFormatXLS, FileName, False
exApp.Visible = True
exApp.Workbooks.Open FileName
DoCmd.Close acForm, "frm_AreaPicks"
End Select


DoCmd.SetWarnings True


Exit_Report_Click:
Exit Sub

Err_Report_Click:
DoCmd.SetWarnings True
MsgBox Err.Description
Resume Exit_Report_Click

End Sub


Rgrds, Tadynn

 

[bboffin]

Well you've got the start of it OK. This sort of thing can only be done by manipulating the Excel Workbook using the Excel Object Model.

The main thing you need to understand is how to select cells and ranges of cells within the workbook that you have created. There's a lot of good stuff on the MSDN website about the Excel Object Model that you'll need to refer to.

First you need to reference the correct Worksheet. If your Workbook only has one Worksheet you can use the ActiveSheet property of the Workbook otherwise you will have to select the required Worksheet by using its name (this is what appears in the tab at the bottom of the sheet)

For example:
Dim MyWorksheet as Excel.Worksheet
Set MyWorkSheet = ExApp.Workbooks(1).Worksheets("AreaPickResults&quot

In order to manipulate individual cells or ranges cells (even a single cell is treated as range) use the Cells property of the WorkSheet or the Range object.

For example to set the color of the text in cell B1 to red:

MyWorksheet.Cells(1,2).Font.Color = RGB(255,0,0)

Note that the arguments to Cells are Row then Column and that they are numeric starting at 1.

or

MyWorksheet.Range("B1&quot.Font.Color = RGB(255,0,0)

It can be quite lot of hard work to get the results you're looking for but start by changing one thing at a time and you'll soon get the hang of it.

Good Luck