Running a macro from a command line

[Guest]

Hi all, can anyone tell me if you can referance a xls workbook and run the
macro within form a command line?

I am now using an on Open event so it auto runs when it is open but i was
wondering if I could run it by referencing it?

Kelly

 

[Gord Dibben]

Kelly

You cannot run a macro from the command line.

What you're doing with the Auto_Open or Workbook_Open is it.

Gord Dibben Excel MVP

 

[Guest]

Thanks Gord, Can you suggest a method to open and edit the macro without it
auto-running?

Here's the situation. It is fairly quick code so it is really tough hit
escape quick enough to stop it form running. Also It is digitaly signed so it
never prompts me ands asks if I want to run the macro. this is true even if I
set the security setting sto high.

The only way I have found to open the workbook and edit the macro is to open
excel and remove the athorization of the the digital certificate THEN sdet
security settings to med or high.

There has to be a better way? I read somewhere if you hold the shift button
down while you open the wkbk it will not run the code but this does not work
for me??

Any ideas?

Thanks again,

Kelly

 

[Guest]

Here is some code that I use. It checks for the existens of differeent files
on my system (essentially flags that tell the macro haw to execute)

Option Explicit
'***********************************************************************************************
'Jim Thomlinson
'June 3, 2004
'modFPA module
'This module changes the functionality of the spreadsheet based on whether
the user is in the
'FPA group or not.
'***********************************************************************************************

'Declare Module Level Constants
Private Const m_cModule As String = "modFPA" 'Used by
ErrorHandler
Private Const m_cFPADirectory As String = "C:\FPA\" 'Directory of
FPA file
Private Const m_cFPAFile As String = "FPA.RUN" 'Name of file to
indicate FPA Group
Private Const m_cAutoRunFile As String = "AUTORUN.RUN" 'Name of file to
indicate AutoRun

'***********************************************************************************************
'Name: auto_open
'Inputs: None
'Outputs: Defines the parameters under which the spreadsheet will run.
'Side Effects: None
'Calls: None
'Description: Based on whether the procedure fines the file indicated by
m_cFPADirectory and
' m_cFPAFile, it initializes the spreadsheet to work
approprite for the user.
'***********************************************************************************************
Public Sub auto_open()
On Error GoTo ErrorHandler

Call AlwaysExecute

'Determine if the FPA file exists on the system
If UCase(Dir(m_cFPADirectory & m_cFPAFile)) = m_cFPAFile Then
'The file exists so initialize for FPA user
Call InitializeApplicationFPA
ElseIf UCase(Dir(m_cFPADirectory & m_cAutoRunFile)) = m_cAutoRunFile Then
'The file exists so initialize for AutoRun
Call InitializeApplicationAutoRun
Else
'The file does not exist so initialize for Non FPA user
Call InitializeApplicationNonFPA
End If

Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "auto_open"
End Sub

'***********************************************************************************************
'Name: InitializeApplicationFPA
'Inputs: None
'Outputs:
'Side Effects: None
'Calls: None
'Description:
'***********************************************************************************************
Private Sub InitializeApplicationFPA()
On Error GoTo ErrorHandler
'Add Code here
Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "InitializeApplicationFPA"
End Sub

'***********************************************************************************************
'Name: InitializeApplicationAutoRun
'Inputs: None
'Outputs:
'Side Effects: None
'Calls: None
'Description:
'***********************************************************************************************
Private Sub InitializeApplicationAutoRun()
On Error GoTo ErrorHandler
'Add Code Here
'Application.ActiveWorkbook.Close SaveChanges:=True
Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "InitializeApplicationAutoRun"
End Sub

'***********************************************************************************************
'Name: InitializeApplicationNonFPA
'Inputs: None
'Outputs:
'Side Effects: None
'Calls: None
'Description:
'***********************************************************************************************
Private Sub InitializeApplicationNonFPA()
On Error GoTo ErrorHandler
'Add Code Here
Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "InitializeApplicationNonFPA"
End Sub

'***********************************************************************************************
'Name: AlwaysExecute
'Inputs: None
'Outputs:
'Side Effects: None
'Calls: None
'Description:
'***********************************************************************************************
Private Sub AlwaysExecute()
On Error GoTo ErrorHandler
'Add Code here
Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "AlwaysExecute"
End Sub

'***********************************************************************************************
'Name: auto_close
'Inputs: None
'Outputs: Defines the parameters under which the spreadsheet will exit.
'Side Effects: None
'Calls: None
'Description: Based on whether the procedure fines the file indicated by
m_cFPADirectory and
' m_cFPAFile, it ends the spreadsheet to work approprite for
the user.
'***********************************************************************************************
Public Sub auto_close()
On Error GoTo ErrorHandler

modLogFiles.LogOut
'Determine if the FPA file exists on the system
If UCase(Dir(m_cFPADirectory & m_cFPAFile)) = m_cFPAFile Then
'The file exists so End for FPA user
Call EndApplicationFPA
ElseIf UCase(Dir(m_cFPADirectory & m_cAutoRunFile)) = m_cAutoRunFile Then
'The file exists so End for AutoRun
Call EndApplicationAutoRun
Else
'The file does not exist so end for Non FPA user
Call EndApplicationNonFPA
End If

Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "auto_close"
End Sub


'***********************************************************************************************
'Name: EndApplicationFPA
'Inputs: None
'Outputs:
'Side Effects: None
'Calls: None
'Description:
'***********************************************************************************************
Private Sub EndApplicationFPA()
On Error GoTo ErrorHandler
'Add Code here
Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "EndApplicationFPA"
End Sub

'***********************************************************************************************
'Name: EndApplicationAutoRun
'Inputs: None
'Outputs:
'Side Effects: None
'Calls: None
'Description:
'***********************************************************************************************
Private Sub EndApplicationAutoRun()
On Error GoTo ErrorHandler
'Add Code Here
'Close and Save the workbook
Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "EndApplicationAutoRun"
End Sub

'***********************************************************************************************
'Name: EndApplicationNonFPA
'Inputs: None
'Outputs:
'Side Effects: None
'Calls: None
'Description:
'***********************************************************************************************
Private Sub EndApplicationNonFPA()
On Error GoTo ErrorHandler
'Add Code Here
Exit Sub

ErrorHandler:
modErrors.HandleError m_cModule, "EndApplicationNonFPA"
End Sub


This is not quite plug and play becuase of the error handling code that you
will want to strip out. If the FPA.run (empty text file) exists on the
machine then it executes one set of instructions. If AutoRun.run exists if
does a different set of instructions (Opens, updates itself and then shuts
itself back down). If none of the files exist then only the always execute
runs. I use this a lotmodule a lot.

 

[Gord Dibben]

Kelly

I am surprised to hear the SHIFT key is not working for you.

Open another workbook or start Excel with no workbook and copy this to the
Immediate Window of VBE

Application.EnableEvents = False

Open the other workbook and the workbook_open code won't run.

Do your editing and save/close.

In VBE Immediate Window enter

Application.EnableEvents = True

Gord Dibben Excel MVP

 

[Guest]

Much Thanks!

Kelly

I am surprised to hear the SHIFT key is not working for you.

Open another workbook or start Excel with no workbook and copy this to the
Immediate Window of VBE

Application.EnableEvents = False

Open the other workbook and the workbook_open code won't run.

Do your editing and save/close.

In VBE Immediate Window enter

Application.EnableEvents = True

Gord Dibben Excel MVP