Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_open - Sub problem
Hello thanks a lot,
Bob I did not understand what you mean as object will not be in scope :) Ina |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_open - Sub problem
But my variable oExec coming from my function Set oExec =
OpenPHObject() can I do it? Ina |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_open - Sub problem
Thank you very much Tom
:) |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_open - Sub problem
It is ok now thanks :)
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_open - Sub problem
It is ok now thanks :)
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with Workbook_open | Excel Discussion (Misc queries) | |||
Workbook_Open Problem ... Worksheets not loaded yet!? | Excel Programming | |||
Excel 97 Workbook_Open problem | Excel Programming | |||
Workbook_Open problem | Excel Programming | |||
problem with code in workbook_open event | Excel Programming |