Excel code in VBA

[gundecharahul]

Hi,

I have written following VBA code in excel which i need to write in access.

Sub Macro3()
i = 2
Do While (Cells(i, 1) <> "")
If (Cells(i, 1) = "CONU_TEMP.xls" And Cells(i + 1, 1) = "CONU_EIM.xls") Then
Range("b" & i).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=B" & i & "<>B" & i + 1 & ""
Selection.FormatConditions(1).Interior.ColorIndex = 3
Range("b" & i).Select
Selection.AutoFill Destination:=Range(Selection, Selection.End(xlToRight)), Type:=xlFillFormats

Range("b" & i + 1).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=B" & i & "<>B" & i + 1 & ""
Selection.FormatConditions(1).Interior.ColorIndex = 3
Range("b" & i).Select
Selection.AutoFill Destination:=Range(Selection, Selection.End(xlToRight)), Type:=xlFillFormats

i = i + 1

Else: i = i + 1

End If
Loop

End Sub



I tried lot but was not able to translate the code of excel into Access.
I would really appreciate if someone could help me in this.

Thanks in advance..

Rahul

 

[boblarson]

You might get better results if you specify what your code is actually trying to accomplish and what you are going to try to use Access for as a replacement. Access is a relational database and Excel is not, so the similarities are not all that many, except that each has data. But, the way you store and use data in Access is not the same as using it on a spreadsheet.

 

[gundecharahul]

Bob, Thanks a lot for your reply.

I am actually exporting data to excel from access and then comparing the difference between rows, if there exists any difference in cell values then i want to color those cells.
For this i am using conditional formatting through VBA and i want to do this using Access.

In the above code i am getting error for .selection statement if i translate it to access vba.

 

[boblarson]

For any items you need to use the Excel objects created in the Access VBA. So, for Selection you would use something like this if you had opened the Excel App by using objXL then it would be

objXL.Selection

 

[dkinley]

Bob ... If I may jump in and ask a question for my own edification and for gundecharahul should they run into further down the line

I have exports set up to used preformated Excel spreadsheets. The code copies the template from template directory, renames and pastes in another directory, and then proceeds to write in the rows and columns. After the routine is complete, it calls to present the file to the user.

The issue is that when I call and it opens up the file, it sometimes opens and is visible in front of the Access program. Other times it opens, but is 'sorta not visible' (clicking on the taskbar does not move it to the front).

'Sorta not visible means' that the program bar (whatever the blue bar at the top of the Excel window is called) is displayed, and so are the borders of Excel (and it is in front of Access) but the content is not visible.

I can click the 'x' in the upper right hand corner and it will respond with normal Excel dialogue information. Again, this happens only sometimes. Also, Access 2007.

Not sure if gundecharahul has/will run into this or not.

Ideas?
-dK