I always want to run the same macro on each sheet of a newly created workbook (workbook has always the same structure).
For this, I thought it would make sense to use the “Invoke VBA” activity, beauce the workbook is newly created.
However, the macro does not work via the “Invoke VBA” activity because nothing happens in the workbook.
So I thought maybe the macro has to be run additionally after the “Invoke VBA” activity but the macro is not found via the “Execute Macro” activity (although all excel settings are activated and I tried to save the macro for all workbooks available).
Here is a screenshot of the loop - tried to combinate the invoke VBA and execute Macro activities - and the .txt file with the VB code.
CompareTwoColumns.txt (907 Bytes)
Btw, the macro (called “CompareTwoColumns”) works running it manually in Excel.
Can anybody help me how to solve this issue so that it works?
it should work with invoke vba alone,
did you tick save cahnges?
Thank you for your response @jack.chan
I tried it with invoke vba alone and I tick save changes in excel application scope. However, nothing happens in the excel - the differences should be highlighted yellow.
Possible that the properties in invoke vba are false?
Or the variable type of “CompFile”? But I assume that it must be type of Object due to invoke vba.
can you upload the xaml and the excel here?
its not working because
- you are not looping over your excel sheets, it should be like this
- you didnt pass sheet Name into VBA function, it was only checking 1 sheet the whole time
replace your excel application scope with this
test22.xaml (9.3 KB)
update VBA as follows
Function CompareTwoColumns(sheetName as string)
Dim col1 As Range, col2 As Range, prod1 As String, prod2 As String
Set col1 = Columns("A")
Set col2 = Columns("B")
lr = Columns("A:B").SpecialCells(xlCellTypeLastCell).Row
For r = 2 To lr
prod1 = Cells(r, col1.Column).Value
prod2 = Cells(r, col2.Column).Value
'if prod1 is in col2
If prod1 <> "" Then
Set iscol2 = col2.Find(prod1)
If iscol2 Is Nothing Then
Cells(r, col1.Column).Interior.Color = vbYellow
'if prod2 is in col1
If prod2 <> "" Then
Set iscol1 = col1.Find(prod2)
If iscol1 Is Nothing Then
Cells(r, col2.Column).Interior.Color = vbYellow
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.