Workbook_Open vs Auto_Open

[Bongard]

Hi, I am having issues with Excel staying open when I use the
Application.quit command in a macro. I previously had this code in the
Workbook_Open event but sometimes the excel.exe process would hang
open and lately the workbook itwself wasn't even closing. The macro
would complete but the window would just stay open on the desktop. I
tried moving the code to the auto_open event and that has seemed to
work temporarily (at least now excel closes when the application.quit
command is used.) This has been happening in a number of workbooks all
where I had code in the workbook_open event. I will paste some code
here and maybe someone could tell me what I'm doing wrong. I'm not
sure if it's something in my code that I'm doing wrong or if it's
something with the excel application itself that I need to adjust in
order for this command to work correctly. Thanks for your help!

Sub auto_open()

Application.DisplayAlerts = False

Dim MyHomePath As String
Dim LECTemplate As String
Dim i As Integer
Dim SecurityLevelData(1 To 5) As String

'The Portfolio Level Data
Dim Symbol(1 To 100) As String
Dim PTFL(1 To 100) As String
Dim SZ_BDR_1(1 To 100) As String
Dim SZ_CLS_1(1 To 100) As String
Dim LSCORE_1(1 To 100) As Double
Dim STLBDR_1(1 To 100) As String
Dim STLBDES1(1 To 100) As String
Dim STLCLAS(1 To 100) As String
Dim OBJ_SIZE(1 To 100) As String
Dim OBLSCR_1(1 To 100) As Double
Dim OBSTYLB1(1 To 100) As String
Dim OSTBDES1(1 To 100) As String
Dim OSTLCLS1(1 To 100) As String

'Security Level Data tabs and prt names
SecurityLevelData(1) = "LEC_Large_Value"
SecurityLevelData(2) = "LEC_Large_Growth"
SecurityLevelData(3) = "LEC_Mid_Core"
SecurityLevelData(4) = "LEC_Mid_Growth"
SecurityLevelData(5) = "LEC_Small_Core"

MyHomePath = ActiveWorkbook.Path
LECTemplate = MyHomePath & "\D_LEC_Template.xls"

Set fs = CreateObject("Scripting.FileSystemObject")
Set fs1 = fs.getfile(LECTemplate)
LastModifiedDate = fs1.datelastmodified
'If files haven't been updated in last day or two, then don't use the
data
If LastModifiedDate < Date - 1 Then
Call FilesNotUpdatedEmail
GoTo GetOuttaHere:
End If

Workbooks.Open MyHomePath & "\D_LEC_Template.xls"

'Select the Portfolio Level Data tab
ActiveWorkbook.Worksheets("tbl_Port_Char_1Period").Select
Range("B3").Select

'How many rows(portfolios) are we loading - Count them from column B
TotalRows = Cells(Rows.Count, "B").End(xlUp).Row - 3

'************************************ Portfolio Level Data Load
************************************
i = 1
For i = 1 To TotalRows
StartOver:
'If the row is blank or there is an error in the data then skip to the
next line
If ActiveCell.Offset(0, 53).Value = "INCOMPLETE" Then GoTo SkipMe
If ActiveCell.Value = "" Then GoTo SkipMe

Symbol(i) = "a" & i
PTFL(i) = ActiveCell.Offset(0, 1).Value
SZ_BDR_1(i) = ActiveCell.Offset(0, 14).Value 'Column P
SZ_CLS_1(i) = ActiveCell.Offset(0, 15).Value 'Column Q
LSCORE_1(i) = ActiveCell.Offset(0, 30).Value 'Column AF
STLBDR_1(i) = ActiveCell.Offset(0, 31).Value 'Column AG
STLBDES1(i) = ActiveCell.Offset(0, 32).Value 'Column AH
STLCLAS(i) = ActiveCell.Offset(0, 36).Value 'Column AL
OBJ_SIZE(i) = ActiveCell.Offset(0, 38).Value 'Column AN
OBLSCR_1(i) = ActiveCell.Offset(0, 46).Value 'Column AV
OBSTYLB1(i) = ActiveCell.Offset(0, 47).Value 'Column AW
OSTBDES1(i) = ActiveCell.Offset(0, 48).Value 'Column AX
OSTLCLS1(i) = ActiveCell.Offset(0, 52).Value 'Column BB

ActiveCell.Offset(1, 0).Select
Next i

EndMe:

'Open the Excel file to be loaded to Factset - populate it with data
Workbooks.Open Filename:=MyHomePath & "\LECPortfolioUpload.xls"
ActiveWorkbook.Sheets("Sheet1").Select
Range("A2:N50").Select
Selection.ClearContents
Range("A2").Select

'Load the updated Data
i = 1
For i = 1 To TotalRows
ActiveCell.Value = Symbol(i)
ActiveCell.Offset(0, 1).Value = PTFL(i)
ActiveCell.Offset(0, 2).Value = SZ_BDR_1(i)
ActiveCell.Offset(0, 3).Value = SZ_CLS_1(i)
ActiveCell.Offset(0, 4).Value = LSCORE_1(i)
ActiveCell.Offset(0, 5).Value = STLBDR_1(i)
ActiveCell.Offset(0, 6).Value = STLBDES1(i)
ActiveCell.Offset(0, 7).Value = STLCLAS(i)
ActiveCell.Offset(0, 8).Value = OBJ_SIZE(i)
ActiveCell.Offset(0, 9).Value = OBLSCR_1(i)
ActiveCell.Offset(0, 10).Value = OBSTYLB1(i)
ActiveCell.Offset(0, 11).Value = OSTBDES1(i)
ActiveCell.Offset(0, 12).Value = OSTLCLS1(i)

ActiveCell.Offset(1, 0).Select
Next i

ActiveWorkbook.Close savechanges:=True

'Load the Portfolio Data to Factset
Set FDSAPI = CreateObject("factset.factset_api")
Result = FDSAPI.RunApplication("Data Central", _
"COMMAND=UPLOAD", _
"PC_DATABASE =" & MyHomePath & "\LECPortfolioUpload.xls", _
"DESCRIPTOR=CLIENT:LEC_PORTFOLIO_LEVEL_DATA", _
"ONLINE_DATABASE=CLIENT:LEC_PTFL_LEVEL_Data.PRT", _
"MODE = REPLACE", _
"BATCH = TRUE")
Set FDSAPI = Nothing

'************************ Begin the Security Level Data Load
*****************************************

Workbooks("D_LEC_Template.xls").Activate

s = 1
For s = 1 To 5
'Copy the Data from the data download file to the upload file
ActiveWorkbook.Worksheets(SecurityLevelData(s)).Activate
Range("A9").Select
Workbooks.Open Filename:= _
"O:\Factset\DailyDownloads\LEC\LECSecurityUploads.xls"
Cells.Select
Selection.ClearContents
Range("A1").Select
Workbooks("D_LEC_Template.xls").Activate
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks("LECSecurityUploads.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
ActiveWorkbook.Close savechanges:=True
'Load the Data to Factset
Set FDSAPI = CreateObject("factset.factset_api")
Result = FDSAPI.RunApplication("Data Central", _
"COMMAND=UPLOAD", _
"PC_DATABASE =" & MyHomePath &
"\LECSecurityUploads.xls", _
"DESCRIPTOR=CLIENT:LEC_SECURITY_LEVEL_DATA", _
"ONLINE_DATABASE=CLIENT:" & SecurityLevelData(s) &
".prt", _
"MODE = REPLACE", _
"BATCH = TRUE")
'Close out FactSet
Set FDSAPI = Nothing
Next s

Application.CutCopyMode = False
Workbooks("D_LEC_Template.xls").Close savechanges:=False

'Send Success Emails
Call LECDataLoadedEmail

Application.DisplayAlerts = True
ActiveWorkbook.Save
GoTo GetOuttaHere

SkipMe:
'If we are past the data range end the loop - else go down one cell
and look for data again
If ActiveCell.Offset(-1, 0).Value <> Empty Then
ActiveCell.Offset(1, 0).Select
GoTo StartOver
End If
GoTo EndMe

GetOuttaHere:
Set fs = Nothing
Set fs1 = Nothing

ActiveWorkbook.Save
Application.DisplayAlerts = False
Application.Quit

End Sub

 

[Bob Umlas]

It's almost a contradiction in terms when you tell excel to quit while
opening a workbook. Try this -- often clears up any problems associatd with
this kind of discrepancy and we use it all the time:
In either Auto_Open or Workbook_Open event, for example Auto_open:
Sub Auto_Open()
Application.Ontime Now,"Continue"
End Sub
Sub Continue()
' ..everything else you HAD in auto_open goes here
End Sub

No conflicts.

Try it; I'll bet you'll like it...

Bob Umlas
Excel MVP

 

[Bongard]

Thanks Bob, I will give this a try tomorrow morning and let you know
if it is any improvement.

Brian

 

[Bongard]

Thanks Bob, I will give this a try tomorrow morning and let you know
if it is any improvement.

Brian

Bob that seemed to work just fine. It's weird because I used to use

Private Sub Workbook_Open
Call Auto_Open_Calc
End Sub

and just by changing the code to:
Private Sub Workbook_Open
Application.OnTime Now, "Auto_Open_Calc"
End Sub

I no longer get the application hanging open. Why would this be? You
say it's a contradiction when you tell excel to quit while opening a
workbook - I guess I don't understand that. I am telling it to quit
but it is well after I told it to open any workbooks.

Thanks for your help,
Brian

 

[jaf]

Brian,
From VBA help...
"You are not required to use the Call keyword when calling a procedure. However, if you use the Call keyword to call a procedure
that requires arguments, argumentlist must be enclosed in parentheses. If you omit the Call keyword, you also must omit the
parentheses around argumentlist. If you use either Call syntax to call any intrinsic or user-defined function, the function's return
value is discarded.

To pass a whole array to a procedure, use the array name followed by empty parentheses."

Call Auto_Open_Calc 'doesn't work
Call Auto_Open_Calc() 'works
Auto_Open_Calc 'works

John

Thanks Bob, I will give this a try tomorrow morning and let you know
if it is any improvement.

Brian

Bob that seemed to work just fine. It's weird because I used to use

Private Sub Workbook_Open
Call Auto_Open_Calc
End Sub

and just by changing the code to:
Private Sub Workbook_Open
Application.OnTime Now, "Auto_Open_Calc"
End Sub

I no longer get the application hanging open. Why would this be? You
say it's a contradiction when you tell excel to quit while opening a
workbook - I guess I don't understand that. I am telling it to quit
but it is well after I told it to open any workbooks.

Thanks for your help,
Brian