MS Access 2013 late binding to Excel, compare columns

[Knicks]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Knicks]

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

 

[SkipVought]

So your code never finishes the [tt]For...Next i [/tt]loop?

Put a [tt]Save[/tt] in your loop, maybe every 10 or 100 rows.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

Try for testing:

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 [tt]objexcel_app.Visible = True[/tt]).
BTW, your formula for last row is OK for any address of used range, however this may be easier to resolve: [tt]Z = xlsExcel_sheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row[/tt] (replace xlCellTypeLastCell = 11 for late binding).

combo

 

[SkipVought]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Knicks]

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.