Formatting Excel sheets

[MartinF]

Hi,

I dont know if this is possible, but i'll ask the question anyway.

I have a database that writes a table to an Excel spreadsheet. The table shows planned maintenance on items of equipment, and when the maintenance is due. This is straight forward enough, but i wish to show which tasks have been completed by colouring corresponding cell in the newly created Excel spreadsheet from Access.

Thanks.

 

[Databaseguy]

You can manipualte Excel from Access. First, you have to set reference to the Excel Object library. Then, declare and instaciate the excel object.

Dim objExcel As Excel.Application
Set objExcel = New Excel.Application
'Make it visible (default is invisible) and add a new workbook
With objExcel
.Visible = True
.Workbooks.Add
End With

----Code here to populate the spreadsheet

----Here's some code that formats the spreadsheet

objExcel.Range(YourRange).Select
With objExcel.Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 45
.ShrinkToFit = False
.MergeCells = False
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With objExcel.Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With objExcel.Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With objExcel.Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With objExcel.Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With objExcel.Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With

 

[jebry]

Hi Martin!

Here's a little tip that I learned. If you go into Excel and start a macro recording, do the thing you want to accomplish from Access, and then stop recording. You will be able to go to macro pop-up box and highlight the macro, hit the edit button and you will see the VBA that Excel wrote to do the tasks. You can then use that code to do automate the actions from Access using the Excel object as described by Databaseguy.

hth
Jeff Bridgham

 

[Databaseguy]

That's how I do it.

You don't really think I sat and studied the Excel Object model for hours, did you ? ;-)

Works with Word to.

Unfortunately, Outlook has no macro recording capability.

 

[jebry]

Hi Databaseguy!

I figured you knew!

Just passing the info on!

Jeff Bridgham