CODEWITHSUNDEEP
excelmacrostaskmanagervba
D.Schiermeyer
D.Schiermeyer

Reputation: 19

Open Excel File with Task Manager, Run Macro, Save file, and close

I am trying to get an excel file to open up every morning at 5am, run a macro which pulls in data and filers/organizes the data, save the file and then close it. I currently have the task manager with the .vbs script to open the file which is below, and the file opens, but as it closes the Save window pops up, and tries to save the file as a copy of the original.

Option Explicit

Dim xlApp, xlBook

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

 ' Import Add-Ins
'xlApp.Workbooks.Open "G:\CNC\Schiermeyer\Active Job Files\Autorefresh" "C:\<pathOfXlaFile>\MyMacro.xla"
'xlApp.AddIns("MyMacro").Installed = True

'Open Excel workbook
Set xlBook = xlApp.Workbooks.Open("G:\CNC\Schiermeyer\Active Job Files\Autorefresh\AutoRefresh Active Job Report.xlsm", 0, True)

' Run Macro
xlApp.Run "sheet1.ActiveJobReportRefresh"

xlBook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing

WScript.Quit

Any help would be greatly appreciated. I am new to using Task manager & .vbs files, and still not a pro with VBA, this is just as far as I have gotten using forums.

Upvotes: 0

Views: 2353

Answers (2)

Oliver Leung
Oliver Leung

Reputation: 802

If you want to save to the same workbook, you need to open the workbook as "write-able". i.e.:

Set xlBook = xlApp.Workbooks.Open("G:\CNC\Schiermeyer\Active Job Files\Autorefresh\AutoRefresh Active Job Report.xlsm", 0, False)

Upvotes: 0

MG78
MG78

Reputation: 219

I created a module and inserted the below code:

Sub Auto_Close()

If ThisWorkbook.Saved = False Then
    ThisWorkbook.Save
End If

End Sub

But this module is created where when the user click the close (X) on top right, excel will save and close by not asking confirmation. Perhaps you can try to modify this code. Thank you.

Upvotes: 1

Related Questions