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&"'!"¯o_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"
