Option Explicit
Sub macro1()
Dim strPath As String
Dim strMonPnL As String, strTuesPnL As String, strWedPnL As String, strThursPnL As String, strFriPnL As String
strPath = "c:\mypath\"
strMonPnL = "20050117.xls"
strTuesPnL = "20050118.xls"
strWedPnL = "20050119.xls"
strThursPnL = "20050120.xls"
strFriPnL = "20050121.xls"
'verify strPath ends with \
If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
'verify directory exists
If Not DirExists(strPath) Then
MsgBox strPath & "The path Does Not Exist!", vbOK, "error"
End If
If FileExists(strPath & strMonPnL) Then 'verify Monday file exists
Workbooks.Open(Filename:=[strpath & strmonpnl], UpdateLinks:=0).RunAutoMacros Which:=xlAutoOpen
ActiveWorkbook.Sheets("PLSpreadsheet").[GSI_MTM].Copy
ThisWorkbook.Sheets("Sheet1").[MondayGSI].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Sheets("PLSpreadsheet").[PIERCE_MTM].Copy
ThisWorkbook.Sheets("Sheet1").[MondayPIERCE].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Close
End If
If FileExists(strPath & strTuesPnL) Then
Workbooks.Open(Filename:=[strPath & strTuesPnL], UpdateLinks:=0).RunAutoMacros Which:=xlAutoOpen
ActiveWorkbook.Sheets("PLSpreadsheet").[GSI_MTM].Copy
ThisWorkbook.Sheets("Sheet1").[TuesdayGSI].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Sheets("PLSpreadsheet").[PIERCE_MTM].Copy
ThisWorkbook.Sheets("Sheet1").[TuesdayPIERCE].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Close
End If
If FileExists(strPath & strWedPnL) Then
Workbooks.Open(Filename:=[strPath & strWedPnL], UpdateLinks:=0).RunAutoMacros Which:=xlAutoOpen
ActiveWorkbook.Sheets("PLSpreadsheet").[GSI_MTM].Copy
ThisWorkbook.Sheets("Sheet1").[WednesdayGSI].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Sheets("PLSpreadsheet").[PIERCE_MTM].Copy
ThisWorkbook.Sheets("Sheet1").[WednesdayPIERCE].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Close
End If
If FileExists(strPath & strThursPnL) Then
Workbooks.Open(Filename:=[strPath & strThursPnL], UpdateLinks:=0).RunAutoMacros Which:=xlAutoOpen
ActiveWorkbook.Sheets("PLSpreadsheet").[GSI_MTM].Copy
ThisWorkbook.Sheets("Sheet1").[ThursdayGSI].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Sheets("PLSpreadsheet").[PIERCE_MTM].Copy
ThisWorkbook.Sheets("Sheet1").[ThursdayPIERCE].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Close
End If
If FileExists(strPath & strFriPnL) Then
Workbooks.Open(Filename:=[strPath & strFriPnL], UpdateLinks:=0).RunAutoMacros Which:=xlAutoOpen
ActiveWorkbook.Sheets("PLSpreadsheet").[GSI_MTM].Copy
ThisWorkbook.Sheets("Sheet1").[FridayGSI].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Sheets("PLSpreadsheet").[PIERCE_MTM].Copy
ThisWorkbook.Sheets("Sheet1").[FridayPIERCE].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Close
End If
End Sub
Function FileExists(sFilePath As String) As Boolean
If Len(Dir(sFilePath)) = 0 Then
FileExists = False
Else
FileExists = True
End If
End Function
Function DirExists(strPath As String)
If Len(Dir(strPath)) = 0 Then
DirExists = False
Else
DirExists = True
End If
End Function