Application.Quit Run-Time Error 1004

B

Bongard

Hi - I'm hoping that this is something simple that I'm overlooking. I
have a fair amount of code that I have tried placing in the
Workbook_Open macro as well as the Auto_Open macro. My code runs just
fine until I get to the last line of code in the workbook
"Application.Quit" and it gives me the error:

Run-time error 1004
Application-defined or object defined error

I will paste my code I suppose so that if something in the code is
triggering this error that it would be evident. Sorry for all the
code. Thanks in advance for any help that you can offer, this is
driving me nuts!

-Brian

Sub Workbook_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 FilesNotUpdatedEmail

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

ActiveWorkbook.Save
Application.Quit '****************************************** IT ERRORS
OUT HERE ************************************

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

End Sub
 
G

Gary''s Student

Might be the ActiveWorkbook.Save

Try switching DisplayAlerts back on before you get to that point in your code.
 
B

Bongard

Gary I appreciate your response. I tried several versions of turning
Display Alerts back on, never turning it off, not saving the workbook
before closing etc... but I still continued to get the same Error
message on the application.quit line of code. Any other ideas?

Thanks,
Brian
 
G

Gary''s Student

We should ask ourselves what could cause Application.Quit to fail??

Are there any other apps we need to Close first?
Are there any other Objects we need to set to Nothing first?
 
B

Bongard

I sure don't believe so. I am not the most knowledgeable coder in the
world and don't claim to be. What applications would need to be
closed? I set the FDSAPI objeect to Nothing, do you see anything else
that I might be missing?

Thanks,
Brian
 
J

JLGWhiz

How do you handle this mail application?

Call LECDataLoadedEmail

Has it been cleared?
 
B

Bongard

The mail application is just a small executeable that our IT group has
put together to send emails internally using through VB. We use it all
over the place in VB code and I have never seen it cause a problem. It
is actually sending emails just fine here in this code. Just to
exclude the possibility of that email application being the issue I
commented out that line of code and gave it a try. Still the same
errors. I don't have to set my variable or anything else like that =
nothing do I? I've never done that before, but I'm just trying to
think of what could be causing this error to pop up.

Thanks,
Brian
 

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

Top