Workbook_open - Sub problem

  • Thread starter Thread starter ina
  • Start date Start date
I

ina

Hello guys I have a problem with workbook_open this workbook_open. When
Excel open this workbook needs to open an external program, execute
code, delete macro, save it, close external program, close excel

but I have problem because it is not recognize the save_Exit function


Public Sub Workbook_Open()
' ************
' Variables
' ************
Dim dtmTime As Date
Dim dtmSave As Date
Dim oExec As Object


' ************
' Call function OpenPHObject
' ************
Set oExec = OpenPHObject()

' ************
' Cursor in clock shape
' ************
Application.Cursor = xlWait
DoEvents

On Error Resume Next
' ************
' After seven seconds the macros has been launched
' ************
dtmTime = Now + TimeValue("00:00:07")
' ************
' Open the sub in thisworkbook
' ************
Application.OnTime dtmTime, "thisworkbook.operations"
Application.Cursor = xlDefault
Application.DisplayAlerts = False
'Sheets(Array("Read Me", "File Layout", "Index", "Quotes")).Copy
ActiveWorkbook.SaveAs Filename:= _
"C:\tet_1.xls", fileformat:= xlNormal, ReadOnlyRecommended:=False,
CreateBackup:=False

Application.DisplayAlerts = True
' ************
' Now + 7seconds + 30 seconds --> launch SAVEandEXIT and close PH
(external program)
' ************
dtmSave = dtmTime + TimeValue("00:00:30")

Application.OnTime dtmSave, "thisworkBook.Save_Exit(oExec)"
' I have a problem here
' because it is not recognize the Save_Exit Sub



End Sub



Public Sub Operations()

Sheet3.Activate
Sheet3.ExecGetInfo
Sheet4.Activate
Sheet4.ExecGetExtra

End Sub

Public Sub Save_Exit(oExec As Object)

Call ClosePHObject(oExec)
SaveAsWithoutCode 'delete all macro
Application.Quit
ThisWorkbook.Close SaveChanges:=True
End Sub
 
Try this

Application.OnTime dtmSave, "'" & ThisworkBook.name "'!Save_Exit oExec"


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
Doesn't work, if take out this argument it works fine but I need to
pass this argument in order to close the external program, how to do
it?

Ina
 
I think the problem is trying to pass an object parameter, I don't think
that will work as the object will not be in scope.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
Hello thanks a lot,

Bob I did not understand what you mean as object will not be in scope
:)

Ina
 
Make oexec a public variable at the top of the module. Alter your Save_Exit
routine to use that variable and not expect an argument.
 
I would suggest that scope is probably not what he meant. He probably meant
you can't pass an object as an argument in this way.
 
if all your code is in the Thisworkbook module:

Dim oExec As Object

Public Sub Workbook_Open()
' ************
' Variables
' ************
Dim dtmTime As Date
Dim dtmSave As Date



' ************
' Call function OpenPHObject
' ************
Set oExec = OpenPHObject()

' ************
' Cursor in clock shape
' ************
Application.Cursor = xlWait
DoEvents

On Error Resume Next
' ************
' After seven seconds the macros has been launched
' ************
dtmTime = Now + TimeValue("00:00:07")
' ************
' Open the sub in thisworkbook
' ************
Application.OnTime dtmTime, "thisworkbook.operations"
Application.Cursor = xlDefault
Application.DisplayAlerts = False
'Sheets(Array("Read Me", "File Layout", "Index", "Quotes")).Copy
ActiveWorkbook.SaveAs Filename:= _
"C:\tet_1.xls", fileformat:= xlNormal, ReadOnlyRecommended:=False,
CreateBackup:=False

Application.DisplayAlerts = True
' ************
' Now + 7seconds + 30 seconds --> launch SAVEandEXIT and close PH
(external program)
' ************
dtmSave = dtmTime + TimeValue("00:00:30")

Application.OnTime dtmSave, "thisworkBook.Save_Exit"
' I have a problem here
' because it is not recognize the Save_Exit Sub



End Sub



Public Sub Operations()

Sheet3.Activate
Sheet3.ExecGetInfo
Sheet4.Activate
Sheet4.ExecGetExtra

End Sub

Public Sub Save_Exit()

Call ClosePHObject(oExec)
SaveAsWithoutCode 'delete all macro
Application.Quit
ThisWorkbook.Close SaveChanges:=True
End Sub
 
I didn't change anything that had to do with that. You set it in your code:

Set oExec = OpenPHObject()
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code Execution has been interrupted - ?? 4
schedule macro - application.OnTime 2
execute macro; save it and close using Application.OnTime 2
Splash Screen Problem 1
Complex problem involving VBA in excel 0

Back
Top