excel automation and coloring

[sam_01]

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 have a access table with 4 fields and wanted to pass those values onto excelsheet and if found that match then do the coloring for the cell

i am opening excel sheet from acess vba and trying to do but unable to
here is my code.
path= "c:\"
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

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

the values in the excel are never sequential so got to walk through each and very cell in excel to found the match

any suggestions would be appreciated
thanks

 

[RoyVidar]

The Excel Range is often used as this

Range("A1").Value = "something"

I e, as you do in formulas in Exel, start with the column, then row.

appXL.range("B" & lngStartRange).Value = rs("Dest").value

 

[boblarson]

Normally you don't use the Application object for the range, you use either the workbook object with

wkbXL.Activesheet.Range("B" & CStr(lngStartRange)).Value = rs("Dest").Value


or the worksheet object:

wksXL.Range("B" & Cstr(lngStartRange)).Value = etc...

I have always had to convert the number to a string since the range is looking for a string.

 

[RoyVidar]

I agree, using the Range method of the sheet object makes more sense here, since that is instantiated and there's nothing else ensuring the correct sheet is used, making the Range method of the application or workbook unsuitable. I didn't see that, I focused only on the column/row thingie.

I thought Usage of ampersand (&) in concatenation, ensured string concatenation vs plus sign (+), which I thought would attempt mathematical operation. I can't remember ever doing type conversion of longs for the Range object, neither within Excel, nor when automating from Access or Word, but then I often use .Cells() in stead

I never use any type conversion when concatenating integers into SQL strings, either. The exception is when I'm working with non-integer numbers (having decimals). Due to different decimal separators around the world, one must ensure US decimal number enters the string, else it will barf (the local decimal separator enters the string, and bam... RT 3075, 3144 ...). Then I might use the Str() function or Format() function.

 

[boblarson]

I've just had type mismatch errors before if I didn't convert them.

 

[Brianwarnock]

I may be totally wrong but isn't the format of the Range ("b2") and wont ("B" & lngStartRange) produce ("B"2), I think that he should use the Cells method , change the B to a 2 then he will find it easier to get the syntax correct.

Or am I wrong?

Brian

 

[boblarson]

Brian:

The range object just needs a string passed to it. I just ran a test and it would appear that you do NOT need to convert the long as it indeed does concatenate automatically into a string. So, I may have had other issues in the past which appeared to be thus but it would appear I didn't need to convert.

So,

xlWS.Range("B" & lngNumber).Select (etc.) should work just fine.

 

[Brianwarnock]

Hi Bob, being a true doubting Thomas I decided to run my own test and yes it works , which means I also had some other error when I last tried it and switched to .Cells

I found it interesting that Range("K" & introw) returned an absolute address $k$2
where introw=2

Brian

 

[sam_01]

excel automation

thanks for such a prompt response.

i have tried no. of options and now i am able to read the value of the first fields .. i tried to debug the values and able to read the value of the particular cell but its not performing any operations and exiting the application like for example
i gave the value as

if (appXL.range(lngStartRange, "a").Value = rs("Dest").area) then
appXL.range(lngStartRange, "b").Value = rs("dest").value
else go to 0
appXL.range(lngStartRange, "c").Value = rs("Dest").value) then
.
now i want to look for the cells inthe range of d:ae and color that particular cell who has all the 3 fields.. field1,field2,field3
.interior.colorindex=8


any thoughts would be appreciated..
thanks

 

[Brianwarnock]

You are still quoting
(appXL.range(lngStartRange, "a").Value

but as has been pointed out it should be
appXL.range("a" & lngStartRange).Value

Brian

 

[sam_01]

excel automation

I have tried the code which u said but its not doing anything, its reading the values from the access table but not comparing the values in the excel sheet

to be mre clear on this , i am sending the excel sheet as an attachment
i have described by intentions or what i exactly need to be specific

thanks once again and would be looking forward for efficient replies

thanks,
bye

 

[boblarson]

I guess I'm not understanding. Are you trying to format the Excel file from Access? If so, can you attach the Access file as well as the Excel file? Also, I know you've tried to explain what is going on, but it still isn't clear to me. Are you wanting a specific color for a specific set of values? Or, what does the color signify? Do you want the same color for cells that match certain criteria?

 

[sam_01]

helo bob,

i am trying to color few cells based on the some conditions which i already mentioned in the previous post..
to be mre clear once again... i am trying to compare fields from access to excel and and i made a make table query in access for comparision of them and the result of that i kept in the one of the sheets of excel

so i have to look for that combination of fields in excel sheet and color the price field in the sheet ..

hope i am able to make to make it clear
thanks
and shall be looking forward for ur the reply