open excel and color cells (1 Viewer)

sam_01

New member
Local time
Today, 02:20
Joined
Nov 19, 2007
Messages
8
hello freinds !!

i ran into a trouble with excel automation , i would appreciate if you can render any thoughts on these:

i am trying to highlight colors in excel cells.
i am opening excel sheet from acess vba and trying to do but unable to
here is my code.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim appXL As Excel.Application
Dim wkbXL As Excel.workBook
Dim wksXL As Excel.workSheet
Dim lngStartRange As Long

strSQL = "select getcolors.Area, getcolors.Dest, getcolors.origin, getcolors.season from getcolors;"

Set db = CurrentDb()
Set rs = db.OpenRecordset("getcolors", dbOpenSnapshot)

If rs.BOF Then
MsgBox "no records to process"
Else

Set wkbXL = GetObject(path)
Set appXL = wkbXL.Parent
Set wksXL = wkbXL.Worksheets("version")

appXL.Visible = False
wkbXL.Windows(1).Visible = True
With appXL
lngStartRange = 2 'start at row 2

While Not rs.EOF
' problem lies from here
appXL.range(lngStartRange, "B").Value = rs("Dest").value

appXL.range(lngStartRange, "C").Value = rs("season").Value
appXL.range(lngStartRange, "D:AE").Value = rs("origin").Value
'appXL.Cells.Interior.ColorIndex = 36
lngStartRange = lngStartRange + 1
rs.MoveNext

Wend
End With
objXLWrkBk.Close SaveChanges:=True

End If

Done:
Set rs = Nothing
Set db = Nothing
Set objXL = Nothing
Set objXLWrkBk = Nothing
Set objXLWrkSht = Nothing
objXL.Quit


Err_Handler:

End Sub

rembr that the data in excel in never sequential so have to walk through each and evry cell and look for the possible combination like
if field1= excel cel then go to field2= excel cel then go to field3= excel cel (higlight colored cell)

any suggestions would be appreciated
thanks
 

Users who are viewing this thread

Top Bottom