×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

MS Access 2013 late binding to Excel, compare columns

MS Access 2013 late binding to Excel, compare columns

MS Access 2013 late binding to Excel, compare columns

(OP)
I have been using MS Access VBA to create the Excel object and perform data analysis from within Access. I have been able to do this successfully when looking at a value from a row & column. I am now attempting to compare a data point in a column to another data point in another column, both using the same row. I am trying to figure out the types of loops to use and whether to use Worksheet or Range. This example I am attempting to use the Worksheet, but it is failing

In this particular situation I am trying to compare Column (E) and Column (K) and using the same Row (starting at row 5 and ending at used rows), so if Column E = "RECOCOM" and
Column (K) = Null, then simply put a jagged RED border around the cell in Column (K). Any pointers would be appreciated!!


Dim objexcel_app As Object
Dim xlsExcel_wkbook As Object
Dim xlsExcel_sheet As Object
Dim xlsExcel_range As Object
Dim Z As Long
Dim i As Integer


Set objexcel_app = CreateObject("Excel.Application")
objexcel_app.Visible = False

Set xlsExcel_wkbook = objexcel_app.Workbooks.Open("C:\Deal\New_NMTC_TLR_Note.xls")
Set xlsExcel_sheet = xlsExcel_wkbook.sheets("New_NMTC_TLR_Note")


Z = xlsExcel_sheet.UsedRange.rows(xlsExcel_sheet.UsedRange.rows.Count).row



For i = 5 To Z


If xlsExcel_sheet.cells(i, 5).Value = "RECOCOM" And xlsExcel_sheet.cells(i, 11).Value = vbNullString Then

xlsExcel_sheet.cells(i, 11).Borders(9).LineStyle = 4
xlsExcel_sheet.cells(i, 11).Borders(8).LineStyle = 4
xlsExcel_sheet.cells(i, 11).Borders(7).LineStyle = 4
xlsExcel_sheet.cells(i, 11).Borders(10).LineStyle = 4
xlsExcel_sheet.cells(i, 11).Borders(9).Color = RGB(255, 0, 0)
xlsExcel_sheet.cells(i, 11).Borders(8).Color = RGB(255, 0, 0)
xlsExcel_sheet.cells(i, 11).Borders(7).Color = RGB(255, 0, 0)
xlsExcel_sheet.cells(i, 11).Borders(10).Color = RGB(255, 0, 0)


Else
End If


Next i

RE: MS Access 2013 late binding to Excel, compare columns

Hi,

Your Z assignment assumes that there is data in row 1 of the sheet in question.

Is that true?

Otherwise, exactly how is your code “failing?”

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: MS Access 2013 late binding to Excel, compare columns

(OP)
The XLS file is a template from the Gov't and it always has 4 rows of header information, so the actual data starts on Row 5. Its always at least 1 row of actual data starting at row 5. I am not exactly sure where the failure is, I may need to remove the On Error that is more deeply embedded in the over all larger chunk of code - but it is keeping Excel open in the background so that you have to do a CTRL-ALT-Delete and kill the Excel process after it did not performing expected column header colors and creating a an additional CSV file. I didn't include other portions of code that were working fine before, it was only the introduction of this new portion of code that is making it hang up

RE: MS Access 2013 late binding to Excel, compare columns

So your code never finishes the For...Next i loop?

Put a Save in your loop, maybe every 10 or 100 rows.


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: MS Access 2013 late binding to Excel, compare columns

Try for testing:

CODE -->

For i = 5 To Z

If xlsExcel_sheet.cells(i, 5).Value = "RECOCOM" And xlsExcel_sheet.cells(i, 11).Value = vbNullString Then Msgbox "Match in row " & i

xlsExcel_sheet.cells(i, 11).Borders(9).LineStyle = 4
xlsExcel_sheet.cells(i, 11).Borders(8).LineStyle = 4
xlsExcel_sheet.cells(i, 11).Borders(7).LineStyle = 4
xlsExcel_sheet.cells(i, 11).Borders(10).LineStyle = 4
xlsExcel_sheet.cells(i, 11).Borders(9).Color = RGB(255, 0, 0)
xlsExcel_sheet.cells(i, 11).Borders(8).Color = RGB(255, 0, 0)
xlsExcel_sheet.cells(i, 11).Borders(7).Color = RGB(255, 0, 0)
xlsExcel_sheet.cells(i, 11).Borders(10).Color = RGB(255, 0, 0)

Next i 
You should get all cells in col. K of tested range with borders formatted and messages when data in row i match.

Also, at least for testing, don't hide excel ( set with objexcel_app.Visible = True).
BTW, your formula for last row is OK for any address of used range, however this may be easier to resolve: Z = xlsExcel_sheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row (replace xlCellTypeLastCell = 11 for late binding).

combo

RE: MS Access 2013 late binding to Excel, compare columns

Why not code a Conditional Format for the entire data range, rather than a loop?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: MS Access 2013 late binding to Excel, compare columns

(OP)
My error, oddly some earlier code that was not causing any trouble was not coded correctly and only became a problem with this new piece of code. I fixed the earlier section and it is now working. Thank you all for helping and clearing the code that I presented was helpful. I am not using Conditional Formatting because I assumed I would probably end up with criteria that was too complex for it (at least that has been my experience in Access), plus because these are XLS made from the Gov't CSV file I am trying to keep that side of the file simple just in case they change the CSV file I won't have to recode in both Access and Excel, just keep it on the Access side. Being able to do these type of comparisons will be super helpful moving forward.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close