Sub Monthly()
Dim BS As Long, Dot As Long
Dim Oldfilename As String
Dim Oldfilepath As String
Dim Newfilename As String
Dim OpenA As Workbook, OpenB As Workbook
Dim TheFile As String
Dim TheFile2 As String
Dim myReturn&
Dim continue&
Application.ScreenUpdating = False
myReturn = MsgBox("Do you want to do Monthly Balance Sheet?", _
vbInformation + vbYesNo, _
"Monthly Balance Sheet?")
If myReturn = 6 Then GoTo Monthly
Range("B10,G10").Value = "N/A"
Application.ScreenUpdating = True
Application.ScreenUpdating = False
If myReturn = 7 Then GoTo SkipMonthly
Monthly:
NewestFolderInThePath = ReturnNewestFolder("\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\")
Path = "\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\"
TheFile = Path & NewestFolderInThePath
CreateObject("WScript.Shell").CurrentDirectory = TheFile
TheFile = Application.GetOpenFilename("Excel Files (*.*), *.*", , "Open Balance Sheet File FRPMBSDTL")
If TheFile = "False" Then
continue = MsgBox("Do you want to Continue to IS Download?", _
vbInformation + vbYesNo, _
"Continue?")
If continue = 6 Then GoTo IncSt
If continue = 7 Then Exit Sub
End If
Oldfilename = TheFile
' Separate the file path and file name
BS = InStrRev(Oldfilename, "\")
Oldfilepath = Left(Oldfilename, BS)
Newfilename = Right(Oldfilename, Len(Oldfilename) - BS)
' Add the CSV extension to the file name
Dot = InStr(1, Newfilename, ".")
Newfilename = Oldfilepath & Left(Newfilename, Dot) & "csv"
' Rename the file
Name Oldfilename As Newfilename
Workbooks.Open Filename:=Newfilename
Set OpenA = Workbooks(Workbooks.Count)
ChDrive "E:\"
ChDir "E:\2007 Work\Analytical Review\Balance Sheet"
TheFile2 = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Open Current Month Balance Sheet Review File")
If TheFile2 = "False" Then
OpenA.Activate
ActiveWindow.Close
continue = MsgBox("Do you want to Continue to IS Download?", _
vbInformation + vbYesNo, _
"Continue?")
If continue = 6 Then GoTo IncSt
If continue = 7 Then Exit Sub
End If
Workbooks.Open Filename:=TheFile2
Set OpenB = Workbooks(Workbooks.Count)
OpenA.Activate
Cells.Select
Selection.Copy
OpenB.Activate
Sheets("Dload").Select
Cells.Select
ActiveSheet.Paste
Application.CutCopyMode = False
OpenA.Activate
ActiveWindow.Close
Workbooks("Analytical Review Guide").Sheets(1).Range("vbs").Value _
= IIf((Application.CountIf(OpenB.Sheets("Validation").Range("D3:E3"), "OK") = 2), "OK", "Error")
OpenB.Activate
ActiveWorkbook.Save
ActiveWindow.Close
Range("mbs").FormulaR1C1 = "X"
Application.ScreenUpdating = True
SkipMonthly:
IncSt:
Application.ScreenUpdating = False
NewestFolderInThePath = ReturnNewestFolder("\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\")
Path = "\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\"
TheFile = Path & NewestFolderInThePath
CreateObject("WScript.Shell").CurrentDirectory = TheFile
TheFile = Application.GetOpenFilename("Excel Files (*.*), *.*", , "Open Income Statement File FRPMISSUM")
If TheFile = "False" Then
continue = MsgBox("Do you want to Continue to OE Download?", _
vbInformation + vbYesNo, _
"Continue?")
If continue = 6 Then GoTo Oest
If continue = 7 Then Exit Sub
End If
Oldfilename = TheFile
' Separate the file path and file name
BS = InStrRev(Oldfilename, "\")
Oldfilepath = Left(Oldfilename, BS)
Newfilename = Right(Oldfilename, Len(Oldfilename) - BS)
' Add the CSV extension to the file name
Dot = InStr(1, Newfilename, ".")
Newfilename = Oldfilepath & Left(Newfilename, Dot) & "csv"
' Rename the file
Name Oldfilename As Newfilename
Workbooks.Open Filename:=Newfilename
Set OpenA = Workbooks(Workbooks.Count)
NewestFolderInThePath = ReturnNewestFolder("\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\")
Path = "\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\"
TheFile = Path & NewestFolderInThePath
CreateObject("WScript.Shell").CurrentDirectory = TheFile
TheFile = Application.GetOpenFilename("Excel Files (*.*), *.*", , "Open Income Statement File COGSSTMACCT3")
If TheFile = "False" Then
OpenA.Activate
ActiveWindow.Close
continue = MsgBox("Do you want to Continue to OE Download?", _
vbInformation + vbYesNo, _
"Continue?")
If continue = 6 Then GoTo Oest
If continue = 7 Then Exit Sub
End If
Oldfilename = TheFile
' Separate the file path and file name
BS = InStrRev(Oldfilename, "\")
Oldfilepath = Left(Oldfilename, BS)
Newfilename = Right(Oldfilename, Len(Oldfilename) - BS)
' Add the CSV extension to the file name
Dot = InStr(1, Newfilename, ".")
Newfilename = Oldfilepath & Left(Newfilename, Dot) & "csv"
' Rename the file
Name Oldfilename As Newfilename
Workbooks.Open Filename:=Newfilename
Set OpenB = Workbooks(Workbooks.Count)
NewestFolderInThePath = ReturnNewestFolder("\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\")
Path = "\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\"
TheFile = Path & NewestFolderInThePath
CreateObject("WScript.Shell").CurrentDirectory = TheFile
TheFile = Application.GetOpenFilename("Excel Files (*.*), *.*", , "Open Income Statement File FRPMOIEDTL")
If TheFile = "False" Then
OpenA.Activate
ActiveWindow.Close
OpenB.Activate
ActiveWindow.Close
continue = MsgBox("Do you want to Continue to OE Download?", _
vbInformation + vbYesNo, _
"Continue?")
If continue = 6 Then GoTo Oest
If continue = 7 Then Exit Sub
End If
Oldfilename = TheFile
' Separate the file path and file name
BS = InStrRev(Oldfilename, "\")
Oldfilepath = Left(Oldfilename, BS)
Newfilename = Right(Oldfilename, Len(Oldfilename) - BS)
' Add the CSV extension to the file name
Dot = InStr(1, Newfilename, ".")
Newfilename = Oldfilepath & Left(Newfilename, Dot) & "csv"
' Rename the file
Name Oldfilename As Newfilename
Workbooks.Open Filename:=Newfilename
Set OpenC = Workbooks(Workbooks.Count)
ChDrive "E:\"
ChDir "E:\2007 Work\Analytical Review\Income Statement"
TheFile2 = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Open Current Month Income Statement Review File")
If TheFile2 = "False" Then
OpenA.Activate
ActiveWindow.Close
OpenB.Activate
ActiveWindow.Close
OpenC.Activate
ActiveWindow.Close
continue = MsgBox("Do you want to Continue to OE Download?", _
vbInformation + vbYesNo, _
"Continue?")
If continue = 6 Then GoTo Oest
If continue = 7 Then Exit Sub
End If
Workbooks.Open Filename:=TheFile2
Set OpenD = Workbooks(Workbooks.Count)
OpenD.Activate
Sheets("Dload").Select
Range("A1:Z700").Select
Selection.ClearContents
OpenA.Activate
Rows("1:50").Select
Selection.Copy
OpenD.Activate
Sheets("Dload").Select
Rows("1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
OpenB.Activate
Rows("1:130").Select
Selection.Copy
OpenD.Activate
Sheets("Dload").Select
Rows("51").Select
ActiveSheet.Paste
Application.CutCopyMode = False
OpenC.Activate
Rows("1:130").Select
Selection.Copy
OpenD.Activate
Sheets("Dload").Select
Rows("195").Select
ActiveSheet.Paste
Application.CutCopyMode = False
OpenA.Activate
ActiveWindow.Close
OpenB.Activate
ActiveWindow.Close
OpenC.Activate
ActiveWindow.Close
Workbooks("Analytical Review Guide").Sheets(1).Range("vis").Value _
= IIf((Application.CountIf(OpenD.Sheets("Validation").Range("C3:F3"), "OK") = 4), "OK", "Error")
OpenD.Activate
ActiveWorkbook.Save
ActiveWindow.Close
Range("mis").FormulaR1C1 = "X"
Application.ScreenUpdating = True
Application.ScreenUpdating = False
Oest: