Excel Macro Automate in Alteryx- VBA script method is not working

[rparwal]

Hi All

I want to run this excel macro in Alteryx but the normal VBA script is not working on it. To start the macro in Excel we first need to click on 'Start Lookup' and then a new pop up display comes up and we need to select 'Lookup ABN Details' and then the excel macro fetches data from server. Is there any way in which i can automate this Excel macro in Alteryx? 

I have attached the Excel Macro. 

Thanks in advance!

[flying008]

Hi, @rparwal 

1- At the first, you need know what is the action name of your button ''Lookup ABN Details''  in vba code.

2- use event or Run Command tool to run powershell script with start the action.

3- below code for your information, but in the line 2th and 4th, you must change the path and button name as yourself.

$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open("C:\path\as your\ABNLookup.xlsm")
$excel.Application.AutomationSecurity = 1
$excel.application.Run("Command_Lookup ABN Details_Click")  # if it is public.
$workbook.Close(SaveChanges=1)
$excel.Quit()
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
Remove-Variable excel

[rparwal]

Hi @flying008

Thank you for your response. I am not very comfortable with VBA as i am new and learning. It would be helpful if you could share your sample Alteryx with me as it would help me in the learning process.

Thank you!

[flying008]

Hi, @rparwal 

That looks your button call is a private sub process, so maybe can not run it with powershell . perhaps you need find other way.