Access to Excel Help required

[Andy]

Here is what I need to do, just need to tie it all in with VBA for Excel
from within Access

I export my data into an excel spreadsheet, autofilter and arranfge column
widths, then e-mail to an exchange folder, all from VBA

I now want to also colour code the rows depending on the value in one of the
columns.

1) Determine number of rows to be placed on excel (this should equate to
number of records + 1)
dim reccount
reccount = DCount("ID", "Inventory")


2) For correctness, discount row1 (this is a heading row)
Just start from D2


3) select range D2 to Dx (column D has required value)
use a loop D2 to reccount


4) could possibly use loop and case to determine value of cells
if Planning found in cell dx, colour the row in blue, if Engineers found,
colour red, or leave uncoloured if neither value found

e.g.

dim Counter
For Counter = 2 To reccount Step 1
If Not IsError(.Value) Then
Select Case .Value
Case "Planning"
.Interior.ColorIndex = 45
Case "Engineers"
.Interior.ColorIndex = 20
End Select
End If
Next Counter

or

Sub Check_Range_Value()
Dim rnArea As Range
Dim rnCell As Range

Set rnArea = Range("D2" reccount)

For Each rnCell In rnArea
With rnCell
If Not IsError(.Value) Then
Select Case .Value
Case "Planning"
.Interior.ColorIndex = 45
Case "Engineers"
.Interior.ColorIndex = 20
End Select
End If
End With
Next

End Sub

Here is my current code, just need to know how to colour the row, not just
the cell and where to put it in my existing code (below)
---------------------
Private Sub Form_Load()
Dim delFile
On Error Resume Next
delFile = "c:\inventory.xls"
DoCmd.SetWarnings False
Kill delFile

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"Inventory", "c:\inventory.xls", True
End Sub
---------------------
Private Sub ToExcel_Click()

Dim xl As Object
Set xl = CreateObject("Excel.Application")

Set xlwb = xl.Workbooks.Open("c:\Inventory.xls")
Set xlws = xlwb.Worksheets("Inventory")

xl.Visible = True

With xlws
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
.Cells.Select
.Cells.EntireColumn.AutoFit
Selection.AutoFilter
.Rows("1:1").Select
Selection.Font.Bold = True
End With
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A1").Select
xlwb.Application.ActiveWorkbook.Save
xl.Quit
Set xlws = Nothing
Set xlwb = Nothing

Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
..To = "(e-mail address removed)"
..Subject = "Inventory"
..Attachments.Add ("c:\Inventory.xls")
..Send
End With

End Sub

 

[John Nurick]

Hi Andy,

A few thoughts:

* You seem to be using TransferSpreadsheet to export a table to a
worksheet, and then deleting the first column of the sheet. If you don't
need that column, don't export it in the first place: create a query
that returns the data you need, and export that.

* Since you're having to use Automation to launch Excel anyway, you may
like to cut out TransferSpreadsheet and use Excel's CopyFromRecordset
method to paste your data into the place on the sheet where you want it.
If you search Google Groups for
klatuu copyfromrecordset
you'll find some useful stuff.

* In general when automating Excel or Word, it's a good idea to avoid
using the Selection object where possible, and instead to work with
range objects. So instead of stuff like

With xlws
.Cells.Select
.Cells.EntireColumn.AutoFit
Selection.AutoFilter
.Rows("1:1").Select
Selection.Font.Bold = True
...

You'd do stuff like this, which in most circumstances is faster and
safer:

Dim raR As Range
...
Set raR = xlws.UsedRange
raR.Columns.Autofit
raR.AutoFilter
With rar.Rows(1)
.Font.Bold = True
.Interior.ColorIndex = 15
...

*To colour the rows, consider using conditional formatting. Select a
range consisting of the UsedRange without the first row, then add the
conditional formatting (I recorded an Excel macro while I formatted some
cells, then changed the recorded code from using Selection to using the
range I'd defined):

With xlws.UsedRange
Set raR = Set R = Range(.Cells(2, 1), _
(.Rows.Count, .Columns.Count))
raR.FormatConditions.Add Type:=xlExpression,
Formula1:= "=$A2=""Engineer"""
rsR.FormatConditions(1).Interior.ColorIndex = 14
rsR.FormatConditions.Add Type:=xlExpression, _
Formula1:= "=$A2=""Planner"""
rsR.FormatConditions(2).Interior.ColorIndex = 3
...

 

[fangliang]

test