Access 2003 - Modifying Excel sheets

[Jcase]

Hey guys, another newbie question for you.

I'm looking to add some color to excel sheets generated by a module.

I've been doing 100% of my VBA in access, working with recordsets, tables and what not, but what is the proper way to interact with Excel via Access VBA?

The main goal of what I want to do is color a row based on a column's field. So if field "Color" = Blue then that whole row would be colored blue.

This is all I have at the moment.

Sub ColorCells(Path As String)

Dim oExcel As Object
Dim oWB As Workbook

Set oExcel = CreateObject(Excel.Application)
Set oWB = oExcel.Workbooks.Open(Path)

End Sub

But at this point I don't know how to manipulate information in this environment. Would someone be able to point me to a good tutorial or offer up a suggestion?

Thanks guys!

 

[chergh]

This is how I would do it, have a look then ask any questions you have, make sure you have the excel library reference checked if you need to use late binding it's a little more complex as you will need to declare some constants to get the functionality.

Sub ColorCells(Path As String)

Dim oExcel As Object
Dim oWB As excel.Workbook
dim oWS as excel.worksheet

Set oExcel = CreateObject(Excel.Application)
Set oWB = oExcel.Workbooks.Open(Path)
set oWS = oWB.worksheets("Sheetname")

for i = 2 to oWS.range("A65536").end(xlup).row 'This assumes the data you need to look at is in column A

select case oWS.range("A" & i).value

case "Red"

oWS.rows(i).interior.color = vbRed

Case "Blue"

oWS.rows(i).interior.color = vbvlue

end select

next i


End Sub

 

[Jcase]

This is how I would do it, have a look then ask any questions you have, make sure you have the excel library reference checked if you need to use late binding it's a little more complex as you will need to declare some constants to get the functionality.

Sub ColorCells(Path As String)

Dim oExcel As Object
Dim oWB As excel.Workbook
dim oWS as excel.worksheet

Set oExcel = CreateObject(Excel.Application)
Set oWB = oExcel.Workbooks.Open(Path)
set oWS = oWB.worksheets("Sheetname")

for i = 2 to oWS.range("A65536").end(xlup).row 'This assumes the data you need to look at is in column A

select case oWS.range("A" & i).value

case "Red"

oWS.rows(i).interior.color = vbRed

Case "Blue"

oWS.rows(i).interior.color = vbvlue

end select

next i


End Sub

I'm not sure if I'm referring to the excel library, how would I check/do this?

 

[Mutdogus]

Another thing that I do when I work with excel is I record a macro in excel to do what I want it to do. This automagically generates all the code required to do the task that I want to do, because I'm doing it manually. Then I copy / paste the macro into Access and modify it for exactly what I want.

I know it's the lazy way vice actually learning the entire object model but if you are just learning VBA with excel it's an easy way to figure out how the object model works.

 

[chergh]

To add a reference goto tools menu then click references and find the reference for the excel object library, it's alphabetical so scroll down to where they start with Microsfot and you will find it there.

 

[Jcase]

To add a reference goto tools menu then click references and find the reference for the excel object library, it's alphabetical so scroll down to where they start with Microsfot and you will find it there.

Found and added! Thank you!

 

[Jcase]

Sub ColorNMatches()

Dim oExcel As Object
Dim oWB As Excel.Workbook
Dim oWS As Excel.Worksheet
Dim I As Integer
Dim Path As String

Path = "d:\test\CJWC_NMatch.xls"

Set oExcel = CreateObject(Excel.Application)
Set oWB = oExcel.Workbooks.Open(Path)
Set oWS = oWB.Worksheets("NMatch1")

For I = 2 To oWS.Range("V65536").End(xlUp).Row

Select Case oWS.Range("V" & I).Value

Case "1"

oWS.Rows(I).Interior.Color = vbRed

Case "2"

oWS.Rows(I).Interior.Color = vbBlue

Case Else

oWS.Rows(I).Interior.Color = vbYellow

End Select

Next I


End Sub

This is what I have now, but it isn't working. Any ideas? The column I'm basing this off of is V.

 

[chergh]

Try removing the quotes around the numbers i.e.:

Case 1

oWS.Rows(I).Interior.Color = vbRed

 

[Jcase]

Thanks for the suggestion chergh, unfortunately that doesn't seem to be the problem now. I made the sub() standalone to test it out and am getting an 'Active X Component can't create object' error.

It points to this line:

Set oExcel = CreateObject(Excel.Application)

Any thoughts guys?

I checked the suggestions of the Microsoft page, [registering it with rgsrv32, checking the references, etc etc] and that seems to be in order. I have the Microsoft Excel 11.0 Object library referenced.

 

[Jcase]

RESOLVED!

I removed the previous line "oExcel = CreateObject(Excel.Application) and changed the following line:

Dim oExcel as Object

To:

Dim oExcel As New Excel.Application

And now it works fine! I'm gonna take a stab in the dark... I didn't have to create an Excel Object since it was already referenced? So I just had to create a new instance of that object?

 

[chergh]

You really shouldn't use

Dim oExcel As New Excel.Application

It may not cause you any issues in this particular instance but it's a bad habit to get into.

Instead use

Dim oExcel as excel.application

and then

set oExcel = new excel.application

Firstly if you dim as a new excel.application then each time the code encounters oExcel it checks to see if oExcel is nothing.

Secondly you can't check if the object is nothing e.g. if you wrote:

If Not oExcel is nothing then

When the code encountered this line then it would cause a new excel application to be created and therefore it would never return that oExcel is nothing.

As I said not a problem in this instance but a bad habit.

 

[Jcase]

Duly noted!

Thank you very much chergh~!