Alteryx Server Discussions

Find answers, ask questions, and share expertise about Alteryx Server.

Run Command Tool Not working in Alteryx Gallery

Megha_Idicula
5 - Atom

Hi,

 

I've been working on a workflow to refresh all my SQL Server data connections in my macro enabled workbook, save it with today's date and email it to a set of people. I've setup a bat file to execute the vba script to do the same and it works well in my designer desktop. However when I upload it to Alteryx gallery, it runs successfully but does not produce any output. What can I do to make this work on my gallery? I'm using a shared drive.

 

This is my vba script:

Option Explicit

' -----------
dim workbook_path
workbook_path = "\\Network Location\Daily Stats\Daily Stats.xlsm"
' Place your workbook file here

dim macro_name
macro_name = "RefreshDataConnections"
' Place your macro name here

' -----------

dim file_system
dim full_workbook_path
set file_system = CreateObject("Scripting.FileSystemObject")
full_workbook_path = file_system.GetAbsolutePathName(workbook_path)
' File address housekeeping

Dim ExcelProgram
Set ExcelProgram = CreateObject("Excel.Application")
' Tell the script what Excel is

ExcelProgram.Application.WorkBooks.Open full_workbook_path
' Open your workbook

ExcelProgram.Application.Visible = False
' Open it in the background

ExcelProgram.Application.Run "'"&full_workbook_path&"'!"&macro_name
' Run your Macro - this tells the Excel running in the background to find this workbook and macro.

ExcelProgram.Application.displayalerts = False
' Do not show prompts since we want this to be automated - Could switch to True to get prompts

' Get today's date in the format YYYY-MM-DD
Dim today
today = Date
Dim formattedDate
formattedDate = Year(today) & "-" & Right("00" & Month(today), 2) & "-" & Right("00" & Day(today), 2)

' Define the new file name with today's date as a regular Excel file (.xlsx)
Dim new_file_path
new_file_path = file_system.GetParentFolderName(full_workbook_path) & "\" & "Daily Stats " & formattedDate & ".xlsx"

' ----------- Save Workbook and Log Result -----------
On Error Resume Next ' Start error handling to catch any issues during file saving
ExcelProgram.Activeworkbook.SaveAs new_file_path, 51 ' Save as .xlsx format

' ----------- Log Setup -----------
dim log_file_path
log_file_path = "\\Network Location\Daily Stats\log.txt" ' Specify your log file path
dim fso, log_file
Set fso = CreateObject("Scripting.FileSystemObject")

' ----------- Delete the existing log file if it exists -----------
If fso.FileExists(log_file_path) Then
fso.DeleteFile log_file_path
End If

' Create or open log file (this will overwrite the log file each time the script runs)
Set log_file = fso.CreateTextFile(log_file_path, True) ' True means overwrite the file

' Check if the file was saved
If Err.Number = 0 Then
log_file.WriteLine "SUCCESS " & Now
Else
log_file.WriteLine "ERROR: " & Err.Description
End If

ExcelProgram.Activeworkbook.Close

' ----------- Cleanup -----------
Set ExcelProgram = Nothing ' Release Excel application object
log_file.Close ' Close the log file
Set log_file = Nothing
Set fso = Nothing

This is my bat file

 

@echo off

cscript //nologo "\\network location\Daily Stats\RefreshData.vbs"

1 REPLY 1
jrgo
14 - Magnetar

This wont work because your VBA script requires an interactive Windows user session to launch/execute, which is why it works when you run locally on your own desktop Designer. Jobs executed on Server run as a service/batch job (non-interactive). Additionally, it's likely that your Alteryx Server does not have MS Excel installed to even execute the script, but again... even if it does, it wouldn't work.

 

If all you're trying to do is save a copy of the output with the date before you email, you can do that all natively in the workflow without VBA. Use a Formula tool before the Output tool to create a new field that'll have the full output file path that concats the date. Then in the output tool, enable the "Take File/Table Name From Field" option. This is a simplified explanation on how to do it and if you're unfamiliar with the options, look at the Output Tool help documentation for detail, or search community, which I'm betting you'll find posts on this also.

 

Hope this helps.

 

Jimmy