Use Excel Macro in Excel Application Scope

Help Activities
, , ,
1

Hi all,

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.

image
image1319×814 31.3 KB

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?

2

it should work with invoke vba alone,

did you tick save cahnges?
image

3

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.

image
image1323×878 35 KB

4

can you upload the xaml and the excel here?

5

Here the xaml- and excel-file:

Merging two excel files based on sheetnames.xaml (16.5 KB)

Result_2022_05_04.xlsx (11.2 KB)

6

its not working because

  1. you are not looping over your excel sheets, it should be like this
    image
  2. 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
        
    ActiveWorkbook.Sheets(sheetName).Activate
    
    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
            End If
        End If
        
        '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
            End If
        End If
    Next r
                    
        
End Function
1 Like
7

Amazing it works now! Thank you very much for your quick and so helpful answer!!! @jack.chan
You made my day :boom: :clap: :hugs:

1 Like
8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.