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

Question

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!

ABNLookup.zip

flying008 · Answer

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.

rparwal · Answer

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 · Answer

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