Unable to save excel file using VBScript

I'm aware that there are similar situations as mentioned in the title. However, I've spent roughly 30 hours trying to apply as many solutions that were presented by google.

To give some context of my goal, I'm using Java to read an excel file and change certain fields, which then a macro must be executed and the results must be compared to a file to see verify if the calculator is working correctly.

In order to execute this macro through code, I've created a VB script that executes the macro, but the problem is that it keeps complaining that it cannot save the file and that it'd have to save to a temporary excel file such as "8823V828.xlsm". This is a problem because this process has to be automated and the program wouldn't even be able to determine the name of the temp file to verify the results.

If you have a better solution, I'd gladly appreciate the insight.

This is what I've tried so far:

1. Creating a VB Script with the many combinations online (I've tried DisplayAlerts = False, Visible = Flase)

   Set xlApp = CreateObject("Excel.Application")  Set Wb = xlApp.Workbooks.Open("C:/Users/339743866/Projects/cob_penguin_test/src/main/resources/outputrtc_calc.xlsm")  xlApp.Run "CalculatePayments"  Wb.Save  Wb.Close  xlApp.Application.Quit  

2. Creating a save macro that supposedly does not prompt and save the excel file.

   Sub AutoCloseAndSave()    If ThisWorkbook.Saved = False Then      ThisWorkbook.Save    End If  End Sub  

   The VBScript would be

   Set xlApp = CreateObject("Excel.Application")  Set Wb = xlApp.Workbooks.Open("C:/Users/339743866/Projects/cob_penguin_test/src/main/resources/outputrtc_calc.xlsm")  xlApp.Run "CalculatePayments"  xlApp.Run "AutoCloseAndSave()  xlApp.Application.Quit  

3. I've enabled the sharing wizard and indexing on the folder

4. I've checked the permissions of the folder and file to see that full permissions have been granted

https://stackoverflow.com/questions/66631357/unable-to-save-excel-file-using-vbscript March 15, 2021 at 09:42AM