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
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