Sub GetOracle(OracleFileName, NewReportTab)
MacroWorkbook_name = ThisWorkbook.Name
MacroWorksheet_Name = ThisWorkbook.ActiveSheet.Name
Application.ScreenUpdating = False
Set PC1 = ThisWorkbook.Worksheets("settings").Range("_PC1")
Set Task1 = ThisWorkbook.Worksheets("settings").Range("Task1")
Set PC2 = ThisWorkbook.Worksheets("settings").Range("_PC2")
Set Task2 = ThisWorkbook.Worksheets("settings").Range("Task2")
Set PC3 = ThisWorkbook.Worksheets("settings").Range("_PC3")
Set Task3 = ThisWorkbook.Worksheets("settings").Range("Task3")
OldReportTab = ActiveSheet.Name
'check if this is a valid report
Select Case ActiveSheet.Name 'find the report title
Case "IS"
FindThis = "IS"
Case "BS"
FindThis = "BS"
End Select
'find the report titles
Workbooks(OracleFileName).Worksheets(NewReportTab).Activate
Set FoundIt = Workbooks(OracleFileName).Worksheets(NewReportTab).Cells.Find(What:=FindThis, After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False)
'Set FoundIt = Cells.Find(What:=FindThis, After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=False)
If FoundIt Is Nothing Then
Response = MsgBox("The selected report is not a valid " & FindThis & " report." & Chr(10) & Chr(10) _
& "Please make sure you select the right report.", vbCritical, Title)
Application.ScreenUpdating = True
Exit Sub
Else
End If
'check if the sum of the company columns are the same as the total column
Application.ScreenUpdating = False
End_Row = ActiveSheet.Range("A1048576").End(xlUp).Row 'find the last row
Workbooks(OracleFileName).Worksheets(NewReportTab).Cells.Find(What:="CheckSum", After:=ActiveCell, LookIn:=xlValues, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
'Cells.Find(What:="CheckSum", After:=ActiveCell, LookIn:=xlValues, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ColumnTitle = ActiveCell.Column
SumColumn = Round(Cells(End_Row, ColumnTitle).Offset(0, 1).Value, 2) 'this is the column that has the parent rollup entity
SumOfColumns = Round(Cells(End_Row, ColumnTitle).Value, 2) 'this is the sum column, it will later be deleted if there is no problem
If SumColumn <> SumOfColumns Then
Response = MsgBox("The sum of the columns does not match the Total column." & Chr(10) & Chr(10) _
& "Please check that all the companies included in the Total column are shown on this report." & Chr(10) & Chr(10) _
& "You will need to contact the Data Monkey...", vbCritical, Title)
Application.ScreenUpdating = True
Exit Sub
Else
End If
Load DoublePBar
With DoublePBar
.ProgressDescription1 = "Copying Oracle report to this file..."
.Progress1.Width = 0.4
.Progress2.Width = 0
.Progress3.Width = 0
.Show False
.Height = 160
End With
DoEvents
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks(OracleFileName).Worksheets(NewReportTab).Copy Before:=ThisWorkbook.Sheets(1) 'copy the new report over to this workbook
ThisWorkbook.Sheets(OldReportTab).Delete 'delete the old sheet
Application.DisplayAlerts = True
ActiveSheet.Name = OldReportTab 'name the sheet
'add code fix
'move report title, date, currency and sob
'ActiveSheet.Select
'First Break
'ThisWorkbook.ActiveSheet.Range("C1").Cut
Workbooks(MacroWorkbook_name).Worksheets(MacroWorksheet_Name).Range("C1").Cut
ThisWorkbook.ActiveSheet.Paste Destination:=ThisWorkbook.ActiveSheet.Range("B3")
'ActiveSheet.Range("B3").Select
'ActiveSheet.Paste
'Application.CutCopyMode = False
ThisWorkbook.ActiveSheet.Range("C2").Cut
'ActiveSheet.Range("C2").Cut
ThisWorkbook.ActiveSheet.Paste Destination:=ThisWorkbook.ActiveSheet.Range("B5")
'Avinash
ThisWorkbook.ActiveSheet.Range("E1").Cut
ThisWorkbook.ActiveSheet.Paste Destination:=ThisWorkbook.ActiveSheet.Range("B7")
ThisWorkbook.ActiveSheet.Range("B6").FormulaR1C1 = _
"=""Current Period: ""&RIGHT(R[-3]C[1],6)&"" Currency: ""&RIGHT(RC[-1],3)"
'ActiveCell.FormulaR1C1 = _
"=""Current Period: ""&RIGHT(R[-3]C[1],6)&"" Currency: ""&RIGHT(RC[-1],3)"
ThisWorkbook.ActiveSheet.Range("B6").Copy
'ActiveSheet.Selection.Copy
ThisWorkbook.ActiveSheet.Range("B6").PasteSpecial Paste:=xlPasteValues
'ActiveSheet.Selection.PasteSpecial Paste:=xlPasteValues
ThisWorkbook.ActiveSheet.Range("A6,C1:E3").Clear
'ActiveSheet.Selection.ClearContents
ThisWorkbook.ActiveSheet.Cells.Interior.ColorIndex = xlNone
'ThisWorkbook.Worksheets(OldReportTab).Cells.Interior.ColorIndex = xlNone
' Workbooks(OracleFileName).Worksheets(NewReportTab).Cells.Interior.ColorIndex = xlNone
'ActiveSheet.Cells.Select
'ActiveSheet.Selection.Interior.ColorIndex = xlNone
Add_Grouping 'start the colunn and row formating
Range("A2").Select
CloseFile = MsgBox("Do you want to Close " & OracleFileName & "?", vbYesNo, "E*TRADE FINANCIALS")
If CloseFile = vbYes Then
Workbooks(OracleFileName).Close SaveChanges:=False
Else: End If
'clean up and finish
PC1.Value = 0.0001
Task1.Value = "File: "
PC2.Value = 0.0001
Task2.Value = "Columns: "
PC3.Value = 0.0001
Task3.Value = "Rows: "
Unload DoublePBar
ActiveSheet.Calculate
Application.ScreenUpdating = True
Application.StatusBar = False
Unload frmPBar
End Sub
