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
Similar Threads
