I am trying to call a VBA function from VBS script:
VBA
Function f_split_master_file(output_folder_path As String, master_excel_file_path As String) As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error GoTo ErrorHandler
Dim wb As Workbook
Dim output As String
' Variables related with the master excel file
Dim wb_master As Workbook
Dim ws_master As Worksheet
Dim master_range As Range
Dim responsible_names_range As Range
Dim responsible_name As Range
Dim last_row_master As Integer
' Variables related with the responsible name excel
Dim savepath As String
Dim wb_name As Workbook
Dim ws_name As Worksheet
Dim name As Variant
' Check whether master file exists
If Len(Dir(master_excel_file_path)) = 0 Then ' Master file does not exist
Err.Raise vbObjectError 513, "Sheet1::f_split_master_file()", "Incorrect Master file path, file does not exist!"
End If
' Check whether output folder exists
If Dir(output_folder_path, vbDirectory) = "" Then ' Output folder path does not exist
Err.Raise vbObjectError 513, "Sheet1::f_split_master_file()", "Incorrect output folder path, directory does not exist!"
End If
Set wb_master = Workbooks.Open(master_excel_file_path)
Set ws_master = wb_master.Sheets(1)
last_row_master = ws_master.Cells(Rows.Count, "AC").End(xlUp).row
Set master_range = ws_master.Range("A1:AD" & last_row_master)
Set responsible_names_range = ws_master.Range("AC2:AC" & last_row_master) ' Get all names
data = get_unique_responsibles(responsible_names_range) 'Call function to get an array containing distict names (column AC)
For Each name In data
'Create wb with name
savepath = output_folder_path & "\" & name & ".xlsx"
Workbooks.Add
ActiveWorkbook.SaveAs savepath
Set wb_name = ActiveWorkbook
Set ws_name = wb_name.Sheets(1)
master_range.AutoFilter 29, Criteria1:=name, Operator:=xlFilterValues
master_range.SpecialCells(xlCellTypeVisible).Copy
ws_name.Range("A1").PasteSpecial Paste:=xlPasteAll
wb_name.Close SaveChanges:=True
' Remove filters and save workbook
Application.CutCopyMode = False
ws_master.AutoFilterMode = False
Next name
CleanUp:
' Close all wb and enable screen updates and alerts
For Each wb In Workbooks
If wb.name <> ThisWorkbook.name Then
wb.Close
End If
Next wb
Application.ScreenUpdating = True
Application.DisplayAlerts = True
f_split_master_file = output ' empty string if successful execution
Exit Function
ErrorHandler:
' TODO: Log to file
' Err object is reset when it exits from here IMPORTANT!
output = Err.Description
Resume CleanUp
End Function
VBS
Set excelOBJ = CreateObject("Excel.Application")
Set workbookOBJ = excelOBJ.Workbooks.Open("C:\Users\aagir\Desktop\BUDGET_AND_FORECAST\Macro_DoNotDeleteMe_ANDONI.xlsm")
returnValue = excelOBJ.Run("sheet1.f_split_master_file","C:\Users\aagir\Desktop\NON-EXISTENT-DIRECTORY","C:\Users\aagir\Desktop\MasterReport_29092022.xlsx")
workbookOBJ.Close
excelOBJ.Quit
msgbox returnValue
The macro (VBA function) works fine. The only thing that I am missing is within the VBS script. When I call the vba function from vbs script it runs fine but I cannot get the return value in the "returnValue" variable defined in VBS (it does not show anything). Can anyone tell what I am doing wrong? Thanks!
CodePudding user response:
Based on the name sheet1
(in your VBS script), I'm assuming the f_split_master_file
Function is in a Worksheet module. Move it to a standard Module and change sheet1
to (eg) Module1
and then try again.