I created a vba macro that worked last month but without changing anything, I think, it now is giving me an error at the "Set objFSO". It stops there and says "Can't find project or library". Please help!!
Sub RateUpdateCopyandPasteLoop()
Dim DDir As String
Dim DFormat As String
Dim i As Long
Dim PDFFileName As String
Dim Filename As String
Dim ActualFilename As String
Dim strPath As String
ActualFilename = ActiveSheet.Range("B16")
DDir = "W:\Derivatives\Swap Documents\Swap Reset Notices (PDF Files)"
strPath = "W:\Derivatives\Swap Documents\Swap Reset Notices (Excel Files)"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Const ForWriting = 2
Set objFolder = objFSO.GetFolder(strPath)
Application.ScreenUpdating = False
For Each objFile In objFolder.Files
If objFSO.GetExtensionName(objFile.Path) = "xls" Then
Set objWorkbook = Workbooks.Open(objFile.Path)
' Takes the updated 1 Month Libor figures and pastes values into Data sheet
Workbooks.Open Filename:="W:\Derivatives\Swap Documents\1 Month Libor Settings.xls"
Windows("1 Month Libor Settings.xls").Activate
Range("A3:B65000").Select
Selection.Copy
objWorkbook.Activate
Sheets("Data").Select
Range("R2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Swap Reset Notice").Select
Range("L15").Select
Selection.Copy
Sheets("Data").Select
Range("L1").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("1 Month Libor Settings.xls").Close Savechanges:=False
Sheets("Swap Reset Notice").Select
Range("A2").Select
DFormat = Format(Date, "m-d-yy") & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=DDir & "\" & ActiveSheet.Range("B16") & " " & DFormat, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
objWorkbook.Close True 'Save changes
End If
Next
End Sub
Bookmarks