Software

Ask something
Question Locked

Run VB macro non-interactively

0 helpful
6 replies

by bcardenuto

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I need to run a VB macro from a scheduled task. Before I added the automatic Pivot table to the module it used to run without problems in the middle of the night.
The VB code works successfully when I execute the macro manually. When launching it from the bat file the worksheet with VB only opens but does not kick off the macro.

When I comment out the code I added, I still get an open worksheet and the macro will not run unless I run it manually.

Any ideas would be welcome. Thanks.

6 replies

by qus

Did you automatically run the macro when the workbook opened?
I usually call procedures I want to run everytime the workbook opened in "This Workbook" object

Private Sub Workbook_Open()
'Call procedures we want to run
End Sub

Or is there another problem?
Haris.

by bcardenuto

I had the following in the "This Workbook" location:
Option Explicit

Private Sub Workbook_Open()

If (ThisWorkbook.Path & "\" = gstrPATH) Then
gstrOpMode = "Prod"
Call RetainPrjctsPerSheet
Else
gstrOpMode = "Devel"
End If
End Sub

It wouldn't run automatically though. This new clone was created in MSOffice 2007. Nothing was changed in the "This workbook" location from the original document.

I stripped out the if/else lines and left the call statement and now it runs automatically. Excellent! Thanks for your help.

by bcardenuto

The excel file containing the VB macro runs automatically and closes automatically as desired. I would like to edit the file without running the VB. How can I do that before it automatically closes on me? Is there a way to open the file without running VB?
Thanks

by qus

I don't know exactly what you want. We can stop macros currently running by pressing Ctrl+Break (or pulldown menu Run, Break). If we don't want the macros to run at all, we can disable at opening the file (if macro security is set to medium). I suppose you knew it.

by bcardenuto

I'm very new to VB and didn't know about this method for stopping the macro. Thanks very much. This solves the problem. Thanks.

This discussion is closed to new replies.