Workbook_open - Sub problem
 

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

Workbook_open - Sub problem
 

Try this

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


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"ina" wrote in message
oups.com...
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

Workbook_open - Sub problem
 

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

Workbook_open - Sub problem
 

I found this page helpful for the type of thing you want to do.
http://www.thecodenet.com/articles.php?id=10
Hope this works for what you want to do.
"ina" wrote:

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

Workbook_open - Sub problem
 

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)

"ina" wrote in message
ups.com...
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

Workbook_open - Sub problem
 

Hello thanks a lot,

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

Ina

Workbook_open - Sub problem
 

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.

--
Regards,
Tom Ogilvy


"ina" wrote:

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

Workbook_open - Sub problem
 

But my variable oExec coming from my function Set oExec =
OpenPHObject()

can I do it?

Ina

Workbook_open - Sub problem
 

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.

--
Regards,
Tom Ogilvy


"ina" wrote:

Hello thanks a lot,

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

Ina

Workbook_open - Sub problem
 

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

--
Regards,
Tom Ogilvy


"ina" wrote:

But my variable oExec coming from my function Set oExec =
OpenPHObject()

can I do it?

Ina

Workbook_open - Sub problem
 

Thank you very much Tom
:)

Workbook_open - Sub problem
 

yes thanks I have an error as the Object variable is not set.


Tom Ogilvy wrote:
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

--
Regards,
Tom Ogilvy


"ina" wrote:

But my variable oExec coming from my function Set oExec =
OpenPHObject()

can I do it?

Ina

Workbook_open - Sub problem
 

I didn't change anything that had to do with that. You set it in your code:

Set oExec = OpenPHObject()


--
Regards,
Tom Ogilvy


"ina" wrote in message
oups.com...
yes thanks I have an error as the Object variable is not set.


Tom Ogilvy wrote:
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

--
Regards,
Tom Ogilvy


"ina" wrote:

But my variable oExec coming from my function Set oExec =
OpenPHObject()

can I do it?

Ina

Workbook_open - Sub problem
 

It is ok now thanks :)

Workbook_open - Sub problem
 

It is ok now thanks :)